I have a very unique issue: I have an Excel worksheet with tonnes of data and a letter written in Word. I have to use the data from the worksheet in a Mail Merge in Word. The issue is that each Mail Merge must be saved as a separate Word file, and each file must be saved with a name derived from the Mail Merge's input data.
For instance, my Excel spreadsheet has a table with three columns labelled Name, Surname, and Birthday. Ten rows make up this table. I must use Mail Merge in Word to add the recipient's name, last name, and birthday. Each Mail Merge must be saved in a separate file, which means that in the end, there will be 10 files total—one for each row. Every file needs to have the relative Surname taken from the Mail Merge as the name.
I found this VBA code online and tried it in Word:
' Modulo1 - Modulo'
Public Sub Test()
On Error GoTo ErrH
Dim mm As Word.MailMerge
Application.ScreenUpdating = False
Set mm = ThisDocument.MailMerge
.Destination = wdSendToNewDocument
For i = 1 To .RecordCount
.FirstRecord = i
.LastRecord = i
.SaveAs "C:\Users\Alessandro\Desktop\excel udine\TRIESTE\" & Format(i, "0000") _
, wdFormatDocument _
.Saved = True
Application.ScreenUpdating = True
Set mm = Nothing
This code can save every Mail Merge exactly like I need to. The only problem is that a filename is a number like 0001, 0002, etc.; I need to set that name to a value I store in the Excel Worksheet and use in the Mail Merge.