Microsoft Office Forums - View Single Post - [Solved] Edit mailmerge recipient list using a macro

hhorber · 12-17-2019, 11:48 AM

In Word 2016 unfortunately it is not possible to record the procedure <Edit Recipient List> within a mail merge document to print selected recipients only.

The VBA code below can be used to fullfill this task:

Module 1

Code:

Sub Test_EditRecipientList()

   ' Edit Recipient List using a VBA macro and print selected recipients

   Dim ch1 As String
   Dim ch2 As String
   Dim ch3 As String
   
   Dim TableName As String
   Dim TableField As String
   Dim FilterCriteria As String

   ' The following 3 variables may be adapted according to your Excel Worksheet
   ch1 = "TABLE_1"         ' Name of the Table within the EXCEL Worksheet
   ch2 = "CATEGORY"        ' Name of the Field within the referenced table which is to be filtered
   ch3 = "STWEG_Hansmatt"  ' The recipients which meat this criteria are selected and printed
   
   ' Do not change the following 3 lines
   TableName = "`" & ch1 & "`"
   TableField = "`" & ch2 & "`"
   FilterCriteria = "'" & ch3 & "'"
   
   Call EditRecipientList(TableName, TableField, FilterCriteria)
   
End Sub

Module 2

Code:

Sub EditRecipientList(TableName As String, TableField As String, FilterCriteria As String)

    Dim strQry_Current As String
    
    strQry_Current = ActiveDocument.MailMerge.DataSource.QueryString  ''Info fü Debug
    
    With ActiveDocument.MailMerge
       .DataSource.QueryString = "SELECT * FROM " & TableName
    End With
    
    With ActiveDocument.MailMerge
       .DataSource.QueryString = "SELECT * FROM " & TableName & "  WHERE " & TableField & " = " & FilterCriteria
       .Destination = wdSendToNewDocument
       .SuppressBlankLines = True
           With .DataSource
              .FirstRecord = wdDefaultFirstRecord
              .LastRecord = wdDefaultLastRecord
           End With
       .Execute Pause:=False
    End With

End Sub