Microsoft Office Tutorials and References
In Depth Information
Creating Custom Mail Merge Using Data in Excel Worksheet
Creating Custom Mail Merge Using Data in Excel Worksheet
By Suat Ozgur
This code shows how to create a custom mail merge instead of using Word’s
internal Mail Merge feature. A new document is created to include more than
one mail merge document with related records.
Example file:
workbook/sheet for
mail merge;
Word Template;
Word Template
Scenario: To use this method in a document, create
bookmarks in the appropriate Word documents. Use field
names that correspond with Excel column headers. The code
simply searches for the bookmark names in the Excel
columns. Then the code uses the column header to return
the data for the related record. Finally, the code creates a
continuous document even though multiple merge
documents are being used.
The following code goes in a standard module of the Microsoft Excel worksheet
that houses the merge data. Access the Visual Basic Editor by pressing Alt +
F11, by using the Tools | Macro | Visual Basic Editor menu option, or by right-
clicking the worksheet tab and selecting View Code from the pop up menu.
Once the VBE is open, select Insert | Module from the menu bar. In the
window in the top right side, place the following code (either type it in or cut
and paste it).
Note: The example file already has the code in place.
Option Explicit¶
' * * * * *¶
Public Sub MailMerge()¶
'Word application objects declaration¶
Dim wrdApp As Object 'Word.Application¶
Dim objDoc As Object 'Word.Document¶
Dim objTemplate As Object 'Word.Document¶
Dim objBookMark As Object 'Word.Bookmark¶
'Excel application objects declaration¶
Dim sht As Excel.Worksheet¶
Dim rng As Excel.Range¶
Dim cll As Excel.Range¶
Dim fnd As Excel.Range¶
Dim strFileName As String¶
Search JabSto ::

Custom Search