Music, Art, Video, Politics and Fun

Archive for September, 2010

Mail Merge (Advanced)

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!

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. 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.

{ =(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 cells, just as you would in Excel.

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”

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.

I hope this helps someone, but I have to abandon this Blog. They have the single most annoying text editor on the planet!!