MS Word Advanced Mail Merge

Ok, creating a Mail Merge in Word is a pain in the butt, no doubt. For one thing, you can’t pull from multiple sources (like I tried to do with MS Access with multiple tables). If you are in the MS Access situation, create a Report with the fields you want and then use Office Links to open/save the data as an Excel file. <–HINT!

Then follow the Word Help instructions for the basic Mail Merge. All will be right with the world.

I found this site that has a lot of good content, and after you get all of that,and things are still not working the way you want them to, come back here and keep reading.

Below are text samples of complete merge fields in a Word document. You can not copy and paste this into Word, it will not work. Every {} you see must be inserted in a Mail Merge, or using CTRL-F9. See link above.

Getting fields of data to simply Add was one of the most difficult and least documented things I needed to do..

{ =({ IF {MERGEFIELD “UD1 } “” “{ MERGEFIELD “SM_CD” }+” “0+”}{ IF { MERGEFIELD UD2} “” “{ MERGEFIELD “QM_CD”}+” “0+ }{ IF { MERGEFIELD “UD3” } “” “{MERGEFIELD “SAS_CD” }” “0” }) \# “0” } results in “=(0+4+3+2)\# “0””.

Since UD1 and SM_CD are NULL, a “0+” is inserted (you will get errors if you don't do this, because NULL + 3 is not valid). If they were not NULL, the value of SM_CD would be inserted. “4+” is then inserted because QM_CD equals 4 and UD2 is not NULL. And so on with the last two numbers. The KEY here is the () locations and the \# “0″, because they result in a formula that Word understands and executes!

This is a real good example of the format you want to add data fields in your Merge. TIP: You can insert all of the required merge fields at the top of a document, then copy/paste them inside { } that you insert with CTRL-F9. Then add the IF and \# stuff.

{ =(B22*F22) \# “$#,##0.00;($#,##0.00)”} result in something like “$125.00”. This is an example of math within a Word table, by actually referencing the existing cells, just as you would in Excel. A1 is the top left cell in your table, B1 would be the field to the right of it. Open a blank Excel sheet if you are confused.

Some date formatting after you insert a field;

{ MERGEFIELD SHIP_DATE \@ “MM/d/yy”} results in “9/6/10”

{ MERGEFIELD SHIP_DATE \@ “d MMMM yyyy” } results in “6 September 2010”

Refer to the link above for more (a Table is located at the bottom of that page).

This one, was an interesting problem. I had to create a list of sentences based on data being present or not.

{ IF{MERGEFIELD UD1NO } “” “ Site Maintained Update”{ MERGEFIELD UD1NO }, { MERGEFIELD TYPE }, { IF { MERGEFIELD QUARTER } “” “Quarter “ } {MERGEFIELD QUARTER } PP
“ }{ IF{ MERGEFIELD JC_CD } “” { IF { MERGEFIELD UD1 “” “ Job Control Media, Quarter {MERGEFIELD QUARTER } PP
“ }}{IF { MERGEFIELD UD2NO } “” “ Quarterly Update #{ MERGEFIELD UD2NO } PP
“ }

Result:
“ Site Maintained Update 14, Quarter 4” only if UD1NO is not NULL
“ Job Control Media, Quarter 4” only if JC_CD and UD1 are not NULL, using a nested IF
“ Quarterly Update #15” only if UD2NO is not NULL

Note the PP is where I inserted a carriage return (Paragraph mark by hitting Enter. The PP did not appear in the actual mail merge document). This forces each sentence to appear on its own line. Creative formatting...

Drop a comment if this isn't working for you and I will try to help out if I can. Include an email address, duh..