How To ...

Word Mail Merge to Separate PDFs with Custom File Names and Folder Locations

Convert your Word Mail Merge into separate PDF documents! No third-party plug-in, no complexity, and no need to go moving and renaming documents.

Introduction

You would think that converting a mail merge to separate PDF documents would be easy. However, a quick search gives up solutions which are either ridiculously complex or require a third-party plug-in (often paid for). Worst of all, even if you get that to work you will end up with a folder containing a load of files named something completely useless like document 1, document 2, document 159. You will then spend a day renaming files before you can email them out or file them away.

So we avoid all those problems and give you a one-button solution which handles the whole thing, saving your PDF files with the names you want and in the folders you want (you can even have different folders for different files).

This is done using a Word Macro, the text of which is below. Read on, or watch the video, to find out how to add the macro to Word, set up your mail merge and then sit back as the computer does the work for you.

The Video

The Super Quick Version

Copy the text of the Macro from below and add to Word.
Add the following fields to your Mail Merge data: DocFolderPath, DocFileName, PdfFolderPath, PdfFileName.
In the FileName fields, put the file names you want for the separated Word Docs and converted PDFs. No need to include the extensions.
In the FolderPath fields put valid folder paths (e.g. C:\Users\me\Documents). These can be the same for all rows, or different – the choice is yours.
Create your Mail Merge.
Run the Macro
Sit back and let the computer work.

The Macro

****** UPDATE! – Windows code updated from what is shown in the video ******

****** UPDATE! – Mac code now added ******

Code for Windows

				
					Sub MailMergeToPdfBasic()                                                        ' Mark the start of the Subroutine (i.e. Macro) and name it "MailMergeToPdf"
' Macro created by Imnoss Ltd
' Please share freely while retaining attribution
' Last Updated 2021-05-03
    Dim masterDoc As Document, singleDoc As Document, lastRecordNum As Long   ' Create variables ("Post-it Notes") for later use
    Set masterDoc = ActiveDocument                                               ' Identify the ActiveDocument (foremost doc when Macro run) as "masterDoc"
    
    masterDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord                   ' jump to the last active record (active = ticked in edit recipients)
    lastRecordNum = masterDoc.MailMerge.DataSource.ActiveRecord                  ' retrieve the record number of the last active record so we know when to stop
    
    masterDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord                  ' jump to the first active record (active = ticked in edit recipients)
    Do While lastRecordNum > 0                                                   ' create a loop, lastRecordNum is used to end the loop by setting to zero (see below)
        masterDoc.MailMerge.Destination = wdSendToNewDocument                    ' Identify that we are creating a word docx (and no e.g. an email)
        masterDoc.MailMerge.DataSource.FirstRecord = masterDoc.MailMerge.DataSource.ActiveRecord              ' Limit the selection to just one document by setting the start ...
        masterDoc.MailMerge.DataSource.LastRecord = masterDoc.MailMerge.DataSource.ActiveRecord               ' ... and end points to the active record
        masterDoc.MailMerge.Execute False                                        ' run the MailMerge based on the above settings (i.e. for one record)
        Set singleDoc = ActiveDocument                                           ' Identify the ActiveDocument (foremost doc after running the MailMerge) as "singleDoc"
        singleDoc.SaveAs2 _
            FileName:=masterDoc.MailMerge.DataSource.DataFields("DocFolderPath").Value & Application.PathSeparator & _
                masterDoc.MailMerge.DataSource.DataFields("DocFileName").Value & ".docx", _
            FileFormat:=wdFormatXMLDocument                                      ' Save "singleDoc" as a word docx with the details provided in the DocFolderPath and DocFileName fields in the MailMerge data
        singleDoc.ExportAsFixedFormat _
            OutputFileName:=masterDoc.MailMerge.DataSource.DataFields("PdfFolderPath").Value & Application.PathSeparator & _
                masterDoc.MailMerge.DataSource.DataFields("PdfFileName").Value & ".pdf", _
            ExportFormat:=wdExportFormatPDF                                      ' Export "singleDoc" as a PDF with the details provided in the PdfFolderPath and PdfFileName fields in the MailMerge data
        singleDoc.Close False                                                    ' Close "singleDoc", the variable "singleDoc" can now be used for the next record when created
        If masterDoc.MailMerge.DataSource.ActiveRecord >= lastRecordNum Then     ' test if we have just created a document for the last record
            lastRecordNum = 0                                                    ' if so we set lastRecordNum to zero to indicate that the loop should end
        Else
            masterDoc.MailMerge.DataSource.ActiveRecord = wdNextRecord           ' otherwise go to the next active record
        End If
    
    Loop                                                                         ' loop back to the Do start
End Sub                                                                          ' Mark the end of the Subroutine

Code for Mac

				
					Sub MailMergeToPdf()                                                        ' Mark the start of the Subroutine (i.e. Macro) and name it "MailMergeToPdf"
    Dim masterDoc As Document, recordNum As Long, singleDoc As Document  ' Create variables ("Post-it Notes") for later use
    Set masterDoc = ActiveDocument                                          ' Identify the ActiveDocument (foremost doc when Macro run) as "masterDoc"
    
    masterDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord
    
    For recordNum = 1 To masterDoc.MailMerge.DataSource.ActiveRecord         ' Set "recordNum" to 1 and start loop | second part defines end point for loop
        masterDoc.MailMerge.DataSource.ActiveRecord = recordNum             ' Change the active record in the MailMerge to the "recordNum"
        masterDoc.MailMerge.Destination = wdSendToNewDocument               ' Identify that we are creating a word docx (and no e.g. an email)
        masterDoc.MailMerge.DataSource.FirstRecord = recordNum              ' Limit the selection to just one document by setting the start ...
        masterDoc.MailMerge.DataSource.LastRecord = recordNum               ' ... and end points to the same number (which is the same as the active record)
        masterDoc.MailMerge.Execute False                                   ' run the MailMerge based on the above settings (i.e. for one record)
        Set singleDoc = ActiveDocument                                      ' Identify the ActiveDocument (foremost doc after running the MailMerge) as "singleDoc"
        singleDoc.SaveAs _
            FileName:=masterDoc.MailMerge.DataSource.DataFields("DocFolderPath").Value & "/" & _
                masterDoc.MailMerge.DataSource.DataFields("DocFileName").Value & ".docx", _
            fileFormat:=wdFormatXMLDocument                                 ' Save "singleDoc" as a word docx with the details provided in the DocFolderPath and DocFileName fields in the MailMerge data
        singleDoc.SaveAs _
            FileName:=masterDoc.MailMerge.DataSource.DataFields("PdfFolderPath").Value & "/" & _
                masterDoc.MailMerge.DataSource.DataFields("PdfFileName").Value & ".pdf", _
            fileFormat:=wdFormatPDF                                              ' Export "singleDoc" as a PDF with the details provided in the PdfFolderPath and PdfFileName fields in the MailMerge data
        singleDoc.Close False                                               ' Close "singleDoc", the variable "singleDoc" can now be used for the next record when created
    Next recordNum                                                          ' Add one to "recordNum" and return to the beginning of the loop (line under "For recordNum = 1 ..."
End Sub                                                                     ' Mark the end of the Subroutine

The Long Version

Adding the Macro

To add a macro to Word you will need to be able to see the Developer tab. If you don’t have this appearing, that’s perfectly normal for Word, they hide it by default. To make it appear is you just right-click anywhere in the ribbon and select “Customise the Ribbon”. In the dialogue box which appears make sure the checkbox next to Developer in the right-hand column is checked. Once done press Ok, and the Developer tab will appear in the Ribbon. This contains the Visual Basic and Macro buttons on the left-hand side.

Click the Visual Basic button in the Developer tab to open the Visual Basic editor where we will add our macro. If you do not see a window called “Project” then click on the View menu and select “Project Explorer”. This will bring up the Project window. In this window right-click on “Normal” and in the contextual menu select “Insert” and then “Module”. A new file called “Module1” will appear in the Modules folder under “Normal” (the file may have a different number if Module1 already exists).

The middle of the window will be a white canvas – this is the editing space for the new module. Copy the Macro from this article and paste it into the middle of the VBA editor.

Right click and select "Customise the Ribbon"

Make sure the checkbox next to Developer in the right-hand column is checked

If needed, click on the View menu and select "Project Explorer"

Select "Insert" and then "Module" in the contextual menu

A new module (i.e. a file) called "Module1" will appear in the Modules folder under "Normal"

Copy the macro from this article and paste it into Module1 the VBA editor

Preparing the Data

Start by preparing your data exactly as you would for a normal mail merge – i.e. a sheet in Excel with headers in the first row and data underneath.

For the macro to work, you will need to add in four extra columns named: DocFolderPath, DocFileName, PdfFolderPath, and PdfFileName. These should be exactly as written here, with no spaces and each word capitalised.

The fields DocFolderPath and PdfFolderPath should contain paths to folders which exist on your system. If you have a suitable folder open you can click in the address bar and copy the folder path from there. The folder paths may be the same or different for different rows. If you wish, you can generate folder paths using formulas. You will need to make sure that each folder exists for the macro to work.

The fields DocFileName and PdfFileName should contain a name for the Word doc and PDF file respectively. If the files are all going into the same folder, the names need to be unique to avoid overwriting. These can be created by copying and pasting e.g. a name, or generated by a formula as shown in the images. There is no need to add an extension (.docx or .pdf).

Word requires that a Word document be saved before converting to PDF so we have to save the Word documents, even if you don’t want them.

Prepare your as you would for any other mail merge, i.e. in a table in Excel

Add in four extra headers fields: DocFolderPath, DocFileName, PdfFolderPath, PdfFileName

For folders, copy the address from the address bar in Windows Explorer

You can use formulas to create any field, here the file name is created from "End of year letter to " and the person's name

Preparing the Mail Merge Template

Prepare your mail merge template as you would any other mail merge. The four extra fields can be ignored unless you want them to appear in the mail merge (I guess you might want the file name to appear in the header).

Running the Mail Merge to PDF

In the Developer tab click the “Macros” button. Select the macro “MailMergeToPdf” and click Run.

Sit back and let the magic happen.

Select the macro "MailMergeToPdf" and click Run

Share This Post