none
Improve on Mail Merge Code RRS feed

  • Question

  • Hello all,

    I am working on a mail merge code where the main document structure would always be the same but the Source Data would constantly be changing. In that regard I want a code that would insert the merge field and change the Data Source on the code. The recorded code with only comment added to it is as follows:


    1. Sub Macro3()

      'This prgram should print to pdf copies of mail merge document.

          'Start the MailMerge process
          ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
          ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

          'Connection to the Data Dource
          ActiveDocument.MailMerge.OpenDataSource Name:= _
              "C:\Users\UserName\Desktop\FileName.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:\Users\UserName\Desktop\FileName.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet O" _
              , SQLStatement:="SELECT * FROM `NamedRangeInExcel`", SQLStatement1:="", SubType:= _
              wdMergeSubTypeAccess

          'Move to the first merge field position
          Selection.MoveRight Unit:=wdCharacter, Count:=20
          Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend

          'Insert the first merge field
          ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
              "Sample_"

          'Move to the second merge field position
          Selection.MoveDown Unit:=wdLine, Count:=2
          Selection.MoveLeft Unit:=wdCharacter, Count:=8
          Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend

          'Insert the Second merge field
          ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
              "REF_"

          'Move to the third merge field position
          Selection.MoveDown Unit:=wdLine, Count:=2
          Selection.MoveLeft Unit:=wdCharacter, Count:=11
          Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend

          'Insert the third merge field
          ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
              "TYPE"

          'Move to the fourth merge field position
          Selection.MoveLeft Unit:=wdCharacter, Count:=35
          Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

          'Insert the fourth merge field
          ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="FA_"

          'Move to the fifth merge field position
          Selection.MoveLeft Unit:=wdCharacter, Count:=26
          Selection.MoveRight Unit:=wdCharacter, Count:=1
          Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend

          'Insert the fifth merge field
          ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
              "Sample_"

          'Print the document to PDF

          With ActiveDocument.MailMerge
              .Destination = wdSendToPrinter
              .SuppressBlankLines = True
              With .DataSource
                  .FirstRecord = wdDefaultFirstRecord
                  .LastRecord = wdDefaultLastRecord
              End With
              .Execute Pause:=False
          End With
      End Sub
                                                                    

    I am seeking the following improvements:

    to make the Name parameter of the OpenDataSource Method a variable and to make Data Source in the connection parameter also a variable.

    Thanks all.

    Link to earlier question which I have tried to improve on.


    Thanks to all that continue to serve as help HashMan




    • Edited by HashMan19_11 Sunday, July 7, 2013 9:05 PM Reduce the number of questions
    Saturday, July 6, 2013 11:12 PM

Answers

All replies

  • to make the Name parameter of the OpenDataSource Method a variable and to make Data Source in the connection parameter also a variable.

    The Name parameter is a string:

    Dim FileName As String
    FileName = "C:\Users\UserName\Desktop\FileName.xlsx"
    ......
    
    ActiveDocument.MailMerge.OpenDataSource Name:=FileName, ...

    To use it in Connection paremeter, you have to combine with other parts:

    Connection:= _
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" + FileName + _
    ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet O",

    I think it look even more messy. You can make the entire Connection string a parameter:

    Dim ConnectionStr As String
    ConnectionStr = _
    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\UserName\Desktop\FileName.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Jet O"

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 10, 2013 8:01 PM
    Moderator
  • Thanks Damon Zheng. 

    Thanks to all that continue to serve as help HashMan

    • Marked as answer by HashMan19_11 Tuesday, July 16, 2013 9:16 PM
    Friday, July 12, 2013 12:26 AM