Performing Mail Merges Using XML Data in Microsoft Office Access 2003

  • Article

 

Frank Rice
Microsoft Corporation

January 2004

Applies to:
    Microsoft® Office Access 2003
    Microsoft Office Word 2003

Summary: Create a mail merge programmatically from Access using an external XML data file. In this article, walk through how to call Word programmatically from Access to create documents merging boilerplate text and customized data stored in Access. (7 printed pages)

Download odc_AcXMLMailAccessSample.exe.

Contents

Introduction
Performing a Mail Merge Programmatically
Conclusion

Introduction

Mail merge is a feature in Microsoft® Word where you can merge boilerplate information (such as a form letter) with variable information (such as a database of names and addresses of customers). This way, you can have documents that are almost exactly the same: the substance of the documents is the same for every customer, yet each document is customized to a particular individual in your database. In the application discussed in this article, a Microsoft Office Access 2003 table created from an XML file, is used as a data store for the address information that is merged with a Word document.

There are three main components to a mail merge:

  • The main document that contains the boilerplate information such as text and graphics. The main document also determines the format of the resulting merged document; for example, form letter, envelopes, or labels.
  • The data source that contains the variable information: a list of names and addresses, for example. This information is typically, but not always, contained in a database.
  • The resulting merged document. This document contains the combination of boilerplate text and graphics, and variable information.

The main document contains the boilerplate text and graphics that you want in every version of the merged document. In the main document, you insert special instructions, called merge fields, to indicate where you want the variable information to appear. When you merge the information in the data source with the main document, the variable information replaces the merge fields with the individualized information. The result is the merged document that contains the combination of boilerplate text and variable information.

The data source contains the information (such as names, addresses, and phone numbers) that varies with each version of the document. Many times, you can store your data in a Word document; however, Word also recognizes other formats. In addition to the Access databases mentioned earlier, the data source can be a Microsoft Excel worksheet or a comma-separated variable (CSV) file among other sources.

The data source is set up with a header row (also called a header record) and data records. The header row is the first item in the data source. It contains the field names that identify the variable information. The data records contain the individualized information. Each data record contains one or more fields, depending on how much variable information you have for each individual. This set of information makes up one data record.

The mail merge process has four main steps:

  1. Open or create the main document.
  2. Open or create the data source.
  3. Edit the main document.
  4. Perform the merge.

From Access, you can setup a mail merge manually with the Microsoft Word Mail Merge Wizard, or programmatically. This article demonstrates doing a mail merge programmatically using an Access table as a data source. The table is created from an external XML file.

Performing a Mail-Merge Programmatically

You can use the OpenDataSource method of the MailMerge object to link from an Access database to a Word application. When you reference an Access data source using the OpenDataSource method, you first reference a Word document file and the Word MailMerge object. Among the arguments for this method, you usually specify two: the Name argument, which indicates the drive, path, and filename for the Access data source; the Connection argument, which specifies either a table or query data source type, and name of the database object. Your Word document must have bookmarks or merge fields that link the data source fields in the database to the mail document. You then invoke the Execute method of the MailMerge object to perform the merge that pulls data from the data source to the merge fields (or bookmarks) in the Word document.

The following procedure demonstrates how to create a form letter, attach it to a data source, and merge the document with the merged document programmatically. The procedure for creating merged envelopes or labels is similar.

  1. If you have a document that contains the boilerplate text that you want to use, open it. Or, if you want to create a new form letter and no blank document is currently displayed, click New Blank Document on the Standard toolbar.

  2. If you are creating the document from a blank document, create the main document. Type the following fields into the document (as seen in Figure 1):

    FirstName

    LastName

    Address

    City

    Region

    PostalCode

  3. Next, add merge fields to the document. Highlight the FirstName field, and then click the Insert menu, click Field, and then scroll down to and click MergeRec in the Field names list. The FirstName field becomes a merge field as denoted by the double-arrows on each side. Repeat this process for the other fields.

    Figure 1. Main document with merge fields

  4. As stated earlier, the table used as the data source is created from an external XML file. The XML file consists of the following (it is also available in the download for this article):

    <?xml version="1.0" encoding="UTF-8"?>
<rootnode xmlns:od="urn:schemas-microsoft-com:officedata">
<Employee>
<LastName>Davolio</LastName>
<FirstName>Nancy</FirstName>
<Address>507 - 20th Ave. E. Apt. 2A</Address>
<City>Seattle</City>
<Region>WA</Region>
<PostalCode>98122</PostalCode>
</Employee>
<Employee>
<LastName>Fuller</LastName>
<FirstName>Andrew</FirstName>
<Address>908 W. Capital Way</Address>
<City>Tacoma</City>
<Region>WA</Region>
<PostalCode>98401</PostalCode>
</Employee>
<Employee>
<LastName>Leverling</LastName>
<FirstName>Janet</FirstName>
<Address>722 Moss Bay Blvd.</Address>
<City>Kirkland</City>
<Region>WA</Region>
<PostalCode>98033</PostalCode>
</Employee>
<Employee>
<LastName>Peacock</LastName>
<FirstName>Margaret</FirstName>
<Address>4110 Old Redmond Rd.</Address>
<City>Redmond</City>
<Region>WA</Region>
<PostalCode>98052</PostalCode>
</Employee>
</rootnode>

  5. Save the file as Employees.xml in the C:\ directory.

  6. Next, open Access 2003. To create a blank database, click New on the File and then click Blank database in the New File task pane.

  7. In the File New Database dialog box, type a file name, navigate to the C:\ directory, and then click Create.

  8. Next, create a new form and add a button. In the Database window, click Forms under Objects.

  9. Click the New button on the Database window toolbar.

  10. In the New Form dialog box, click Design View and then click OK.

  11. Add a button to the form from the toolbar. Click Cancel in the Command Button Wizard.

  12. On the View menu, click Properties to display the Property window.

  13. On the All tab, type MailMerge in the Name box, and Mail Merge in the Caption box.

  14. On the Event tab, click On Click, and the click the ellipses(. . .).

  15. In the Choose Builder dialog box, click Code Builder, and then click OK.

  16. Add a reference to the Microsoft Word 11.0 object Library by clicking References on the Tools menu.

  17. In the References box, click to select the Microsoft Word 11.0 object Library, and then click OK.

  18. In the MailMerge_Click procedure, insert the following code:

    'Import the XML document into Access.
Application.ImportXML "c:\Employees.xml"

' Call the mail merge procedure.
MergeIt

  19. Click outside the procedure you just created and insert the following:

    Function MergeIt()
  Dim objWord As Word.Document

  Set objWord = GetObject("C:\MyMerge.doc", "Word.Document")
  ' Make Word visible.
  objWord.Application.Visible = True

  ' Set the mail merge data source as the current database.
  objWord.MailMerge.OpenDataSource _
  Name:=CurrentDb.Name, _
  LinkToSource:=True, _
  Connection:="TABLE Employee", _
  SQLStatement:="Select * from [Employee]"

  ' Execute the mail merge.
  objWord.MailMerge.Execute

End Function

  20. Close the Visual Basic Editor, and then close the Property window.

  21. On the View menu, click Form View.

  22. Click the button you just created.

When you run the code, the first thing that happens is that the Employee table is created from the Employees.xml file. This table acts as the data source for the mail merge. Next, the MergeIt subroutine is called.

Then, the MergeIt subroutine sets a reference to the main document and makes the main document visible. Next, the current database is designated as the active data source using the OpenDataSource method of the MailMerge object. Then, the SQL statement that retrieves the data is defined. And finally, the Execute method of the MailMerge object is executed to perform the mail merge.

The result is a form letter for each address in the data source that is based on an XML file (see Figure 2).

Figure 2. One of the form letters created during the mail merge

Conclusion

This article demonstrates how to create programmatically a Word mail merge from Access by using data from an XML file. You did this by using the OpenDataSource method of the MailMerge object. Using an external XML file adds versatility to your mail merge applications.