Automate mail merge to save each record individually using VBA

user4829 · #1 12-01-2021, 08:15 PM

Hi,

I am trying to automate mail merge to save each record individually using VBA. I know there are a lot of guides and tutorials out there, I have tried a few and had no luck. The best success I've had so far is the code below, pulled from

HTML Code:

https://swissmacuser.ch/microsoft-word-mail-merge-into-single-documents/

.

This currently works to export a document from mail merge and save as individual file, it just appears to save the same document over the top of each other. E.g. I will end up with only one merged document at the end as the others were saved over during the process. I can see in preview mode when I run the macro, the file saving over itself and updating content.

Here is my code:

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

ActiveDocument.MailMerge.DataSource.lastRecord = wdLastRecord
iRec = ActiveDocument.MailMerge.DataSource.ActiveRecord

ActiveDocument.MailMerge.DataSource.FirstRecord = wdFirstRecord

For i = 1 To iRec
With ActiveDocument.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("Title").Value
End With
.Execute Pause:=False
Set docLetters = ActiveDocument
End With

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

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to mail merge next record on same page to save paper	klc9761	Mail Merge	1	04-22-2017 02:29 PM
Showing record number during mail merge	catflap	Mail Merge	1	04-13-2017 07:32 AM
Mail Merge Next Record If rule	RHensley	Mail Merge	10	03-07-2017 08:05 AM
Automate daily mail merge	JCInfo	Mail Merge	4	12-02-2013 05:12 PM
avoid duplicete record and merge the record with the existed record	hemant.behere	Excel	0	01-10-2012 02:53 AM