How to split mail merge into separate documents

[Stewart67]

Hello

I am looking for some help on how to split a Word mail merge document into separate files that can be saved under individual names. The merged data comes from an Excel spreadsheet which has a number of tabs and all the merge data is in one of the tabs.

There are 20 separate documents that need to be created.

Ideally, I would like to be able to do this directly from the mail merge document (as opposed to after creating the full document via the 'Finish & Merge' button) and save each document under a unique file name. But doing it from the 'Finish & Merge' file would also be fine. At the moment, I have to 'Finish & Merge', then delete all the documents except one, and then save it with the name I want. Then re-open the 'Finish & Merge' document, and repeat until I have created the 20 individually named documents. That's very tedious!

Is there a way of automating this?

Thanks
Stewart

 

[Stewart67]

Hello - after trying to find a solution all day, I have now found one. So I will post it below.

This macro creates and saves the Mail merge documents separately with unique, defined file names as both Word and Pdf documents.

You just need to open the Mail merge document and then run the macro. I have got it working (for a change)!

1. Create two folders to save the Word and pdf files that will be generated. I used 'Doc & 'Pdf'.

2. Add the below columns with these exact title names into your Excel spreadsheet data source

You will need to copy and paste the paths of the folders created in Step 1.

3. Go into your Mail merge Word file, then Developer, then insert a Module and copy and paste the code below.

4. Then just go into Developer and run the macro. Amazingly it worked first time for me!

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