Word Mail Merge with Excel data has to be saved in different files with custom names

Question

I have a very specific problem: I have a letter written in Word and a worksheet full of data in Excel. I need to do a Mail Merge in Word that uses the worksheet's data. The problem is: every Mail Merge has to be saved as a different Word file and, also, every file has to be saved with a name from the data used in the Mail Merge.

So, for example: my Excel has a table with 3 columns called Name, Surname, Birthday. This table has 10 rows. I need to do the Mail Merge in Word with Name, Surname and Birthday. Every Mail Merge has to be saved in a different file (so, in the end, we'll have 10 files, 1 for each row). Every file has to be named as the relative Surname extracted from the Mail Merge.

I found this VBA code online and tried it in Word:

' Modulo1 - Modulo'

Option Explicit

Public Sub Test()

On Error GoTo ErrH

Dim mm As Word.MailMerge
Dim i

Application.ScreenUpdating = False

Set mm = ThisDocument.MailMerge
With mm
  .Destination = wdSendToNewDocument
  With .DataSource
    For i = 1 To .RecordCount
      .FirstRecord = i
      .LastRecord = i
      mm.Execute
      With Application.ActiveDocument
        .SaveAs "C:\Users\Alessandro\Desktop\excel udine\TRIESTE\" & Format(i, "0000") _
              , wdFormatDocument _
              , AddToRecentFiles:=False
        .Saved = True
        .Close
      End With
    Next
  End With
End With

ExitProc:
Application.ScreenUpdating = True
Set mm = Nothing
Exit Sub

ErrH:
MsgBox Err.Description
Resume ExitProc

End Sub

This code can save every Mail Merge exactly like I need to. The only problem is that the filename is a number like 0001, 0002, etc. and I need to set that name to a value I store in the Excel Worksheet and also use in the Mail Merge.

Answer 1

I managed to find a solution to my own problem. I still don't know why I couldn't make work the old code, but this code works perfectly:

Public Sub Mail_Merge()

On Error GoTo ErrH

Dim mm As Word.MailMerge
Dim singleDoc As Document
Dim i
Dim nameFile As String
Dim path As String

path = "WRITE PATH TO SAVE FILE"
nameFile = "WRITE COLUMN NAME FROM MAIL MERGE"

Application.ScreenUpdating = False

Set mm = ThisDocument.MailMerge

mm.DataSource.ActiveRecord = wdFirstRecord

For i = 1 To mm.DataSource.RecordCount
    mm.Destination = wdSendToNewDocument

    mm.DataSource.FirstRecord = mm.DataSource.ActiveRecord
    mm.DataSource.LastRecord = mm.DataSource.ActiveRecord
    
    mm.Execute False

    Set singleDoc = ActiveDocument

    singleDoc.SaveAs2 _
        FileName:=path & mm.DataSource.DataFields(nameFile).Value, _
        FileFormat:=wdFormatDocumentDefault, _
        AddToRecentFiles:=False

    singleDoc.Close False

    mm.DataSource.ActiveRecord = wdNextRecord
Next

ExitProc:
    Application.ScreenUpdating = True
    Set mm = Nothing
    Exit Sub
   
ErrH:
    MsgBox Err.Description
    Resume ExitProc
   
End Sub