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.

18 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?

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>