Mail Merge and Zip+4

[Heather Fabries]

I have a client who recently upgraded to Ofiice 2007. She is trying to do a
mail merge in Word 2007 using a Word 2003 template and Excel 2003 data
source. The mail merge works except for the zip codes. The 5 digit zip
codes show up in the mail merge but the zip+4 shows up as 0. In the Excel
spreadsheet, the zip codes are set up in one column and the zip odes are
either "special zip code" or "special zip+4". I have tried changing the
number setting, but the zip +4 still comes over as 0. It works perfectly
when I run it using Word 2003.

Thank you.
Heather Fabries

 

[Peter Jamieson]

When you use Word 2003, are you using DDE to connect to the data source?
(You can probably tell because if you are, then if the workbook is not
already open, Excel should start, then open the workbook).

If so, the simplest solution in Word 2007 is probably to use DDE. To do
that,
a. check Word Office button->Word Options->Advanced->General->Confirm file
format conversion upon open
b. go through the connection process again. In the "Select Data Source"
dialog box, select the source, and click Open.
c. You should then see a dialog box titled "Confirm Data Source". In
previous versions of Word, a DDE option would be visible. However, in Word
2007, you probably just see "OLE DB Database Files". If you see "MS Excel
Worksheets via DDE (*.xls)", select it (even if your workbook is a .xlsx,
not a .xls). If not...
- Check the "Show all" box. A whole bunch of options are displayed,
most of which are irrelevant to an Excel data source.
- scroll down and select "MS Excel Worksheets via DDE (*.xls)"
d. Click OK. Excel should start if it is not already running, and should
open the workbook if it is not already open.

Otherwise, If you need to use the default connection method (OLE DB in both
WOrd 2003 and Word 2007) then you probably need to replace the Merge field
that inserts the ZIP code with something like the following:

{ IF { QUOTE "{ MERGEFIELD zip }99999" \#0 } < 0
"{ MERGEFIELD zip }"
"{ IF { MERGEFIELD ZIP } > 99999
"{ MERGEFIELD ZIP \#"00000'-'0000" }"
"{ MERGEFIELD ZIP \# "00000" }" }" }

where "zip" is the name of the column in the Excel workbook that contains
the ZIP codes, and all the {} are the special "field code braces" that you
can insert into Word using ctrl-F9. For more info. on this, please see the
recent conversation in this group titled "Switches/field codes to fix zip
code in merge"