I'm using the below code to automate the saving of a large number of documents produced by a mail merge.
The columns in excel are "Docfolderpath", which contains the file path to a folder to save that specific document after it's created in the merge, and "docfilename" is the filename it's saved as. One record is saved at a time.
The problem is that when I use a much larger spreadsheet (1000 rows with 25-30 columns of data), I get an overflow error when I start the macro. Bear in mind that I still get this error even when I filter for a specific metric in the mail merge
Any help to change the code would be appreciated, thanks
Here's a pic of a scaled-down version of the spreadsheet being used
Code
Sub MailMergeToPdf()
Dim masterDoc As Document, recordNum As Integer, singleDoc As Document
Set masterDoc = ActiveDocument
For recordNum = 1 To masterDoc.MailMerge.DataSource.RecordCount
masterDoc.MailMerge.DataSource.ActiveRecord = recordNum
masterDoc.MailMerge.Destination = wdSendToNewDocument
masterDoc.MailMerge.DataSource.FirstRecord = recordNum
masterDoc.MailMerge.DataSource.LastRecord = recordNum
masterDoc.MailMerge.Execute False
Set singleDoc = ActiveDocument
singleDoc.SaveAs2 _
FileName:=masterDoc.MailMerge.DataSource.DataFields("DocFolderPath").Value & "\" & _
masterDoc.MailMerge.DataSource.DataFields("DocFileName").Value & ".docx", _
FileFormat:=wdFormatXMLDocument
singleDoc.Close False
Next recordNum
End Sub
Display More