Mail Merge in Word+Excel using VBA

Over at Automated Word Mail Merge I have a sample workbook for creating a mail merge in Excel using VBA, but I've felt the example wasn't the best. So I produced another, and here it is.

Unfortunately, it's difficult to create useful mail merge examples because there is almost no way to produce a generic example. Any example I come up with is going to solve a particular problem that helps very few people. So my code just demonstrates a few techniques which hopefully you can adapt for your own needs.

To follow along, download the sample files here:

Sample mail merge docs for Office 2003
Sample mail merge docs for Office 2007

For purposes of demonstration, unzip all files into the same folder.

You'll want to review this MSKB article. It shows you how to get around some of the security features of MS Word when doing mail merges, so you can do it programmatically without prompts. At this point I suppose I have to tell you that disabling security features is discouraged and you do so at your own risk.

A simple mail merge

This example assumes that

a) you have already created your source data (Excel worksheet) and the mail merge document (Word Document),
b) you have already linked them together, written your document and inserted merge fields, and
c) all you want to do is programmatically create the individualized documents (or send them to the printer, or email) at some later date.

If you are following along, the files involved are

• Mail_Merge_Code – contains VBA code for merging
• Mail_Merge_Data – data source
• Mail_Merge_Simple – document to be merged

Open Mail_Merge_Code.xls/.xlsx and check out the DoMailMerge_Simple procedure. Here is the code:

Sub DoMailMerge_Simple()

Dim msWord As Object  ' Word.Application
Dim wordDoc As Object  ' Word.Document

Const wdSendToNewDocument = 0
Const wdDefaultFirstRecord = 1
Const wdDefaultLastRecord = -16

' grab MS Word
  Set msWord = GetWordApp

  ' open mail merge document
  If Not msWord Is Nothing Then
    Set wordDoc = GetWordDoc(msWord, ActiveWorkbook.Path & "\Mail_Merge_Simple.doc")

    ' perform mail merge
    With wordDoc.MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
    End With

    ' show merged document
    msWord.Visible = True

  End If

End Sub

This code opens Mail_Merge_Simple.doc/.docx (which is already linked to Mail_Merge_Data.xls/.xlsx) and does a simple mail merge with the data source. Since the files are already linked, the merge is simply executed and the result displayed. Note that there are other procedures being used here which I haven't printed, but they are present in the workbook.

A little bit harder

This example will take a blank document (Mail_Merge_Difficult.doc/.docx), create the relationship with the data source (Mail_Merge_Data.xls/.xlsx) and populate the document with fields and values from the data source. It uses an existing blank document, but you could easily just create a new document programmatically instead.

If you are following along, the files involved are

• Mail_Merge_Code – contains VBA code for merging
• Mail_Merge_Data – data source
• Mail_Merge_Difficult – document to be merged

Open Mail_Merge_Code.xls/.xlsx and check out the DoMailMerge_Difficult procedure. Here is the code:

Sub DoMailMerge_Difficult()

Dim msWord As Object  ' Word.Application
Dim wordDoc As Object  ' Word.Document
Dim wkbk As Excel.Workbook
Dim headerRange As Excel.Range
Dim headerValues As Variant
Dim i As Long

Const wdFormLetters = 0
Const wdFieldMergeField = 59
Const wdSendToNewDocument = 0
Const wdDefaultFirstRecord = 1
Const wdDefaultLastRecord = -16

  ' grab MS Word
  Set msWord = GetWordApp

  ' open mail merge document
  If Not msWord Is Nothing Then
    Set wordDoc = GetWordDoc(msWord, ActiveWorkbook.Path & "\Mail_Merge_Difficult.doc")

    ' link document to data source
    wordDoc.MailMerge.MainDocumentType = wdFormLetters
    wordDoc.MailMerge.OpenDataSource Name:=ActiveWorkbook.Path & "\Mail_Merge_Data.xls", _
                                     SQLStatement:="SELECT * FROM `Sheet1$`"

    ' populate body of document with fields from data source

    ' first get field names from worksheet
    Set wkbk = Excel.Workbooks.Open(ActiveWorkbook.Path & "\Mail_Merge_Data.xls")
    Set headerRange = Excel.Range(wkbk.Sheets("Sheet1").Range("A1"), wkbk.Sheets("Sheet1").Range("IV1").End(xlToLeft))
    headerValues = Application.Transpose(headerRange.Value)
    wkbk.Close False

    ' put header values onto worksheet along with merge fields
    For i = 1 To UBound(headerValues)

      ' field name
      msWord.Selection.TypeText Text:=headerValues(i, 1) & ": "
      ' field value
      wordDoc.Fields.Add Range:=msWord.Selection.Range, _
                         Type:=wdFieldMergeField, _
                         Text:="""" & Replace(headerValues(i, 1), " ", "_") & """"
      ' line break
      msWord.Selection.TypeParagraph
    Next i

    ' perform mail merge
    With wordDoc.MailMerge
      .Destination = wdSendToNewDocument  ' wdSendToPrinter if you want to print instead
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
    End With

    ' show merged document
    msWord.Visible = True

  End If

End Sub

This code opens Mail_Merge_Difficult.doc/.docx (which you put in the same folder as the code, right?) and simulates the manual steps of a mail merge.

Step 1: The Document Type — Form letters, or the wdFormLetters constant
Step 2: The Data Source — the Mail_Merge_Data.xls/.xlsx file and the SQL statement that selects the data from sheet 1

Since it's a blank document, we open the source workbook and pull the header values in order to populate the document. The merge fields that correspond to each header are also added into the document. This is actually a great way to create a mail merge on any data source without knowing the field names or values beforehand; the code sets all of that up for you and puts the header values neatly into the document all on its own.

To generate some of this code, I performed a mail merge with Word's macro recorder turned on and pasted the code into Excel. I'm not that familiar with Word's object model, so the code to add fields and create line breaks also comes from recorded code.

Sample mail merge docs for Office 2003
Sample mail merge docs for Office 2007