Macro 98: Simulating Mail Merge with a Word Document
One of the most requested forms of integration with Word is the mail merge. In most cases, mail
merge refers to the process of creating one letter or document and then combining it with a separate
document containing their names and addresses. For example, suppose you had a list of customers
and you wanted to compose a letter to each customer. With mail merge, you can write the body of
the letter one time, and then run the mail merge feature in Word to automatically create a letter for
each customer, affixing the appropriate, address, name and other information to each letter.
For you automation buffs, you can use a macro to simulate the Word mail merge function from Excel.
How it works
The idea is relatively simple. You start with a template that contains bookmarks identifying where
each element of contact information should go. After the template is ready, the idea is to simply loop
through each contact in your contact list, assigning the component pieces of their contact informa-
tion to the respective bookmarks.
Note that you will need to set a reference to the Microsoft Word Object Library. To do so, open the
Visual Basic Editor in Excel and select Tools➜References. The Reference dialog box opens. Scroll
down until you find the entry Microsoft Word XX Object Library, where the XX is your version of Word.
Select the check box next to the entry.
Sub Macro98()
‘Step 1: Declare your variables
Dim wd As Word.Application
Dim wdDoc As Word.Document
Dim MyRange As Excel.Range
Dim MyCell As Excel.Range
Dim txtAddress As String
Dim txtCity As String
Dim txtState As String
Dim txtPostalCode As String
Dim txtFname As String
Dim txtFullname As String
‘Step 2: Start Word and add a new document
Set wd = New Word.Application
Set wdDoc = wd.Documents.Add
wd.Visible = True
‘Step 3: Set the range of your contact list
Set MyRange = Sheets(“Contact List”).Range(“A5:A24”)
‘Step 4: Start the loop through each cell
For Each MyCell In MyRange.Cells
‘Step 5: Assign values to each component of the letter
txtAddress = MyCell.Value
txtCity = MyCell.Offset(, 1).Value
txtState = MyCell.Offset(, 2).Value
txtPostalCode = MyCell.Offset(, 3).Value
txtFname = MyCell.Offset(, 5).Value
txtFullname = MyCell.Offset(, 6).Value
‘Step 6: Insert the structure of template document
wd.Selection.InsertFile _
ThisWorkbook.Path & “\” & “MailMerge.docx”
‘Step 7: Fill each relevant bookmark with respective value
wd.Selection.Goto What:=wdGoToBookmark, Name:=”Customer”
wd.Selection.TypeText Text:=txtFullname
wd.Selection.Goto What:=wdGoToBookmark, Name:=”Address”
wd.Selection.TypeText Text:=txtAddress
wd.Selection.Goto What:=wdGoToBookmark, Name:=”City”
wd.Selection.TypeText Text:=txtCity
wd.Selection.Goto What:=wdGoToBookmark, Name:=”State”
wd.Selection.TypeText Text:=txtState
wd.Selection.Goto What:=wdGoToBookmark, Name:=”Zip”
wd.Selection.TypeText Text:=txtPostalCode
wd.Selection.Goto What:=wdGoToBookmark, Name:=”FirstName”
wd.Selection.TypeText Text:=txtFname
‘Step 8: Clear any remaining bookmarks
On Error Resume Next
wdDoc.Bookmarks(“Address”).Delete
wdDoc.Bookmarks(“Customer”).Delete
wdDoc.Bookmarks(“City”).Delete
wdDoc.Bookmarks(“State”).Delete
wdDoc.Bookmarks(“FirstName”).Delete
wdDoc.Bookmarks(“Zip”).Delete
‘Step 9: Go to the end, insert new page, and start with the next cell
wd.Selection.EndKey Unit:=wdStory
wd.Selection.InsertBreak Type:=wdPageBreak
Next MyCell
‘Step 10: Set cursor to beginning and clean up memory
wd.Selection.HomeKey Unit:=wdStory
wd.Activate
Set wd = Nothing
Set wdDoc = Nothing
End Sub
1. Step 1 declares four variables: wd is an object variable that exposes the Word Application
object, wdDoc is an object variable that exposes the Word Document object, MyRange con-
tains the range defining the contact list, and MyCell is used to pass cell values into the
string variables. We also declare six string variables. Each of the string variables holds a com-
ponent piece of information for each contact in the contact list.
2. This step opens Word with a blank document. Note that we set the Visible property of the
Word application to True. This ensures that we can see the action in Word as the code runs.
3. Step 3 defines each contact in the contact list. Note that this range only selects the first col- umn
in the contacts table. This is because each cell in the range must be passed individually to string
variables. Selecting only the first column gives us one cell per row. From that one cell, we can
easily adjust the cursor to the right or left to capture the cells around it. The idea is that if we
move to the right one space, we get the value of the next field in that row. If we move to the
right two spaces, we get the value of that field, and so on.
4. This step starts the loop through each contact as defined in the range set in Step 3.
5. Step 5 uses Excel’s Offset method to capture the value of each field in a particular row. We
start with the range defined in Step 3 (the first column in the list of contacts). We then use
Offset to move the cursor a certain number of columns to the right to capture the data in
each relevant field. As each field is covered, we assign their values to the appropriate string
variable.
6. In Step 6, we insert the existing template into the empty document in Word. This is tanta-
mount to copying the structure of our template and pasting it into a blank document.
7. Step 7 assigns the value of each string variable to its respective bookmark. As you can see in
the code, this step selects the bookmark by name, and then changes the text to equal the
value of the assigned string variable.
8. The goal in Step 8 is to remove any stray bookmarks. If any bookmarks linger, we get dupli-
cate bookmarks as the procedure loops through each cell.
9. At this point in the code, we have created a document for one contact in our list of contacts.
The idea now is to create a new blank document so that we can perform the same procedure
for the next contact. Inserting a page break effectively creates the new blank document. We
then loop back to Step 5, where we pick up the contact information for the next row in the
list. Then at Step 6, we insert the blank template (complete with bookmarks) into the new
page. Finally, we assign values to the bookmarks and clean up. The For…Next loop
ensures that this cycle is repeated for each row in the contact list.
10. Step 10 releases the objects assigned to your variables, reducing the chance of any problems
caused by rogue objects that may remain open in memory.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code in the newly created module.
