Mail Merge with Word 2007

Microsoft Word 2007 brings some changes to how Mail Merge works. On top of this, Windows XP Service Pack 2 and Service Pack 3 change some settings that effect mail merges that depend on Dynamic Data Exchange (DDE). By default, Word 2007 also turns off the ability to connect to documents that are on a server. This document will walk you through the steps you need to make your old mail merge procedures work in Office 2007.

Choosing a Communication Protocol

Mail merges usually consist of two documents: an Excel spreadsheet or Access database, and a Word document. When you initiate a merge, Word needs a way to communicate with Excel or Access, so that it can request and receive the data from the file containing your merge data. There are two main protocols that can be used for this communication: DDE and Object Linking and Embedding (OLE).

DDE is an older technology that trasfers the data in a text format; most mail merges that we see on campus are set up to use DDE to transfer. OLE is a newer technology that uses a binary format to communicate. There are trade-offs with each technology, and unfortunately you need to understand them before you do your mail merge.

DDE

• DDE will bring your data over as it's formatted in Excel or Access. If you have data formatted as zip codes or currency, they'll flow seamlessly into your document.
• DDE only works with the first Excel worksheet in your workbook. If you have more than one worksheet you must use OLE.
• DDE is slower than OLE, though in small merges you won't notice much difference.

Enabling Network DDE

If you are using DDE to connect to a document on a server, you must first enable DDE to communicate over the network. This is a setting that is turned off in Windows XP when Service Pack 2 or Service Pack 3 is installed. 

• From the Windows Start menu select Settings-->Control Panel.
• Double-click "Administrative Tools," then double-click "Services."
• On the Extended tab, scroll down until you find "Network DDE," and then double-click it.
• Change "Startup Type" to "Automatic," and then click on "Start," to immediately start the service.
• "OK" the dialog and then close out of Administrative Tools and the Control Panel.

These settings should remain set, but installing Windows XP Service Packs may change them.

Enabling DDE use in Word

Whether you're using a local or a server data file, you must explicitly enable use of DDE in Word.

• In Word, go to Office Button-->Word Options, and select "Advanced" from the panel on the left.
• Scroll down the panel on the right until you get to the "General" section.
• Check the "Confirm file format conversion on open" box.
• OK your way out of the dialogs.

This setting will be saved until you change it.

Choosing DDE as your Communication Method

When you're ready to do your merge, you won't automatically be given the choice to use DDE; you must instruct Word to give you that choice.

• From the Word Mailings tab, choose "Select Recipients," and then select your data file to open.
• In the "Confirm Data Source" dialog box, check the "Select All" box.
• Scroll down until you find either "MS Excel Worksheets via DDE (*.xls)" or "MS Access Databases via DDE (*.mdb, *.mde)," and select the appropriate one. 
• "OK" out of the dialog boxes.

That's it! When you Finish & Merge, you should get your data formatted as it's formatted by Excel or Access.

OLE

• OLE does not preserve data formatting. If you are importing zip codes or currency you will need to do some work within Word to format the data correctly.
• OLE lets you choose which worksheet in a workbook you want to use.
• OLE is faster than DDE.

Using OLE for mail merge is a straightforward process, except for the step of formatting your data. The data that Excel receives is in its raw numeric format, unless it's marked as text in the source application. What this means is that zip codes lose their leading zeroes and nine-digit zip codes lose their hyphens, and monetary values come over as plain numbers. In order to display them in a formatted manner you must provide formatting codes in your Word document.

OLE Formatting Codes

If you're familiar with mail merges, then you're used to seeing place holders in your document for the fields that will be filled in from your data source, such as <<first_name>>, <<zip>> or perhaps <<donation>>. These are known as "merge fields," and they vary depending on your data source, so your actual merge field names might be completely different.

Tied to each of these fields is a format that's hidden from you by default; to see the formatting info in your Word merge document, use the key combination <Alt>-F9 (i.e. hold down the "Alt" key while you press the F9 key).  When you do that you will see a different representation of your document, with curly braces and "MERGEFIELD" surrounding or preceding each merge field. Press Alt-F9 again and you'll see the layout you're used to.

When you are in the Alt-F9 MERGEFIELD layout, you can add formatting codes to define the formatting of numeric data, much the way you can use Custom Format in Excel to format numeric data. 

• Custom formats are entered after the field name, within the curly brackets.
• Number formats are preceded by "\#" (without the quotes), to indicate that a numeric format is coming.
• A "0" (zero) indicates that if there isn't a digit in that place a zero should be placed there.
• A "$" means to put a currency symbol before the number.
• A "," means to use the thousands separator when displaying the number.
• You can also embed conditional IF statements (and other Word directives) in your formats, which is useful when you are formatting mixed zip-code data.

Some helpful format commands are given below. To enter them, first use Alt-F9 to uncover the MERGEFIELD formats. Entering the special curly braces is done by using Ctrl-F9 (holding down the Control key and pressing the F9 key). "Zip" and "Amount" should be replaced with your data source's actual field names. 

Download OLE Mail Merge PDF

Enabling Communication with a Document on a Sever

If your merge data is not on your local hard drive (for instance, if it's on newmisserver), you must tell Word that you "trust" the location where it resides. This is true for both DDE and OLE connections. You do this through the Trust Center:

• In Word 2007, select Office Button-->Word Options and click on "Trust Center" in the left panel.
• Click the "Trust Cetner Settings" button on the right panel.
• In the panel on the left, select "Trusted Locations"
• Check the box "Allow trusted locations on my network (not recommended)"
• Select "Add new location..." and browse to the folder containing your data. You may want to also enable trusting of subfolders.
• OK your way out of the Trust Center and Word Options.
• These settings are sticky: they will stay set until you change them.