locked
Word Template - mail merge RRS feed

  • Question

  • Hi,
     I have a program in which I am trying to do the mail merge with the word template that I already have.
     I have already setup the datasource for the word template in the word itself and it is pointing to my Employee Table which has all the information for the employee. I have also inserted the merge fields in the document where ever I need them. Now I want to print the document (Letter) with all the merge fields filled in from my application but for only employee whose Name or Number I sent from my program not all the employees I have in the datasource.
    Now if I have to do it in Word, we have an option called Find Recipient in Mailings -> Preview Results in which I can set the Name or Number of the employee to get all his information to do the merge but how can I do the same thing from my program.
     I can send all the fields values from my code like in the code below but I want to use my datasource to provide values. I have employee's Number I want to provide to the template to find the employee - so all the values are filled based on the employee's Number.
     
    Dim myWord As New Microsoft.Office.Interop.Word.Application
    Dim strToFile As String = "C:\User Letter.dotx"
    appWord.Documents.Open(strToFile, , True)
    appWord.Documents.Item(1).Fields.Item(1).Result.Text = "1234 56 Street"  'Address
    appWord.Documents.Item(1).Fields.Item(2).Result.Text = "T1T2T3"      'Postal Code
    
    Employee Number is the primary key for the table that is the datasource for my word template.
    Monday, June 6, 2011 8:03 PM

Answers

  • Hi,

     

    Thanks for your post.

     

    Here is my understanding of your question, please correct me if I have misunderstood anything:

    1. You want to implement Word mail merge feature automatically in VB.NET

    2. You know how to achieve this in Word UI

    3.  You want to attach an exist datasource to mailmerge automatically

     

    As I don’t know neither how you design the document nor how your datasource looks like, I’m unable to write the sample for you. However there is suggestion for you try:

     

    Word support for recording macro feature which means you can record all your actions in UI as VBA code. For more information about this, please refer to:

    http://office.microsoft.com/en-us/word-help/record-or-run-a-macro-HA010099769.aspx

    And

    http://msdn.microsoft.com/en-us/library/bb221203.aspx

     

    You can see how these actions perform in VBA, something like:

     

     ActiveDocument.MailMerge.OpenDataSource Name:= _
      "C:\Test\ContactList.xlsx", ConfirmConversions:=False, _
      ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
      PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
      WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
      Connection:= _
      "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Test\ContactList.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Lockin" _
      , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
      wdMergeSubTypeAccess
    

    Then you can code them in your VB.NET application.
    Please be aware of that VBA is similar with VB.NET somehow, but VBA is not equal to VB.NET, you cannot just paste VBA to VB.NET application.

     

    For more information, please refer to:

    Converting Code from VBA to Visual Basic .NET

     

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Marked as answer by Calvin_Gao Wednesday, June 15, 2011 1:46 PM
    Friday, June 10, 2011 5:44 AM

All replies

  • Hi,

     

    Thanks for your post.

     

    Here is my understanding of your question, please correct me if I have misunderstood anything:

    1. You want to implement Word mail merge feature automatically in VB.NET

    2. You know how to achieve this in Word UI

    3.  You want to attach an exist datasource to mailmerge automatically

     

    As I don’t know neither how you design the document nor how your datasource looks like, I’m unable to write the sample for you. However there is suggestion for you try:

     

    Word support for recording macro feature which means you can record all your actions in UI as VBA code. For more information about this, please refer to:

    http://office.microsoft.com/en-us/word-help/record-or-run-a-macro-HA010099769.aspx

    And

    http://msdn.microsoft.com/en-us/library/bb221203.aspx

     

    You can see how these actions perform in VBA, something like:

     

     ActiveDocument.MailMerge.OpenDataSource Name:= _
      "C:\Test\ContactList.xlsx", ConfirmConversions:=False, _
      ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
      PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
      WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
      Connection:= _
      "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Test\ContactList.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Lockin" _
      , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
      wdMergeSubTypeAccess
    

    Then you can code them in your VB.NET application.
    Please be aware of that VBA is similar with VB.NET somehow, but VBA is not equal to VB.NET, you cannot just paste VBA to VB.NET application.

     

    For more information, please refer to:

    Converting Code from VBA to Visual Basic .NET

     

    I hope this helps.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    • Marked as answer by Calvin_Gao Wednesday, June 15, 2011 1:46 PM
    Friday, June 10, 2011 5:44 AM
  • Hi,

    you can easily do VB.NET Word mail merge with a DataTable's single row or any other .NET object with this C# / VB.NET Word library.

    Here is a sample VB.NET code:

    ' Use the component in free mode.
    ComponentInfo.SetLicense("FREE-LIMITED-KEY")
    
    ' Create a DataTable with letter recipients.
    Dim dataTable = New DataTable()
    dataTable.Columns.Add("FullName", GetType(String))
    dataTable.Columns.Add("Location", GetType(String))
    dataTable.Rows.Add("John Doe", "New York City")
    dataTable.Rows.Add("Fred Nurk", "Chicago")
    dataTable.Rows.Add("Hans Meier", "Los Angeles")
    
    ' Create a template document for demonstration purpose.
    Dim templateDoc = New DocumentModel()
    templateDoc.Sections.Add(
    	New Section(templateDoc,
    		New Paragraph(templateDoc,
    			New Run(templateDoc, "Recipient:"),
    			New SpecialCharacter(templateDoc, SpecialCharacterType.Tab),
    			New Field(templateDoc, FieldType.MergeField, "FullName"),
    			New SpecialCharacter(templateDoc, SpecialCharacterType.LineBreak),
    			New Run(templateDoc, "Location:"),
    			New SpecialCharacter(templateDoc, SpecialCharacterType.Tab),
    			New Field(templateDoc, FieldType.MergeField, "Location"))))
    ' If you have already existing template document, load it like this:
    ' var templateDoc = DocumentModel.Load("TemplateDocument.docx", LoadOptions.DocxDefault);
    
    ' Execute a mail merge on a single data row.
    templateDoc.MailMerge.Execute(DataTable.Rows(0))
    
    ' Save the document to a file.
    templateDoc.Save("Document.docx", SaveOptions.DocxDefault)

    Tuesday, April 3, 2012 7:36 AM