Microsoft Word: Mail Merge into single documents

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:

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.

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

  1. Pingback: Oliver Raduner (@oliverattweetin)

  2. Hi,
    I have tested the macro: it works as far as it concerns the name of the file (every file is saved with the new name according with the mergefield I have specified) but … all files are equal! apparently, it doesn’t move to the next record.

  3. @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 🙂

  4. Kindly clarify in step by step how to (manually set the “Mail Merge Output” setting to “Current Record”)?

  5. @tarek: in Office for Microsoft Windows, you can find the settings as shown in this screenshot:

    1) In the Mail Merge Ribbon, click on “Finish & Merge”
    2) Click on “Edit individual Documents…”
    3) Choose the “Current Record” radiobutton and confirm with OK

    Current Record Settings in Windows Office

  6. Thank you for the instant reply. I really appreciate it. Yet I have done what you’ve just suggested and after that the code, when runs, is saving seperate files that are all equally the same (copies of the same record). Any help please?

  7. Hi again, please I just want to know the step by step how to insert the code and how to run the code, because if we have to do it manually then what is the use of the code?

  8. @tarek:

    In order to use a Macro, you first have to enable the “Developer” Ribbon vai the Word Options:

    Add a new Macro (Name can be anything):

    Copy-paste the Macro-Code and Save:

    Now you can run the Macro:

  9. Ok I tested it again with Microsoft Word 2012 on WINDOWS and I have to admit unfortunately, that the Macro-Code does NOT work with that Version of MS Word 🙁

    I would need to investigate further – but the problem is definitely, that the “lastRecord” is not being calculated correctly, according to the Datasource (list of addresses, or similiar) used for Mail Merge.

    SORRY

  10. First let me give credit where credit is due because I know absolutely nothing of writing macros. In fact this is my first attempt at using a macro let alone modifying the code. Armed only with 24 year old knowledge of Basic (yes the original, not Visual Basic) and Fortran (no not the punch card Fortan but really close) I took Mr. Raduner macro above, Remou macro code for producing pdf’s at the following link http://stackoverflow.com/questions/14643524/losing-format-with-ms-word-mailmerge-macro, and a few others and combined different aspects and PRESTO!!! I clearly got very lucky but it works in MS Word 2010. Hope it works for everyone else as well. I’m loading both individual pdf creator and individual word file creator. I hope Mr. Raduner will work his magic, clean this up and make it more user friendly for everyone else as he clearly knows way more than I do.

    INDIVIDUAL WORD FILE MACRO (note you must have a “FileName” Column in your Excel data source):

    Sub SaveIndividualWordFiles()
    Dim iRec As Integer
    Dim docMail As Document
    Dim docLetters As Document
    Dim savePath As String

    Set docMail = ActiveDocument
    ''There is a problem with the recordcount property returning -1
    ''http://msdn.microsoft.com/en-us/library/office/ff838901.aspx

    savePath = ActiveDocument.Path &amp; "\"

    docMail.MailMerge.DataSource.ActiveRecord = wdLastRecord
    iRec = docMail.MailMerge.DataSource.ActiveRecord
    docMail.MailMerge.DataSource.ActiveRecord = wdFirstRecord

    For i = 1 To iRec
        With docMail.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                '' This will be the file name
               '' the test data source had unique surnames
               '' in a field (column) called FileName
               sFName = .DataFields("FileName").Value
            End With
            .Execute Pause:=False
            Set docLetters = ActiveDocument
        End With

    ' Save generated document and close it after saving
               docLetters.SaveAs FileName:=savePath &amp; sFName
                docLetters.Close False

        docMail.MailMerge.DataSource.ActiveRecord = wdNextRecord
    Next
    End Sub

    INDIVIDUAL PDF FILE MARCO: (note you must have a “FileName” Column in your Excel data source):

    Sub SavePdfIndividualFiles()

    Dim iRec As Integer
    Dim docMail As Document
    Dim docLetters As Document
    Dim savePath As String


    Set docMail = ActiveDocument

    ''There is a problem with the recordcount property returning -1
    ''http://msdn.microsoft.com/en-us/library/office/ff838901.aspx

    savePath = ActiveDocument.Path &amp; "\"

    docMail.MailMerge.DataSource.ActiveRecord = wdLastRecord
    iRec = docMail.MailMerge.DataSource.ActiveRecord

    docMail.MailMerge.DataSource.ActiveRecord = wdFirstRecord

    For i = 1 To iRec
        With docMail.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                '' This will be the file name
               '' the test data source had unique FileName
               '' in a field (column) called FileName
               sFName = .DataFields("FileName").Value
            End With
            .Execute Pause:=False
            Set docLetters = ActiveDocument
        End With


        docLetters.ExportAsFixedFormat OutputFileName:= _
            savePath &amp; sFName &amp; ".pdf", ExportFormat:= _
            wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
            wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
            Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
            CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
            BitmapMissingFonts:=True, UseISO19005_1:=False
        docLetters.Close False

        docMail.MailMerge.DataSource.ActiveRecord = wdNextRecord
    Next
     
    End Sub
  11. Have word 2007. When I run the VBA code for the Macro (MailMergeSaveEachRecordToFile()), Word generate single files with document name as I choose. But inside each of them are all the pages and not just one form.
    So I have like 50 equal documents with different file names.
    This happens with “Mail Merge Output” setting to “All”.
    If I set the “Mail Merge Output” setting to “Current Record”, I’ve got just one new document with one form in it.
    Am I doing something wrong?

  12. Luka,
    Have you tried generating the Mail Merge without the Macro and verify the output? It might be, that you missing the “Next record” field in the bottom of your template.

    Regards,
    Oli

  13. I’ve just used your original code in Word2010, and many thanks for putting it together. I looked for quite a while for something clear and simple. While my last programming was in Basic (before QuickBasic even), I did manage a simple improvement which seems to avoid the whole “current record” issue. You can leave it set to “All”.

    All of my individual documents appeared the same when opened, which forced me to try this:

    With ActiveDocument.MailMerge

    .DataSource.FirstRecord = rec
    .DataSource.lastRecord = rec
    .Destination = wdSendToNewDocument
    .Execute

    End With

    The DataSource.FirstRecord and lastRecord pointers (probably the wrong term) are all that I put in there, and now the rec variable insures that I get the right record. Works great now. The only other change I made was to hard-code the filepath since I wasn’t getting any kind of prompt. Anyway, maybe someone else can profit by this.

  14. Oliver,

    In other words, I used the code you show at the top as-is, with only the addition of two lines. I’m running W7, Word 2010.
    Original:
    ‘ Execute Mail Merge action
    With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .Execute
    End With

    Modified:
    ‘ Execute Mail Merge action
    With ActiveDocument.MailMerge
    .DataSource.FirstRecord = rec
    .DataSource.lastRecord = rec
    .Destination = wdSendToNewDocument
    .Execute
    End With

    I don’t think the problem you mention at the top is a problem anymore. Can you verify?
    Greg

  15. This Part

    docLetters.ExportAsFixedFormat OutputFileName:= _

    savePath & sFName & “.pdf”, ExportFormat:= _

    wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _

    wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _

    Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _

    CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _

    BitmapMissingFonts:=True, UseISO19005_1:=False

    is not working for word for mac…does anybody know why?

    Thanks

  16. Hey tried the code you posted on December 6 for single PDF files. I cannot run it as it gives me an error in this part

    docLetters.ExportAsFixedFormat OutputFileName:= _
    savePath & sFName & “.pdf”, ExportFormat:= _
    wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
    wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
    Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
    CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
    BitmapMissingFonts:=True, UseISO19005_1:=False
    docLetters.Close False

    I am using Windows for mac. Anyone an Idea? I am helpful for every hint.

    Cheers
    Giovanni

  17. Hello,
    I am trying to merge letters into separate files… and I keep finding this macro. I know nothing about macros … how do I use one?

  18. Thanks, this post was very helpful. In my case i had to customise the macro script a little in order to save the documents as PDF’s instead.

    I changed two sections in the script as below:

    First replace both appearances of .docx with .pdf so the exported files have the correct file name extension:

    strDocName = “document” & rec & “.pdf”
    Else
    strDocName = ActiveDocument.MailMerge.DataSource.DataFields(docNameField).Value & “.pdf”

    Then add the bit that let’s Word know to export as a PDF:

    ActiveDocument.SaveAs FileName:=savePath & strDocName, FileFormat:=wdFormatPDF

  19. I just wanted to say thank you to Oliver for your original post and Greg for his fix from Feb 2014 – you just saved me a ton of time!

  20. First I would like to say Thanks to Greg and Oliver for making the script and for fixing it for windows.

    While running the macro I noticed if there was 2 of the same fields it replaces it instead of making another.
    For example I want to save the file according to the Name
    If the Name data field have 2 John when it creates the second John it will overwrite the first one and I am left with only 1 John.docx

  21. So what I did to get around this and create a new entry would be:

    Else

    strDocName = ActiveDocument.MailMerge.DataSource.DataFields(docNameField).Value & “.docx”

    If Not Dir(savePath & strDocName) = “” Then ‘filename exists
    i = i + 1
    strDocName = ActiveDocument.MailMerge.DataSource.DataFields(docNameField).Value & i & “.docx”
    End If

    End If

    This way it checks if file is present in dir, if so add i into filename

    Cheers
    -Harold

  22. Hi,

    What do I change in the macro to save the file name using fields within the form?

    I read that I could either stipulate that using:

    1. the actual merge field within the document: <> and <>

    OR

    2. the line on which the text sits on: so if my <> and <> field sits on the 3rd line of the document.

    Sub BreakOnSection()
    ‘ Select a folder
    Dim strFolder As String
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
    .Title = “Select the folder into which the documents will be saved.”
    If .Show = -1 Then
    strFolder = .SelectedItems(1) & “\”
    Else
    MsgBox “The documents will be saved in the default document file location.”
    strFolder = “c:\”
    End If
    End With
    ChangeFileOpenDirectory strFolder

    ‘Used to set criteria for moving through the document by section.
    Application.Browser.Target = wdBrowseSection

    ‘A mailmerge document ends with a section break next page.
    ‘Subtracting one from the section count stop error message.
    For i = 1 To ((ActiveDocument.Sections.Count) – 1)

    ‘Select and copy the section text to the clipboard
    ActiveDocument.Bookmarks(“\Section”).Range.Copy

    ‘Create a new document to paste text from clipboard.
    Documents.Add
    Selection.PasteAndFormat (wdFormatOriginalFormatting)

    ‘Removes the break that is copied at the end of the section, if any.
    Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend
    Selection.Delete Unit:=wdCharacter, Count:=1

    DocNum = DocNum + 1
    ‘ ActiveDocument.SaveAs FileName:=”test_” & DocNum & “.doc”
    ActiveDocument.SaveAs
    ActiveDocument.Close
    ‘Move the selection to the next section in the document
    Application.Browser.Next
    Next i
    ActiveDocument.Close savechanges:=wdDoNotSaveChanges
    End Sub

Leave a Reply

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