Sorting and filtering a Word Mail Merge with VBA

Question

I am trying to automate creating a mail merge in Word from an Excel spreadsheet. I recorded a macro that does most of it, but the recording doesn't work when the 'Edit Recipients' dialog is open, so I need to go into the VBA code to do the sorting and filtering. Currently the entire code is this:

Sub Castingdirectors()
'
' Castingdirectors Macro
'
'
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    ChangeFileOpenDirectory "Y:\--file path redacted--\CASTING BOOK\"
    ActiveDocument.SaveAs2 FileName:= _
        "Y:\--file path redacted--\Casting Directors.docx", FileFormat:= _
        wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
        :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
        :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False, CompatibilityMode:=14
    ActiveWindow.Close
End Sub

This generates a mail merge of all entries.

I need to filter so that any entries where the field 'CASTING DIRECTOR SORT' is blank are NOT included, and to sort by CASTING DIRECTOR SORT ascending.

I could do this by inserting a 'skip record' field into the body of the Word mail merge doc and doing the 'sort' manually, but I am trying to create a 'master' mail merge document, with several macros assigned to command buttons that will execute and save different mail merges with different sorting/filtering.

Cindy Meister Cindy Meister · Accepted Answer · 2015-11-16T15:02:50

This is a good start. I think you can get pretty much everything you need using the Macro Recorder if you go a level deeper in the Edit Recipient dialog box. Look at the list of links at the bottom - choose "Filter".

This allows you to define Query terms AND sort the records (the second tab in the dialog box).

EDIT: Since the macro recorder no longer picks this up, you need to get the basic filter code another way. After defining the filter using the dialog box, create a little macro to print out the query information:

Sub GetQueryString()
    Debug.Print ActiveDocument.MailMerge.DataSource.QueryString
End Sub

This should print out the SQL statement to the Immediate Window (Ctrl+G if you're not seeing it). You can use this in your code "as is" if it filters and sorts the records correctly. Or you can modify it to suit your purpose.

Sorting and filtering a Word Mail Merge with VBA

1 Answers