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

0 votes

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'

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 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.

Nov 6 in Others by Kithuzzz
• 20,660 points
38 views

1 answer to this question.

0 votes

Try this:

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
answered Nov 6 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
101 views
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7 in Others by narikkadan
• 37,660 points
53 views
0 votes
1 answer

How to export data with functions to another workbook in excel

The issue appears to be that once ...READ MORE

answered Oct 30 in Others by narikkadan
• 37,660 points
31 views
0 votes
0 answers
0 votes
1 answer

How can I find and replace text in Word using Excel VBA?

Try this code Option Explicit Const wdReplaceAll = 2 Sub ...READ MORE

answered Oct 15 in Others by narikkadan
• 37,660 points
85 views
0 votes
1 answer

Excel Arrow Key is Creating a Formula?

Your scroll lock is on. This is ...READ MORE

answered Oct 23 in Others by narikkadan
• 37,660 points
38 views
0 votes
1 answer

Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

answered Oct 28 in Others by narikkadan
• 37,660 points
40 views
0 votes
1 answer

Trying to create an enclosing bookmark after pasting a picture from Excel into Word using VBA

Try this: Sub IMPORT_TO_WORD() Dim ws1 As Worksheet, ws2 ...READ MORE

answered Oct 30 in Others by narikkadan
• 37,660 points
33 views
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14 in Others by narikkadan
• 37,660 points
31 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
56 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP