Recently 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.
- Unfortunately this Macro does not work with Microsoft Office 2010+ on Windows! (Reason is the next point #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”)
Here’s the VBA code for the Macro:
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
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.