Microsoft Word: Mail Merge into single documents

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.

Attention

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:

Alternative approach

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.

Oliver /
18. January 2013 /
Development, Microsoft, Microsoft Word, Software, Solutions /

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

Pingback: Oliver (@oliverattweetin)
Oliver Raduner says:

4. September 2013 at 20:22

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

Reply
tarek says:

7. October 2013 at 09:26

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

Reply
Oliver says:

7. October 2013 at 13:26

@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

Reply
tarek says:

7. October 2013 at 14:59

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?

Reply
tarek says:

8. October 2013 at 11:31

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?

Reply
Oliver says:

8. October 2013 at 23:38

@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:

Reply
Oliver Raduner says:

8. October 2013 at 23:42

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

Reply

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 https://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 & "\"

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 & 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 & "\"

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 & 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

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

Oliver Raduner says:

6. December 2013 at 14:38

Awesome! Thank you, Raymond, for sharing the code! And I am happy that you got it working.

Reply
Laura-J says:

22. October 2020 at 21:32

I typically discount solutions that are this dated, as things often change but, after spending countless hours researching & testing code to solve this problem using Windows/Office 365 (Word 2016). Oliver’s code looked more promising than anything I had tried yet. It didn’t quite get me what I needed and I even went so far as to try running it on my kids Mac.

I ended up running Spanky Raymond’s code back on my laptop and it worked ever so BRILLIANTLY!!!! I’m commenting so that others that have dug themselves into a deep mail merge hole, as I had, can find some light.

Thank you both (hoping against hope that you are still around to find my praise) for sharing your skills / solutions with us!

Reply
1. Oliver says:
  
  23. October 2020 at 23:10
  
  Hi Laura & thanks for your positive feedback 😊
  In recent years I reused this macro as well and found it kept working and doing it’s job… therefore of course letting this online for anyone facing the same challenge.
  
  And yes, still here accepting and reading comments 😉
  Oliver

Luka says:

16. December 2013 at 14:36

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?

Reply
1. Oliver Raduner says:
  
  16. December 2013 at 20:56
  
  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
  
  Reply
Greg Compy says:

6. February 2014 at 21:59
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.
Reply

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

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

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

kleinermann says:

16. May 2014 at 08:09
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
```
Reply
Ashlee says:

8. October 2015 at 22:50

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!

Reply
MSH says:

3. June 2016 at 23:23

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

Reply

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

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

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

Oliver says:

10. May 2017 at 00:15

Hi jc
To use a field for the file name, change line 31 from the macro code to something like this:

docNameField = ActiveDocument.MailMerge.DataSource.DataFields("Field Name").Value

Hope this helps!
Oliver

Reply

Gretchen says:

1. May 2017 at 10:03

I tried this macro and it works. Just to confirm, you have to manually run each document (record)?
I have a few hundred mail merge documents to do. Can you modify your app, or do you know if an app for mac exists that would allow you to name each record (created document) based on a field in the excel file?

Reply
1. Oliver says:
  
  9. May 2017 at 22:46
  
  Hi Gretchen
  I do not understand your first question completely – what the macro does is exactly writing each mail merge record into 1 single document.
  
  I may answer the second question however. You can easily use each record’s values coming from the excel file, to use in the file name. Change line 31 to something like this:
  
  docNameField = ActiveDocument.MailMerge.DataSource.DataFields("ExcelFile ColumnName").Value
  
  Hope this helps!
  Oliver
  
  Reply
Linn S says:

17. September 2022 at 19:10

I would just use merge tools instead. Check out https://mergeguru.com/ which is free. It can create your files automatically for you and even name them automatically.

Reply

Questions? Suggestions? Let us know with a comment!Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Here’s the VBA code for the Macro:

Alternative approach

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

Questions? Suggestions? Let us know with a comment!Cancel reply

Footer widgets