Microsoft Office Word LogoRecently I had to create a lot of Microsoft Office Word documents based on the same template, so I decided to use Mail Merge. Unfortunately when you generate a document from a defined Mail Merge template in Word, you end up having all the pages inside one huge document. But I wanted to have each form in a seperate word document.

To achieve this non-standard behavior, I wrote a little helper: execute the following VBA Macro on your Office Word Mail Merge template to have Word generate & save every record into a single file.

Attention

  1. Unfortunately this Macro does not work with Microsoft Office 2010+ on Windows! (Reason is the next point #2)
  2. There is one thing to do manually, because I couldn’t solve it programmatically: you have to manually set the “Mail Merge Output” setting to “Current Record”! (default is “All”)
    Office Word Mail Merge VBA manual output setting

Here’s the VBA code for the Macro:

[code lang="vb"]
Option Explicit
Sub MailMergeSaveEachRecordToFile()
'
' Save each single Mail Merge Record into a seperate Document
'
Dim rec, lastRecord As Integer
Dim docNameField, strDocName, savePath As String

' Choose Folder dialog (Mac and Windows)
If System.OperatingSystem Like "*Mac*" Then
    savePath = MacScript("(choose folder with prompt ""Select the folder"") as string")
Else 'Windows
    savePath = ActiveDocument.Path & "\"
End If

' If a destination folder has been selected
If savePath <> "" Then
    ' Turn off some visuals to speed things up a bit
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Find the last record of the Mail Merge data
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdLastRecord
    lastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
    
    ' Ask for user confirmation to start creating the documents
    If MsgBox(lastRecord & " documents will be created based on your Mail Merge template.", vbOKCancel) = vbOK Then
        ' Ask for the name of the Merge Field name to use for the document names
        docNameField = InputBox("Which Mergefield [name] should be used for document name?")
        
        ' Create document for each Mail Merge record (loop)
        For rec = ActiveDocument.MailMerge.DataSource.FirstRecord To lastRecord
            ActiveDocument.MailMerge.DataSource.ActiveRecord = rec
            
            ' Set document name for current record
            If Trim(docNameField) = "" Then
                strDocName = "document" & rec & ".docx"
            Else
                strDocName = ActiveDocument.MailMerge.DataSource.DataFields(docNameField).Value & ".docx"
            End If
            
            ' Execute Mail Merge action
            With ActiveDocument.MailMerge
                .Destination = wdSendToNewDocument
                .Execute
            End With
           
            ' Save generated document and close it after saving
            ActiveDocument.SaveAs FileName:=savePath & strDocName
            ActiveDocument.Close False
            
            ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
        Next rec
        
        ' Re-enable screen visuals
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        
    Else 'if no destination folder was selected
        'Re-enable screen visuals
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        Exit Sub
    End If
End If

End Sub[/code]

An alternative approach is to split the generated mail merge document based on the section breaks that word inserts when executing mail merging. For me this was not working because of special formatting and tables inside the tempalte document.

23 thoughts on “Microsoft Word: Mail Merge into single documents”

  1. @Nino: you missed one IMPORTANT thing 🙂 See in my article what I wrote under “Attention”:

    There is one thing to do manually, because I couldn’t solve it programmatically: you have to manually set the “Mail Merge Output” setting to “Current Record”! (default is “All”)

    If you don’t change this setting manually, it will copy all the mail merge letters into each of the documents – just as you described your problem! Hope it helps 🙂

Leave a reply

Your email address will not be published. Required fields are marked *