Introduction
The following tips address a wide range of issues people have with mailmerges.

There are also 8 useful macros towards the end of this thread, on Post #2, to:
• Delete Unwanted Rows from Mailmerge Tables;
• Prevent Line Wrapping in Label Merges;
• Activate Plain-Text Email Addresses & Hyperlinks in mailmerge output documents;
• Send Mailmerge Output to Individual Files;
• Run a Mailmerge from Excel, Sending the Output to Individual Files;
• Split Merged Output to Separate Documents; and
• Convert Text Representations of Fields to Working Fields; and
• Create Text Representations of Working Fields.
For PC macro installation & usage instructions, see: Installing Macros.
For Mac macro installation & usage instructions, see: Word:mac - Install a Macro

Note 1: The field brace pairs (i.e. '{ }') for the following field code examples are all created in the mailmerge main document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from here. Nor is it practicable (for the most part) to add them via the standard Word dialogues. Likewise, the chevrons (i.e. '« »') - are part of the actual mergefields, inserted via the 'Insert Merge Field' dropdown (i.e. you can't type them or copy & paste them from here). The spaces shown in the field constructions are all required. Instead of the →, ↵ and ¶ symbols shown in the examples, you should use real tabs and line/paragraph breaks, respectively.

Note 2: Mailmerge previews are not a reliable indicator of how compound fields will be output, especially where conditional output is concerned. For testing & validation purposes, send the output to a new document.

Note 3: Some of the field examples below use IF tests (e.g. {IF«MyData»= "MyText" "True" "False"}). If the «MyData» mergefield outputs mixed alpha-numeric content (e.g. 123 ABC), you may need to enclose it in double quotes (e.g. {IF "«MyData»" = "MyText" "True" "False"}).


Mailmerge Data Format Problems
By default, Word 2002 & later use OLE DB to get records from the data source. Because the OLE DB provider is designed to return data in a way that is compatible with databases, it requires a specific data type for each field, and every record in that field must be of that data type. When using other data sources, the OLE DB provider queries the first 8 records to determine the data type for each field. This can lead to unexpected results with data sources such as Excel workbooks, where rows (records) in a column (field) have mixed data types. When the OLE DB provider gets data from a column with mixed data types, records that don’t conform to the determined data type for the column may not be handled correctly. Furthermore, there are two text data types: "text" (up to 255 characters) and "memo" (more than 255 characters).

Some common mailmerge issues arising out of this include:
• Numbers but not text or dates being output; and
• Text data being truncated at 255 characters.

Ideally, one would ensure each field has only one data type. Workarounds include:
• Inserting a dummy first record containing data in the format that is not being output correctly; or
• Reordering the data so the first record has content in the format that is not otherwise being output correctly.
Thus, if numbers appear but text and/or dates don’t, ensure the first record in that field has text or a date. Similarly, if text over 255 characters is being truncated, ensure the first record in that field has more than 255 characters.

The use of field switches in the mailmerge main document is the preferred method of controlling the output format (amongst other things it allows the mailmerge to format the data differently than the source). While Dynamic Data Exchange (DDE) can also preserve the source data’s formatting, DDE is only useful if the mailmerge data start on the first row of the first sheet in the workbook. Although the Windows DDE dialog box allows range names for the data source, it only presents those that reference the first sheet and have either Workbook scope or the same scope as the sheet.

To use DDE when connecting to an Excel datasource:
• In Word 2003 & earlier, on the Word Tools menu, click on Tools|Options|General;
• In Word 2007, click on Office Button|Options|Advanced>General;
• In Word 2010 & later, click on File|Options|Advanced>General,
then check the ‘Confirm conversion at Open’ option. After you connect to your data file, the 'Confirm Data Source' dialog box opens. Click “Application via DDE (*.???)”, then click OK. In the Application dialog box, select the table/range containing the data you want to merge, then click OK. The data will now have the same formatting in your merged documents as they have in the source file.
Hint: Turn off the Confirm conversion at Open option after you finish your mail merge.

Another common source of confusion is the failure of a mailmerge to retain attributes like font formatting. That's because mailmerges work with data values and data types (i.e. text, date, number, etc.) only, regardless of what type of data source is being used. Formatting like font colors, shading, etc. are neither data values nor data types.


Mailmerge Output Problems
Mergefields inside shapes (e.g. textboxes) do not update correctly when you execute a mailmerge and the mergefields concerned remain active in the output document. Indeed, more or less immediately the merge has finished, the mergefields in the output document are liable to revert to their default state, leading to data loss.
Mergefields in page headers and footer do not update correctly when you execute a Catalog/Directory merge or a Label merge. Instead, the unpopulated mergefields concerned remain in the output document.

Having double quotes in any field in a mailmerge data source - even if that field isn't used in the mailmerge - is liable to result in an incomplete or corrupt output. You can have single quotes, however and, if you pair those single quotes, they will approximate the appearance of double quotes. That can often be achieved via a simple Find/Replace in the data source.

When doing a mailmerge, it is important to execute/finish the merge, rather than just saving previews of it, otherwise:
• calculated and conditional fields are liable to show incorrect results;
• Catalog/Directory merges that group data won’t do the grouping;
• mergefields in the document will remain active and the document will remain linked to the data source, with the result that the user will get the SQL prompt next time the document is opened. If the SQL query is:

• enabled, the document will attempt to connect to the data source. If:

• successful, the document will open with the preview record displayed, but the user can then access other records, with potentially adverse security & privacy implications; or
• unsuccessful, which is likely if you email the ‘preview’ document to someone on a different network, the user will be presented with one or more dialogues reporting errors and other options they will have to work through before being able to access the document. One of those options allows connection to a different data source, with probable adverse implications for the document’s data integrity and potentially adverse security & privacy implications. If the user instead goes though the options that 'Remove Data/Header Source' or 'Remove All Merge Info', the document will open with the preview record displayed, but any action that updates the document’s fields (e.g. a print preview or document printout) will cause the mergefields to revert to their default state, leading to data loss; or

• disabled, the document will open with the preview record displayed, but any action that updates the document’s fields (e.g. a print preview or document printout) will cause the mergefields to revert to their default state, leading to data loss;

• if you save the ‘preview’ document and unlink it from the data source, the mergefields are liable to revert to their default state, leading to data loss. Even if you save & close the document beforehand, the document will re-open with the preview record displayed, but any action that updates the document’s fields (e.g. a print preview or document printout) will cause the mergefields to revert to their default state, again leading to data loss


Prompt The User for a Mailmerge Starting Sequence Number
The following field prompts the user for a starting Record Number. It can be used for merging to Invoices, etc.
{QUOTE{ASK Offset "What is the starting Invoice #?" \o}{SET Val {MERGEREC}}{=Offset+Val}}


Using SKIPIF Fields To Filter Mailmerge Data
The following SKIPIF field prompts for the record range to process, based on a simple record count. With this coding, the user will be prompted to supply the first and last record #s to merge. Any records outside this range will be excluded.
{SKIPIF{=({MERGEREC}< {FILLIN "First Record #" \d 1 \o})+({MERGEREC}> {FILLIN "Last Record #" \d 999 \o})}= 1}

The following SKIPIF field prompts for the record range to process, based on dates in a ‘DbDate’ field in the source data. With this coding, the user will be prompted to supply the first and last dates to merge. Any records outside this date range will be excluded.
{SKIPIF{={IF{MERGEFIELD DbDate \@ YYYYMMDD}<{FILLIN "First Date to include." \@ YYYYMMDD \o} 1 0}*{IF{MERGEFIELD DbDate \@ YYYYMMDD}>{FILLIN "Last Date to include." \@ YYYYMMDD \o} 1 0}}= 1}

The following SKIPIF field first prompts for a contract item to include/exclude, then solicits input for the inclusion/exclusion rule.
{SKIPIF{IF{FILLIN "Contract to include/exclude." \o }=«CONTRACT» 0 1}= {FILLIN "1: include. 0: exclude." \o}}

In a generalized sense, the SKIPIF field can be used to apply AND and OR filters for multiple conditions, using coding like:
• AND: {SKIPIF{={IF«Field1»= Y 1 0}*{IF«Field2»= "" 1 0}}= 1}
• AND: {SKIPIF{={IF«Field1»= Y 1 0}*{IF«Field2»= "" 1 0}*{IF«Field3»= "" 1 0}}= 1}
• OR: {SKIPIF{={IF«Field1»= Y 1 0}+{IF«Field2»= "" 1 0}}> 0}
• OR: {SKIPIF{={IF«Field1»= Y 1 0}+{IF«Field2»= "" 1 0}+{IF«Field3»= "" 1 0}}> 0}


Edit Mailmerge Output During Merge Execution
For mailmerge data that require editing (e.g. because space is limited), you could employ a FILLIN field so that you can make the edits when the merge executes. Such a field might be coded as:
{QUOTE{FILLIN "Record: «FirstName» «LastName»" \d «DataField»}}

This way, you can both pull the data from the data source and edit it as necessary - or not at all - as each record is merged, without having to edit the data at the source.

Conversely, if all you're merging is some boilerplate text that needs editing, you might use a field coded as:
{QUOTE{FILLIN "Record: «FirstName» «LastName»" \d "Boilerplate Text"}}

The potential drawback with either approach, of course, is that the edits won't be reflected in the data source, but it shouldn't be too difficult to copy them back to it from the output document later on.


Test Whether a Text Mergefield Contains Numeric Data
The following field code allows you to test whether a given mergefield is text or is up to a 13-digit decimal value. This is useful if you want to control the output format.
{QUOTE{SET Val «Data»}{IF{REF Val}= {=Val} "{Val} is Numeric" "{Val} is Text"}}
and, so:
{QUOTE{SET Val «Data»}{IF{REF Val}= {=Val} {=Val \# 0.00} {REF Val}}}


Convert Mailmerge Date Fields to Plain Text
DATE fields ordinarily survive the mailmerge process and remain live in the output document, which can be problematic, since such fields update to the current date every time you open the document. Conversely, CREATEDATE fields from the mailmerge main document reflect that document's creation date (not that of the mailmerge output). To force DATE fields to convert to their results to static text reflecting the date on which the merge was performed, simply embed them in a QUOTE field coded as:
{QUOTE{DATE}}
plus any formatting switches you might want for the date format.


Managing Mailmerge Graphics
To insert variable images in a mailmerge, you need to embed the relevant mailmerge field in a INCLUDEPICTURE field. However, there are some issues with this that make the process less straightforward than one might expect. For example, when embedding a mailmerge field in an INCLUDEPICTURE field for the purpose of merging graphics:
1. the file paths to the fields need to have the separators expressed as '\\' or '/' instead of the usual '\';
2. the pictures usually won't show until you refresh the fields (eg Ctrl-A, then F9) in the output document; and
3. even after updating the fields, the images remain linked to their source files, which can be an issue if you later delete the image or send the merged output to someone else.
The following field constructions and accompanying macro address all three issues above:

• If the path to the images isn't included in the data source and you can't be sure they will always be in the same folder as the mailmerge main document, you could use a field coded as:
{INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d}

• If the path to the images is held in a separate field in the mailmerge data source, you could use:
{INCLUDEPICTURE {IF TRUE "«FilePath»\«Image»"} \d}

Note: You need a path separator between the filepath mergefield and the image mergefield. If that separator is included in the source data, it can be omitted from the field code above, but leaving it there also has no adverse effects.

• If the path to the images is held in the same field as the image name in the mailmerge data source, you could use:
{INCLUDEPICTURE {IF TRUE "«Image»"} \d}

• If the image path isn't in the data source but you can be sure they will always be in the same folder as the mailmerge main document, you can incorporate a FILENAME field thus:
{INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\«Image»"} \d}

By adding the following macro to your mailmerge main document, clicking on the 'Edit Individual Documents' button will intercept the merge, finishing it and unlinking the pictures (and any other residual fields except for hyperlinks).
For Word 2007 or later, the mailmerge main document will need to be saved in the .doc or .docm formats, since the .docx format cannot contain macros. The potential disadvantage of intercepting the 'Edit Individual Documents' process this way is the inability to choose which records to merge at that stage. However, the same outcome can be achieved - and with greater control - via the 'Edit Recipient List' tools.


Mailmerge Hyperlink ‘Click Here’ Insertion
By default, if you insert a mailmerge field into a hyperlink field, the hyperlinks will all show the first record’s address as the 'Text to display' text. To have the mailmerge display your preferred 'Text to display':
1. Disregarding mergefield issues for the moment, insert a hyperlink into the document in the normal way, choosing whatever 'Click Here' text you want in the 'Text to display' box.
2. Select the inserted hyperlink and press Shift-F9 to expose its field code.
3. Replace everything in the field after 'HYPERLINK' with your mergefield.
4. Select the field and press F9 to update the display.

In Word 2007 & later, you can make the display text variable also, via these additional steps:
5. Position the cursor anywhere within the display text.
6. Insert a mergefield pointing to whatever data field you want to use for the display text (this could even be the same field as used at step 3 above).
7. Delete all of the previous display text either side of your last-inserted mergefield (note that this field will likely have updated already).
8. Execute the merge.
9. After merging to a new document, use Ctrl-A, F9 to update all fields. Without this, the mergefield hover text won’t update to the correct targets.

Note: The above is only for merged output sent to a new document; it does not work with merges to email or print. For merges to email, see: Hyperlinks in data source fields are converted to plain text when you perform an email mail merge in HTML format in Word 2002 and Word 2003

Note: Hyperlink fields in an unsaved mailmerge main document modified this way are liable to cease functioning once the merge has been executed. Accordingly, it's best to save the mailmerge main document before doing the merge and not re-save it afterwards. If you need to make changes to the mailmerge main document, don't make/save them after doing a merge; make/save them beforehand.

To activate plain-text email address and hyperlink strings in a mailmerge output document, see 'Activate Plain-Text Email Addresses & Hyperlinks' on Post #2 below.

Conditionally Merge Spouse Data
The following construction can be used to manage data where some recipients may be living in a marital relationship with the same or different surnames:
«First_Name»{MERGEFIELD Middle_Name \b " "} {IF«Spouse_First_Name»= "" «Last_Name» {IF«Spouse_Last_Name»= «Last_Name» "and «Spouse_First_Name»{MERGEFIELD Spouse_Middle_Name \b " "} «Last_Name»" {IF«Spouse_Last_Name»= "" "and «Spouse_First_Name»{MERGEFIELD Spouse_Middle_Name \b " "} «Last_Name»" "«Last_Name» and «Spouse_First_Name»{MERGEFIELD Spouse_Middle_Name \b " "} «Spouse_Last_Name»"}}}


Merge Multiple Fields, Using Commas and ‘And’
The following construction can be used to manage data where one or more related fields (A, B, C, D, E, & F) may or may not be populated:
«A»{IF«C»= "" " and «B»" ", «B»{IF«D»= "" " and «C»" ", «C»{IF«E»= "" " and «D»" ", «D»{IF«F»= "" " and «E»" ", «E» and «F»"}"}"}"}

Note: It is assumed that all fields after the first empty one have no data.


Apply ‘a/an’ Indefinite Articles to Variable Text
The following field construction shows how a mailmerge field might be used to vary the preceding indefinite article for grammatic correctness.
a{IF{MERGEFIELD MyField \* Lower}= "a*" "n"}{IF{MERGEFIELD MyField \* Lower}= "e*" "n"}{IF{MERGEFIELD MyField \* Lower}= "i*" "n"}{IF{MERGEFIELD MyField}= "o*" "n"}{IF{MERGEFIELD MyField \* Lower}= "u*" "n"}{IF{MERGEFIELD MyField \* Lower}= "h*" "n"}
For numerals, change 'Lower' throughout the field code to 'Cardtext’.


Mailmerge CheckBox Insertion
To use a mergefield to toggle the state of a checkbox, insert an IF field coded as:
{IF«CheckBox»= "X" {SYMBOL 254 \f Wingdings \u } {SYMBOL 253 \f Wingdings \u }}
where 'CheckBox' is the name of the mergefield used to determine the checkbox status and 'X' is the field value that toggles it 'checked'.


Mailmerge String Formatting
To control mailmerge string formatting, add a 'Charformat' switch to the mergefield as follows:
1. select the mergefield;
2. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyData}, where 'MyData' is the field name;
3. delete everything between 'MyData' and the closing field brace;
4. add ' \* Charformat' after 'MyData', so that you end up with {MERGEFIELD MyData \* Charformat};
5. format at least the 'M' in 'MERGEFIELD' with the required font attributes;
6. position the cursor anywhere in the field and press F9 to update it.


Mailmerge Number & Currency Formatting
To control number & currency formatting in Word, add a numeric picture switch to the mergefield, thus:
1. select the mergefield;
2. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
3. edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
4. position the cursor anywhere in this field and press F9 to update it.

Note 1: The '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
• \# 0 for rounded whole numbers
• \# ,0 for rounded whole numbers with a thousands separator
• \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
• \# $#,##0.00 to pad out missing digits for values less than $1,000.00
• \# $,0 for rounded whole dollars with a thousands separator
• \# "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values

Note 2: The output precision is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.
If you use a final ';' in the formatting switch with nothing following, (eg \# "$,0.00;($,0.00);") zero and empty values will be suppressed.

Note 3: If you use a decimal tab or right-aligned tab to align the values, format the switch as \# "$→,0.00" (i.e. with a tab after the $ sign).

To have a mailmerge output amounts of less than 1 million in words, you could use a field coded as:
{QUOTE {SET Val «Amount»}"{=INT(Val) \* CardText}" dollar{=INT(Val)-1 \# "s;s;"}"
{IF{=MOD(Val,1) \# 0.00}> 0 " and {=MOD(Val,1)*100 \# 0 \* CardText} cent{=INT(MOD(Val,1)*100-1) \# "s;s;"}"}" \* FirstCap}
For larger values (up to 99,999,999,999,999.99), and to output 'and' in the strings (per standard English expressions), you could use a field coded as:
{QUOTE{SET Val «Amount»}
{IF{=Val}< 0 "Minus "}
{SET Val{=ABS(Val)}
{SET B{=INT(Val/1000000000)}}
{SET M{=INT(MOD(Val,1000000000)/1000000)}}
{SET T{=INT(MOD(Val,1000000)/1000)}}
{SET H{=INT(MOD(Val,1000))}}
{SET D{=MOD(Val,1)*100}}
{IF{=INT(B/100)}> 0 "{=INT(B/100) \*Cardtext} hundred"}
{IF{=MOD(B,100)}> 0 "{IF{=INT(B/100)}> 0 " and "}{=MOD(B,100) \*Cardtext}}{IF{B}> 0 " billion{=M+T+H \# "', ';;' '"}"}
{IF{=INT(M/100)}> 0 "{=INT(M/100) \*Cardtext} hundred"}
{IF{=MOD(M,100)}> 0 "{IF{=INT(M/100)}> 0 " and "}{=MOD(M,100) \*Cardtext}}{IF{M}> 0 " million{=T+H \# "', ';;' '"}"}
{IF{=INT(T/100)}> 0 "{=INT(T/100) \*Cardtext} hundred"}
{IF{=MOD(T,100)}> 0 "{IF{=INT(T/100)}> 0 " and "}{=MOD(T,100) \*Cardtext}}{IF{T}> 0 " thousand{=H \# "', ';;' '"}"}
{IF{=INT({H}/100)}> 0 "{=INT({H}/100) \*Cardtext} hundred "}
{IF{=MOD({H},100)}> 0 "and {=MOD({H},100) \*Cardtext} "}{=INT(Val-1) \# "'dollars';'zero dollars';'dollar'"}{IF{D}> 0 ", and {D \*Cardtext} cents"}\* FirstCap}.

The field code above turns the output from a field containing 1234567891011.12 into:
Twelve hundred and thirty-four billion, five hundred and sixty-seven million, eight hundred and ninety-one thousand, and eleven dollars, and twelve cents.
You can omit/edit the portions of the field code that output the:
• billions and millions;
• currency portions if you want just numbers or a different currency
• 'and ' text; and


• decimal values if you have only whole numbers,
if not needed.


Force '0' Substitution for Empty/Missing Records
There is a variety of ways to force the substitution of 0 for missing records. Perhaps the simplest way is to:
1. select your mergefield, which will look something like «Value».
2. press Ctrl-F9 to embed your mergefield in another field, so that you get {«Value»}
3. type 'QUOTE 0"' into the {«Value»} field before the «Value» and '"' after it, so that you end up with {QUOTE "0«Value»"}
4. position the cursor anywhere in this field and press F9 to update it.
This last approach works by prepending the mergefield's result with a 0, so the results of any calculations should be the same. Add any numeric formatting switches you require before the final '}', such as:
{QUOTE "0«Value»" \# "$0.00"}


Dealing with Null Values in Mailmerge Arithmetic
If your source data has some empty numeric records:
• a numeric picture switch won't display the null value as 0. You can overcome this by prefixing the mergefield with 0 and 'quoting' it, as in:
{QUOTE "0«Data1»" \# 0}
• a formula referencing those records is liable to fail with a Syntax Error. For example, a calculation field coded as {=«Data1»+«Data2» \# 0} will fail. You can overcome this by prefixing the potentially null mergefields with 0s and 'quoting' them, as in:
{QUOTE{={QUOTE "0«Data1»"}+{QUOTE "0«Data2»"}} \# 0}


Basic Mailmerge Maths
You can create the formula in Word to perform math on a mergefield. For example, suppose your data include the final price of an item for which you need to show how much is the base price and how much is the tax component, where the tax is 10% of the base price (ie 1/11th of the final price):
1. select your mergefield, which will look something like «Price»;
2. press Ctrl-F9 to wrap another field around it, thus { «Price» };
3. to calculate the tax component, edit the field so that you get {=«Price»/11 \# "$,0.00"};
4. to calculate the base price, edit the field so that you get {=«Price»*10/11 \# "$,0.00"};
5. position the cursor anywhere in this field and press F9 to update it.

To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial.


Mailmerge Percentage Formatting
To format a number as a percentage, add a formula and numeric picture switch to the mergefield, as follows:
1. select the mergefield, which will look something like «Percent»;
2. press Ctrl-F9 to wrap another field around it, thus { «Percent» };
3. edit the field so that you get {=«Percent»*100 \# 0.00%};
4. position the cursor anywhere in this field and press F9 to update it.


Convert Mailmerge Calculation Fields to Plain Text
To eliminate a calculation field's code from a mailmerge output document, wrap the calculation field in a QUOTE field, thus:
{QUOTE{=«Price»*10/11 \# "$,0.00"}}


Mailmerge Phone Number Formatting
To control Phone Number formatting, all you need to do is to add a picture switch to the mergefield, as follows:
1. select the mergefield;
2. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyData}, where 'MyData' is your data field's name;
3. delete everything between 'MyData' and the closing field brace;
4. add ' \# "(000) 0000 0000"' after 'MyData', so that you end up with {MERGEFIELD MyData \# "(000) 0000 0000"}. Other phone # formats can also be specified this way;
5. position the cursor anywhere in this field and press F9 to update it.


Mailmerge Date Formatting
When Word is connected to an OLE DB data source (the default), it treats dates as if they are in the US mm/dd/yyyy format, regardless of the format in the datasource, your regional settings etc. Applying a date format switch fixes that - and gives the mailmerge document the ability to format the date independently of whatever format is used in the data source.

To get the date format you want, regardless of whether you use OLE DB or DDE, you can add a formatting picture switch as follows:
1. select the mergefield;
2. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyDate} where 'MyDate' is your mergefield's name;
3. delete anything appearing after the mergefield's name and add '\@ "d MMMM yyyy"' to the field, as in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your dates will come out like '2 August 2008'.
4. position the cursor anywhere in this field and press F9 to update it.

Other possible date formatting switches include:
• \@ "dddd, d MMMM yyyy";
• \@ "ddd, d MMMM yyyy";
• \@ "d MMM yyyy";
• \@ "dd/MMM/yyyy";
• \@ "d-MM-yy".

Note: You can swap the d, M, y expressions around, but you must use uppercase 'M's for months - lowercase 'm's are for minutes.


Mailmerge Date Calculations
To see how to do just about everything you might want to do with dates in Word, check out my Microsoft Word Date Calculation Tutorial. For mailmerges, in particular, look at the item titled 'Date and Time Calculations in a Mailmerge'. Do read the document's introductory material, as it contains important information regarding configuring the fields for different regions and date formats.


Mailmerge Time Formatting
To get the time format you want, you can add a formatting picture switch as follows:
1. select the mergefield;
2. press Shift-F9 to expose the field coding. It should look something like {MERGEFIELD MyTime} where 'MyTime' is your mergefield's name;
3. delete anything appearing after the mergefield's name and add '\@ "h:m"', as in {MERGEFIELD MyTime \@ "h:m am/pm"};
4. if you want the hours and/or minutes to display leading 0s, change 'h' to 'hh for hours and 'm' to 'mm' for minutes;
5. position the cursor anywhere in this field and press F9 to update it.

Note: you must use lowercase 'm's for minutes - uppercase 'M's are for months.


Expressing Number Ranges as Ordinals
Suppose your datasource has a field that contains numbers and/or number ranges that are expressed in the form of 1, or 2-5, and so on. A field coded as:
{QUOTE{SET Data «MyField»}{SET EXP1{=-{=-{Data}-Data}/2}}{SET EXP2{=({Data}*(-1)-Data)/2}}"{EXP1 \* Ordinal}{EXP2 \# '-';;}{EXP2 \# 0;; \* Ordinal}"}
will output results like 1st and 2nd-5th, where «MyField» is your mergefield.


Mailmerge US Social Security Number Formatting
The following field suppresses all except the last four digits in a mergefield where the data are formatted as '123-45-6789':
{QUOTE{SET ID «SSN»}{SET Part3 {=({ID}*(-1)-ID)/2}}"XXX-XX-"{Part3 \# 0000}}
The full Security Number Formatting can be parsed with a field like:
{QUOTE{SET ID «SSN»}{SET Part1{=-{=-{ID}-ID}/2}}{SET Part2{=-({ID}*(-1)-{ID})/2}}{SET Part3{=({ID}*(-1)-ID)/2}}}
which can then be used to return the components, via {Part1 \# 000}, {Part2 \# 00} and {Part3 \# 0000}


Mailmerge US Zip Code Formatting
The following field code correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data are formatted as a 5-digit or 9-digit string without hyphens or have incorrect formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (e.g. because a datasource only accepts Zip Codes in the 5+4-digit format):
{QUOTE
{SET Zip «ZipCode»}
{IF{Zip}> 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

Note: the line breaks depicted here are not needed and have no effect on the output - they're just used to help with visualizing the code's structure.


Mailmerge String Testing
You can test the contents of a mergefield by doing the following:
1. select the field and Press Ctrl-F9 to enclose it in a new field, thus {«MyData»};
2. fill in around the new field braces so that you end up with: {IF«MyData»= "MyText" "String/field to output if True" "String/field to output if False"};
3. position the cursor anywhere in this field and press F9 to update it.


Test Whether a Mergefield Contains a Given String
When doing a mailmerge, the 'If Then Else' Rules offer the following options in the drop down menu:
• Field Name Equal To
• Field Name Not Equal To
• Field Name Less Than
• Field Name Greater Than
• Field Name Less Than or Equal
• Field Name Great Than or Equal
• Field Name is Blank
• Field Name is Not Blank
Word doesn't have a 'Field Name Contains' option. The simple reason is that Word has no such field. That said, there are limited ways for testing whether a field contains a given string. For example, a field coded as:
{IF«MyField»= "MyText*" "True" "False"}
tests whether the results of the mergefield starts with 'MyText'. Similarly, a field coded as:
{IF«MyField»= "*MyText" "True" "False"}
tests whether the results of the mergefield ends with 'MyText'.

You can also test whether the text you're interested in exists a pre-defined number of characters from the start or end. Thus, a field coded as:
{IF«MyField»= "???MyText*" "True" "False"}
tests whether the results of the mergefield contains any three characters followed by 'MyText'. Similarly, a field coded as:
{IF«MyField»= "*MyText???" "True" "False"}
tests whether the results of the mergefield ends with any three characters after 'MyText'.

If a mergefield contains a mix of alpha-numeric text, with only a single, non-zero, number, you can extract that number for testing via a field switch such as:
{MERGEFIELD MyField \# 0}
with the appropriate decimal provisions, if any.

Having extracted the number, you can then test its value or perform other mathematical functions on in it the normal way. To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial.


Mailmerge Empty Space Suppression
On PCs, you can use the mergefield \b and/or \f switches to suppress a space before or after an empty mergefield. For example, suppose you have:
«Title» «FirstName» «SecondName» «LastName»
but «SecondName» is sometimes empty and you don’t want that to leave two spaces in the output. To deal with that:
1. select the «SecondName» field and press Shift-F9 so that you get {MERGEFIELD SecondName};
2. edit the field code so that you end up with-
{MERGEFIELD SecondName \f " "}
or:
{MERGEFIELD SecondName \b " "}
depending on whether the space to be suppressed is following or before the mergefield;
3. delete, as appropriate, the corresponding space following or before the mergefield;
4. position the cursor anywhere in this field and press F9 to update it.

Note: the \b and \f switches don't work on Macs or in conjunction with other switches. In such cases you can use an IF test instead, coded along the lines of:
{IF«SecondName»<> "" " «SecondName»"}
or:
{IF«SecondName»<> "" "«SecondName» "}

Even so, you can use the \b and \f switches to express other mergefields that do have switches of their own. For example, suppose you have four fields ‘Product’, ‘Supplier’, ‘Quantity’ and ‘UnitPrice’, and you don’t want to output the ‘Product’, ‘Quantity’ or ‘UnitPrice’ fields if the ‘Supplier’ field is empty. In that case, you might use a field coded along the lines of:
{MERGEFIELD "Supplier" \b "{MERGEFIELD Product}→" \f "→{MERGEFIELD Quantity \# 0}→{MERGEFIELD UnitPrice \# "$0.00"}¶
"}


Mailmerge Empty Line Suppression
On PCs, you can use the mergefield \b and/or \f switches to suppress a paragraph break (or a line break) before and/or after an empty mergefield.
Suppose you have:
«Title» «FirstName» «LastName»
«CompanyName»
«Address1»
«Address2»
«City», «State» «Zip»
but «Address2» is sometimes empty. To deal with that:
1. select the «Address2» field and press Shift-F9 so that you get {MERGEFIELD Address2};
2. edit the field code so that you get
{MERGEFIELD Address2 \f "¶
"}
or:
{MERGEFIELD Address2 \b "¶
"},
depending on whether the line to be suppressed is following or before the mergefield;
3. delete the paragraph break or line break before/sfter the mergefield, as appropriate; then
4. position the cursor anywhere in the field and press F9 to update it.

Note: the \b and \f switches don't work on Macs, in conjunction with other switches, or if the field being tested contains spaces etc. when nominally 'empty'. In such cases you need to use and IF test instead, coded along the lines of:
{IF«Address2»<> "" "«Address2»¶
"}}¶
or:
{IF«Address2»<> "" "¶
«Address2»"}}¶

Similarly, you might have multiple phone #s, etc., but only want to display those that have data. For that you might use something like:
{MERGEFIELD HomePh \b "Home ph: " \f "¶
"}{MERGEFIELD BusPh \b "Work ph: " \f "¶
"}{MERGEFIELD MobPh \b "Mobile ph: " \f "¶
"}{MERGEFIELD Email \b "Email: " \f "¶
"}¶

Alternatively, for conditionally outputting formatted phone numbers, you might use:
{MERGEFIELD Home_Phone \# "'h. '(##) #### ####'¶
';;"}{MERGEFIELD Work_Phone \# "'w. '(##) #### ####'¶
';;"}{MERGEFIELD Mobile_Phone \# "'m. '#### ### ###'¶
';;"}¶

You can use the same approach to conditionally output a bulleted list:
{MERGEFIELD Field_1 \b "•→Item 1→" \f "¶
"}{MERGEFIELD Field_2 \b "•→Item 2→" \f "¶
"}{MERGEFIELD Field_3 \b "•→Item 3→" \f "¶
"}{MERGEFIELD Field_4 \b "•→Item 4→" \f "¶
"}{MERGEFIELD Field_5 \b "•→Item 5→" \f "¶
"}¶

The problem with using the \b and \f switches is that you can't use them on Macs or with other switches, such as for numeric formatting. For that, you need to use something like:
{IF{MERGEFIELD Field_1}<> "" "•→Item 1→ {MERGEFIELD Field_1 \# "$,0.00}¶"
"}{IF{MERGEFIELD Field_2}<> "" "•→Item 2→ {MERGEFIELD Field_2 \# "$,0.00}¶"
"}{IF{MERGEFIELD Field_3}<> "" "•→Item 3→ {MERGEFIELD Field_3 \# "$,0.00}¶"
"}{IF{MERGEFIELD Field_4}<> "" "•→Item 4→ {MERGEFIELD Field_4 \# "$,0.00}¶"
"}{IF{MERGEFIELD Field_5}<> "" "•→Item 5→ {MERGEFIELD Field_5 \# "$,0.00}¶"
"}¶
For each of the above constructions, removing the trailing paragraph break after applying whatever paragraph formatting you want for the field code will remove the space between the list and the next paragraph when the merge is executed, without affecting the format of that next paragraph.


Output Text with Conditionally-Numbered Paragraphs
The following construction can be used to generate a sequentially-numbered list of items (with no auto-numbering for the paragraphs) for a record where the fields for the conditional content contain '1' to indicate a TRUE condition:
The applicable items for this record are :{SET Counter 0}{IF«Item01»= 1 "¶
{SET Counter {= Counter+1}}{=Counter} Text for Item 1"}{IF«Item02»= 1 "¶
{SET Counter {= Counter+1}}{=Counter} Text for Item 2"}{IF«Item03»= 1 "¶
{SET Counter {= Counter+1}}{=Counter} Text for Item 3"}{IF«Item04»= 1 "¶
{SET Counter {= Counter+1}}{=Counter} Text for Item 4"}{IF«Item05»= 1 "¶
{SET Counter {= Counter+1}}{=Counter} Text for Item 5"}, etc.

With such a construction, each of the paragraphs for which data exist will be numbered, 1, 2, 3 and so on, without numbers being skipped when a preceding item had no data.

As will quickly become apparent, the field codes are mostly just a series of:
{IF«Item#»= "true" "¶
{SET Counter {=Counter+1}}{=Counter}. Text for Item #"}
where # is the item number. The {SET Counter 0} field simply resets the paragraph counter for each record.


Conditionally Format Mailmerge Output
To conditionally format mailmerge outputs, you could use a series of fields coded as:
{IF«MyField»= "1" «MyField» \* Charformat}
{IF«MyField»= "2" «MyField» \* Charformat}
{IF«MyField»= "3" «MyField» \* Charformat}

where the 'I' in each 'IF' is formatted the way you want the output to appear.

Similarly, to conditionally format text that varies according to mergefield outputs, you could use a series of fields coded as:
{IF«MyField»= "1" "Text for 1" \* Charformat}
{IF«MyField»= "2" "Text for 2" \* Charformat}
{IF«MyField»= "3" "Text for 3" \* Charformat}
or:
{IF«MyField»= "1" "Plain Text and coloured text for 1"}
{IF«MyField»= "2" "Plain Text and coloured text for 2"}
{IF«MyField»= "3" "Plain Text and coloured text for 3"}

The advantage of the second approach is the ability to mix the formats of the output.

Note 1: Although the above examples use coloured text, you can use whatever text formatting you desire.

Note 2: Although the above examples are laid out on separate lines, that is for presentation purposes only; your mailmerge main document should have the fields all on the same logical line.

Note 3: Where a mergefield outputs only numeric data, it may be possible to use numeric picture switches to apply the colouring. This is described in my Microsoft Word Field Maths Tutorial and in Conditionally Color/Shade Numeric Output Ranges below.

Note 4: You can't use \* Charformat switches with numeric picture switches or date switches. The \* Charformat switch also doesn't work on systems with Office set up for use with a RTL language - even if that language is not enabled. A workaround would be to apply the desired formatting to the entire field (preferably via a Character Style).


Conditionally Shade Table Cells
Word doesn't have a conditional shading function like Excel has, but you can achieve a similar result via field coding. To do this, you need a set of fields (one for each condition) in the relevant cell(s), along the lines of:

{IF«Condition»= "1" "→→↵
→Condition 1 Text→↵
→→" \* Charformat}
{IF«Condition»= "2" "→→↵
→Condition 2 Text→↵
→→" \* Charformat}
{IF«Condition»= "3" "→→↵
→Condition 3 Text→↵
→→" \* Charformat}
where the 'I' in each 'IF' is highlighted with the relevant highlight color and each cell to be shaded has:
• margins set to 0 all round
• paragraph formatting with a single right-aligned tab-stop set to at least the cell width.

Note: All the fields depicted above should be inserted one after the other, without line/paragraph breaks between them - the above separations are just to make the field coding easier to follow.

In the above depiction, three conditions are provided for, but you can have more or less - highlighting allows 16 possibilities and font colouring can be used to expand the colour differentiations even further.

Similarly, if you have numeric data that can be +ve, -ve, or 0, and apply shading as follows, you gain access to all of Word's shading options:

{QUOTE{=«Value1»-«Value2» \# "' →↵
→';' →↵
→';' →↵
→'"}}


Conditionally Color/Shade Numeric Output Ranges
You can use field coded to apply up to three color/highlight formats to numeric data falling into various ranges. In the following examples, values up to 200 get colored green, values over 240 get colored red and values between these get colored orange:
{QUOTE{=INT(«Val»/40)-5 \# "{MERGEFIELD Val \* Charformat } - High';'{MERGEFIELD Val \* Charformat } - Low';'{MERGEFIELD Val \* Charformat } - Medium'}}
The above construction allows for up to 64 characters in the field switch. For even more characters, you can use coding along the lines of:
{QUOTE{=INT(«Val»/40)-5 \# "{QUOTE"{MERGEFIELD Val \* Charformat } - High"};{QUOTE"{MERGEFIELD Val \* Charformat } - Low"};{QUOTE"{MERGEFIELD Val \* Charformat } - Medium"}"}}


Suppress Output on Unused Labels in a Label Merge
Ordinarily, any default text intended for printing on labels will appear on all labels on the last page of a label merge, even after the last record has been output. To prevent that, you can use an IF test that looks at the MERGEREC field’s value. For example:
{IF{MERGEREC}<> "" "Text to Display"}
This can be use with the conditional display of other mergefields in conjunction with the default text. For example:
{IF{MERGEREC}<> "" "Default Text: {IF«MyField»= "" "No Record" «MyField»}"}


Display Fractions in a Mailmerge
If your source data always have the same denominator for a fraction ('8' in the example below), you could try:
{QUOTE{=INT(«Data»)}{=MOD(«Data»,1)*8}"/8"}
with the {=MOD(«Data»,1)*8} part of the field superscripted and the '8' in '/8' subscripted.


Label Mail Merges - Down-Then-Across Output
To achieve this:
1. In the Labels dialog, click New Document to create a new document based on the label definition you have chosen.
2. Delete all cells except the first. If there is a spacer column, note its width beforehand.
3. On the Page Layout tab, in the Page Setup group, click Columns and choose More Columns...
4. In the Columns dialog, select however many labels across your label stationery originally had
5. In the Spacing box, set the width to the original spacer column width or, if it had none, 0
6. Set the column width to your label width.
7. Change the mailmerge type to 'Directory'.
8. Run the mailmerge.


Label Mail Merges - Start Each Record on a New Page
Suppose you're doing a label mailmerge and, instead of having a different record for each label, you want the whole page filled with the same label.

In that case, you can complete the label setup in the normal manner then, once you've clicked 'Update Labels, you can simply delete the «NextRecord» fields. The simplest way to do that is:
1. Press Alt-F9 to expose the field codes. The appearance of the «NextRecord» fields will change to { NEXT }.
2. Do a Find/Replace, where -
Find = ^d NEXT
Replace = nothing
3. Press Alt-F9 to hide the field codes
You can then execute the merge.

Note: The same basic approach can be used to produce multiple copies of each record on a page before switching to the next record. This only works well, though, if the number of labels per page is evenly divisible by the number of records per label.

To do that, simply copy the first «NextRecord» field before doing the Find/Replace described above, then paste the copied «NextRecord» field back into the label table at whatever interval you require.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Delete Unwanted Rows from a Mailmerge Table
In a mailmerge with a table with a row that may or may not be required, it’s fairly simple to use field coding like:
{IF«condition»= TRUE "
Table for true condition goes here
" "
Table for false condition goes here
"}
This approach is easy-enough to implement where there are perhaps no more than 3 conditional rows. For larger tables, though, where the number of permutations becomes problematic, a macro that intercepts the 'Finish & Merge>Edit Individual Documents' command to execute the merge then goes through the output document and delete any table rows where a given cell is empty is a simpler approach. The following macro does just that, deleting rows whose cell in column 2 is empty. You could test a different column by changing the 2 in .Cell(r, 2). The two commented-out code lines would be used if the intention is to print the output rather than save it. More sophisticated code could be used to test different cells on various rows or in different tables.
Simply comment-out or delete the unwanted row deletion code lines and edit the remaining code line to suit your scenario.

If merging direct to print, change the macro name to 'MailMergeToPrinter' and insert:
before the final:
Prevent Line Wrapping in Label Merges
In a label merge, it is sometimes desirable to prevent line wrapping - such as when creating address labels. The following macro ensures the output on each label in a mailmerge contains no line wrapping. Text that is too long to fit on a single line is fitted to the cell’s width.
Note: For this approach to work, the required separate lines in the labels must be separated by paragraph breaks.

If merging direct to print, change the macro name to 'MailMergeToPrinter' and insert:
before the final:
Activate Plain-Text Email Addresses & Hyperlinks
If you insert a HYPERLINK field into another field (e.g. as part of an IF test) the hyperlink will be inactive in the output document. For example:
{IF«balance_due»= "0.00" "Thanks for paying your invoice" "Please pay your invoice online {HYPERLINK "https://MySite.com/pay inv=«inv_number»&amountt=«inv_amount»"}"}
won't give you a working hyperlink. To overcome that limitation, you could code the mergefield along the lines of:
{IF«balance_due»= "0.00" "Thanks for paying your invoice" "Please pay your invoice online, at: https://MySite.com/pay?inv=«inv_number»&amountt=«inv_amount»"}
and add the following macro to your mailmerge main document. Clicking on the 'Edit Individual Documents' button will intercept the merge and convert any email and hyperlink strings in the output document to working hyperlinks.
and add the following macro to your mailmerge main document. Clicking on the 'Edit Individual Documents' button will intercept the merge and convert any email and hyperlink strings in the output document to working hyperlinks.

Send Mailmerge Output to Individual Files
By adding the following macro to your mailmerge main document, you can generate one output file per record. Files are saved to the same folder as the mailmerge main document, using the 'Last_Name' & 'First_Name' fields in the data source for the filenames (change these to suit your needs). PDF & DOCX formats are catered for.
Note 1: If you rename the above macro as 'MailMergeToDoc', clicking on the 'Edit Individual Documents' button will intercept the merge and the process will run automatically. The potential disadvantage of intercepting the 'Edit Individual Documents' process this way is that you no longer get to choose which records to merge at that stage. However, you can still achieve the same outcome - and with greater control - via the 'Edit Recipient List' tools.

Note 2: If you're using Word 2007 or later, your mailmerge main document will need to be saved in the .doc or .docm formats, as documents using the .docx format cannot contain macros.

Note 3: The above code defaults to saving the output to the mailmerge main document's folder. You can change the destination folder by editing:
If destination folders are specified in the data source, you could delete or comment-out that line and un-comment the line:
where the folder the output is to be saved to is in a data field named 'Folder'.

If there is a risk that the output folder does not exist, it can be created on the fly by adding:
Alternatively, to save the output to the same folder as the data source, you could replace:
with:
Illegal filename characters are replaced with underscores.

Note 4: The above code also provides for the filename to be output to the page footer. This, of course, assumes the footer is suitably formatted. Simply uncomment the line concerned.


Run a Mailmerge from Excel, Sending the Output to Individual Files
The following macro automates a mailmerge from Excel. The code assumes you have a document named 'MailMergeMainDocument.docx' stored in the same folder as the Excel workbook. That document should not contain macros or protection and should be saved as an ordinary document or as a mailmerge main document.

As coded, the macro also assumes a standard query, processing all records from Sheet1. Change the sheet references, as appropriate. If you're using filtering, you'd have to add that to the macro's SQLStatement, too.

Each record's output is sent to a new file in the same folder as the Excel workbook, using the 'Last_Name' & 'First_Name' fields in the data source for the filenames (change these to suit your requirements). Illegal filename characters are replaced with underscores.
Note 1: For testing purposes, you might want to change the line 'wdApp.Visible = False' to 'wdApp.Visible = True'.

Note 2: The above code also provides for the filename to be output to the page footer. This, of course, assumes the footer is suitably formatted. Simply uncomment the line concerned.

Note 3: The above code defaults to saving the output to the workbook's folder. You can change that by editing:
For example, to save the output to the user's 'Documents' folder, you could use:
If you already have a mailmerge main document set up with (or without) filtering, you could use the following Word macro to retrieve the SQL statement:

Split Merged Output to Separate Documents
Execute the merge, sending the output to a new document, then run the following macro over that document.
Illegal filename characters are replaced with underscores.

As coded, it is assumed the output filename consists of the first paragraph in each record. If not, you could use a different range or replace all of the content between the ***** strings with code like

Convert Text Representations of Fields to Working Fields
The following macro converts text representations of Word field codes to working field codes. To do the conversion, simply paste the "textual" field codes into your document, select them and run the macro. Tabs, line breaks and paragraph breaks represented by the →, ↵ and ¶ symbols are converted to real tabs, line breaks and paragraph breaks, respectively.
Note: Character formatting in the source string is not preserved.


Create Text Representations of Working Fields
The following macro creates text representations of Word field codes, regardless of how complex the fields might be, copying the text representation to the Windows Clipboard so you can paste it wherever you want. Simply select the field(s), then run the macro. Tabs, line breaks and paragraph breaks in the field code are converted to →, ↵ and ¶ symbols, respectively. The formatting of line breaks and paragraph breaks is approximated through the use of line breaks following the ↵ and ¶ symbols.
Note: As the field code is converted to a simple text string, character formatting is not preserved.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]