Results 1 to 1 of 1
  1. #1
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,945
    Thanks
    0
    Thanked 203 Times in 184 Posts

    Word Mailmerge Tips & Tricks

    Introduction
    The following tips are written with a view to addressing a wide range of common problems people have with mailmerges. Please read them and see if your issues are addressed before asking for help. You might find a quicker solution this way than waiting for someone to respond to a post.

    There are also three useful macros towards the end of this thread.
    For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm.
    For Mac macro installation & usage instructions, see: http://word.mvps.org/Mac/InstallMacro.html

    Note 1: The field brace pairs (i.e. '{ }') for the following examples are 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 this thread or another website. Nor is it practicable (for the most part) to add them via any of the standard Word dialogues. Likewise, you can't type or copy & paste the chevrons (i.e. '« »') - they're part of the actual mergefields, which you can insert from the mailmerge toolbar. The spaces represented in the field constructions are all required. Instead of the ↵ and ¶ shown in the examples, you should use real line/paragraph breaks.

    Note 2: For a macro to convert any of the text-fields below into working fields in your document, scroll to the bottom of this page. You will, of course, need to edit the mailmerge field names in them to match your own.

    Note 3: Mailmerge previews are not a reliable indicator of how many of the following fields will work when the merge is executed. For testing & validation purposes, always send the output to a new document.

    Mailmerge Data Format Problems
    By default, Word (2002 & later) uses the OLE DB provider to get merge data from an Excel workbook. The older ODBC driver works in a similar way. The basic problem with using the OLE DB provider is that it's designed to return data in a way that is compatible with Access and other relational database packages. Access requires a specific data type for each field, and every value in that field must have only that data type. In Excel, however, cells in a column (field) can have different data types. When the OLE DB provider gets data from an Excel column with mixed data types, it has to determine the data type for each column. The first 8 records are used for that (the 8 can be changed in the Windows Registry, but it’s not advisable to do so). Records that don’t conform to the determined data type are liable to not be handled correctly. To complicate matters, for historical reasons, there are two text data types: "text" (up to 255 characters) and "memo" (can be longer than that).

    Some common mailmerge issues arising out of this include:
    • Numbers but not text or dates being output for some records; 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 for that field has text or a date. Similarly, if text over 255 characters is being truncated, ensure the first record for that field contains more than 255 characters.

    Although adding field switches to 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), you can also preserve the source data’s formatting by using Dynamic Data Exchange (DDE). This is only useful, though, if the mailmerge data are in the first sheet in the workbook and the data start on the first row of that sheet. To choose the DDE data transfer method when you’re connecting to an Excel worksheet during a mail merge, all you have to do is select one check box in Word:
    1. On the Word Tools menu, click Options, and then click the General tab.
    2. Click Confirm conversion at Open.

    At the step in the mail-merge process where you connect to your data file, after you locate the file you want to connect to, the Confirm Data Source dialog box opens. Click “Application via DDE (*.???)”, and then click OK. In the Application dialog box, select the table/range that contains the information you want to merge, and then click OK. The data will now be formatted the same in your merged documents as they look in the source file.

    Although the Windows DDE dialog box allows and presents range names for the data source, it only presents names that reference the first sheet and have either Workbook scope or the same scope as the sheet.

    Note: It’s probably a good idea to turn off the Confirm conversion at Open option after you finish your mail merge. Otherwise, you might be prompted to confirm your data source at times when that’s the last thing you want to worry about.


    Test Whether a Text Mergefield Contains Numeric Data
    The following field code allows you to test whether a given mergefield is text or numeric. This is useful if the field can contain mixed data and you want to control the formatting of the data.
    {QUOTE{SET Val {MERGEFIELD Data}}{IF{REF Val}= {=Val} "{Val} is Numeric" "{Val} is Text"}}
    and so:
    {QUOTE{SET Val {MERGEFIELD Data}}{IF{REF Val}= {=Val} {=Val \# 0.00} {REF Val}}}

    Note 1: This approach fails if the numeric data have more than a 13 digit decimal.

    Note 2: This approach is not intended to address problems cause by having text data in fields the mailmerge process has already determined to be numeric (see Mailmerge Data Format Problems above).


    Convert Mailmerge Date Fields to Plain Text
    The DATE field ordinarily survives the mailmerge process and remain active 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 (ie so that they become static dates), reflecting the date on which the merge was performed, simply embed them in a QUOTE field coded along the lines of:
    '{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 '\\' 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 after completing the merge; and
    3. even after updating the images, they remain linked to the image files, which can be an issue if you later delete the image or you need to send the merged output to someone else.

    The following field construction addresses all three issues (i.e. you don't need to do anything special to the paths, or refresh the fields, and they'll no longer be linked to the source files):
    {IF{INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\«Image»"} \d}}
    or:
    {IF{INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "C:\Users\My Document Path\Pictures\{MERGEFIELD Image}"} \d}}

    After running a mailmerge coded this way, you'll have the correct, unlinked image for each record.
    This form of field construction can be very useful where the filepath for the images is held in the mailmerge data source, in which case you could use:
    {IF{INCLUDEPICTURE {IF TRUE "«FilePath»\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "«FilePath»\«Image»"} \d}}
    or:
    {IF{INCLUDEPICTURE {IF TRUE "{MERGEFIELD FilePath}\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "{MERGEFIELD FilePath}\{MERGEFIELD 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 above construction but leaving it there has no adverse effects either.

    If you can be sure the pictures will always be in the same folder as the mailmerge main document, you can incorporate a FILENAME field thus:
    {IF{INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\{MERGEFIELD Image}"} \d} {INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\{MERGEFIELD Image}"} \d}}
    or:
    {IF{INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\«Image»"} \d} {INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\«Image»"} \d}}

    If the path data are included with in the image field, you can use:
    {IF{INCLUDEPICTURE {IF TRUE «Image»} \d} {INCLUDEPICTURE {IF TRUE «Image»} \d}}
    or:
    {IF{INCLUDEPICTURE {IF TRUE {MERGEFIELD Image}} \d} {INCLUDEPICTURE {IF TRUE {MERGEFIELD Image}} \d}}
    For what it's worth, *provided* the path has the separators expressed as '\\', you can retain the links by omitting the all-encompassing IF test and the images will display correctly without the need to refresh the fields after completing the merge. For example:
    {INCLUDEPICTURE {IF TRUE "C:\\Users\\My Document Path\\Pictures\\«Image»"} \d}


    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. Here's how you can do get a mailmerge to display your preferred default 'Text to display' text instead:
    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, by following 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.


    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.
    {MERGEFIELD First_Name}{MERGEFIELD Middle_Name \b " "} {IF{MERGEFIELD Spouse_First_Name}= "" {MERGEFIELD Last_Name} {IF{MERGEFIELD Spouse_Last_Name}= {MERGEFIELD Last_Name} "and {MERGEFIELD Spouse_First_Name}{MERGEFIELD Spouse_Middle_Name \b " "} {MERGEFIELD Last_Name}" {IF{MERGEFIELD Spouse_Last_Name}= "" "and {MERGEFIELD Spouse_First_Name}{MERGEFIELD Spouse_Middle_Name \b " "} {MERGEFIELD Last_Name}" "{MERGEFIELD Last_Name} and {MERGEFIELD Spouse_First_Name}{MERGEFIELD Spouse_Middle_Name \b " "} {MERGEFIELD 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.
    {MERGEFIELD A}{IF{MERGEFIELD C}= "" " and {MERGEFIELD B}" ", {MERGEFIELD B}{IF{MERGEFIELD D}= "" " and {MERGEFIELD C}" ", {MERGEFIELD C}{IF{MERGEFIELD E}= "" " and {MERGEFIELD D}" ", {MERGEFIELD D}{IF{MERGEFIELD F}= "" " and {MERGEFIELD E}" ", {MERGEFIELD E} and {MERGEFIELD F}"}"}"}"}

    Note: It is assumed that none of the fields after the first empty one will be populated.


    Mailmerge CheckBox Insertion
    To use a mergefield to toggle the state of a checkbox, insert an IF field coded as:
    {IF{MERGEFIELD CheckState}= "X" {SYMBOL 254 \f Wingdings \u } {SYMBOL 253 \f Wingdings \u }}
    or:
    {IF«CheckState»= "X" {SYMBOL 254 \f Wingdings \u } {SYMBOL 253 \f Wingdings \u }}
    where 'CheckState' 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' 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 ' \* Charformat' after 'MyData', so that you end up with {MERGEFIELD MyData \* Charformat};
    5. format at least the 'M' in 'MERGEFIELD' with the font attributes you want;
    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. To do this:
    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 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 precision of the displayed value 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 values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.

    Note 3: If you use a decimal tab or right-aligned tab to align the values, wrap the switch in quotes (i.e. \# "$,0.00") and insert a tab into the field code after the $ sign, you can have the values output with the decimal alignment occurring after the $ sign.


    Force '0' Substitution for Empty/Missing Records
    You can force the substitution of $0.00 for missing records via the following process:
    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 'Set Val' into the '{«Value»}' field, so that you end up with '{Set Val «Value»}'
    4. after the newly-inserted field, press Ctrl-F9 again to insert another (empty) field, so that you get '{Set Val «Value»}{ }'
    5. type '=Val \# "$,0.00"' into the second field, so that you end up with '{Set Val «Value»}{=Val \# "$,0.00"}'
    6. position the cursor anywhere in this field and press F9 to update it.


    Basic Mailmerge Maths
    You can create the formula in Word to perform maths 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.

    Note: the precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

    To see how to do a wide range of calculations in Word, check out my Microsoft Word Field Maths Tutorial:
    http://windowssecrets.com/forums/sho...Maths-Tutorial


    Dealing With Null Values in Mailmerge Arithmetic
    If your source data sometimes has empty fields, a formula referencing those fields is liable to fail with a Syntax Error. For example:
    {={MERGEFIELD Data1}+{MERGEFIELD Data2} \# 0}
    will fail in such cases. You can overcome this by testing each mergefield and replacing any nulls with 0s, as in:
    {={IF{MERGEFIELD Data1}= "" 0 {MERGEFIELD Data1}}+{IF{MERGEFIELD Data2}= "" 0 {MERGEFIELD Data2}}}
    or by coding the field along the lines of:
    {QUOTE{SET Val1 {MERGEFIELD Data1}}{SET Val2 {MERGEFIELD Data2}}{=Val1+Val2 \# 0}}


    Mailmerge Percentage Formatting
    To control percentage formatting in Word, add a formula and numeric picture switch to the mergefield, as follows:
    1. select your 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;
    5. run your mailmerge.

    Note: the precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.


    Mailmerge Phone Number Formatting
    To control Phone Number formatting in Word, 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"};
    5. position the cursor anywhere in this field and press F9 to update it.


    Mailmerge Date Formatting
    Word has a number of different methods of connecting to mail merge data sources, including DDE and OLE DB. Word 2002 and later use the OLE DB connection by default, though you can change this (to DDE, for example). To work around a limitation in the OLE DB provider used to get data from Excel etc., when Word is connected to an OLE DB data source, it treats dates as if they are in the US mm/dd/yyyy format, regardless of the format in Excel, 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, 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:
    http://windowssecrets.com/forums/sho...ation-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 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.


    Mailmerge US Social Security Number Formatting
    The following field suppresses all except the last four digits in a mergefield that gets its data from a field named ‘SSN’, where the data are formatted as ‘123-45-6789’.
    {QUOTE{SET ID {MERGEFIELD SSN}}{SET Part3 {=({ID}*(-1)-ID)/2}}"XXX-XX-"{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 may have incorrect formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes (eg because a db only accepts Zip Codes in the 5+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens. A data field named ‘ZipCode’ is assumed.
    {QUOTE
    {SET Zip {MERGEFIELD 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 - 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»= "Test String" "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 reson 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{MERGEFIELD MyField}= "MyText*" "True" "False"}
    or:
    {IF«MyField»= "MyText*" "True" "False"}
    tests whether the results of the MERGEFIELD named MyField starts with 'MyText'
    Similarly, a field coded as:
    {IF{MERGEFIELD MyField}= "*MyText" "True" "False"}
    or:
    {IF«MyField»= "*MyText" "True" "False"}
    tests whether the results of the MERGEFIELD named MyField 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{MERGEFIELD MyField}= "???MyText*" "True" "False"}
    or:
    {IF«MyField»= "???MyText*" "True" "False"}
    tests whether the results of the MERGEFIELD named MyField contains any three characters followed by 'MyText'
    Similarly, a field coded as:
    {IF{MERGEFIELD MyField}= "*MyText???" "True" "False"}
    or:
    {IF«MyField»= "*MyText???" "True" "False"}
    tests whether the results of the MERGEFIELD named MyField ends with any three characters after 'MyText'

    If a mergefield contains a mix of alpha-numeric text, with only a single number, you can extract that number for testing via a formula such as:
    {={MERGEFIELD MyField}}
    or:
    {=«MyField»}
    Having extracted the number, you can then test its value or perform other mathematical functions with 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:
    http://windowssecrets.com/forums/sho...Maths-Tutorial


    Mailmerge Empty Space Suppression
    You can use the mergefield \b and/or \f switches to suppress a space before or after an empty mergefield.
    Suppose you have:
    «Title» «FirstName» «SecondName» «LastName»
    but «SecondName» is sometimes empty. 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 need to use and IF test instead, coded along the lines of:
    {IF{MERGEFIELD SecondName}<> "" " {MERGEFIELD SecondName}"}
    or:
    {IF{MERGEFIELD SecondName}<> "" "{MERGEFIELD SecondName} "}


    Mailmerge Empty Line Suppression
    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. move the paragraph break or line break to be suppressed into field code so that you end up with {MERGEFIELD Address2 \f "¶
    "} or {MERGEFIELD Address2 \b "¶
    "};
    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 need to use and IF test instead, coded along the lines of:
    {IF{MERGEFIELD Address2}<> "" "{MERGEFIELD Address2}¶
    "}}
    or:
    {IF{MERGEFIELD Address2}<> "" "¶
    {MERGEFIELD Address2}"}}


    Catalog/Directory Mailmerge to Label Stationery
    To use a catalog mailmerge with label stationery, all you need to do is to set up the page layout in the mailmerge main document so that you've got the same column layout and margins as the labels, then put the mergefields inside a single-cell table of the correct dimensions for a single label.


    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»}"}
    or:
    {IF{MERGEREC}<> "" "Default Text: {IF{MERGEFIELD MyField}= "" "No Record" {MERGEFIELD MyField}}"}


    Send Mailmerge Output to Individual Files
    The following macro generates 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 requirements). PDF & DOCX formats are catered for.
    Code:
    Sub Merge_To_Individual_Files()
    'Merges one record at a time to the folder containing the mailmerge main document.
    ' Sourced from: http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks
    Application.ScreenUpdating = False
    Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j as long
    Set MainDoc = ActiveDocument
    With MainDoc
      StrFolder = .Path & Application.PathSeparator
      For i = 1 To .MailMerge.DataSource.RecordCount
        With .MailMerge
          .Destination = wdSendToNewDocument
          .SuppressBlankLines = True
          With .DataSource
            .FirstRecord = i
            .LastRecord = i
            .ActiveRecord = i
            If Trim(.DataFields("Last_Name")) = "" Then Exit For
            StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name")
          End With
          .Execute Pause:=False
        End With
        For j = 1 To 255
          Select Case j
            Case 1 To 31, 33, 34, 37, 42, 44, 46, 47, 58 - 63, 91 - 93, 96, 124, 147, 148
            StrName = Replace(StrName, Chr(j), "")
          End Select
        Next
        StrName = Trim(StrName)
        With ActiveDocument
          .SaveAs FileName:=StrPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
          ' and/or:
          .SaveAs FileName:=StrPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
          .Close SaveChanges:=False
        End With
      Next i
    End With
    Application.ScreenUpdating = True
    End Sub

    Convert Text Representations of Fields to Working Fields
    The following macro converts text representations of Word field codes to working field codes.
    Code:
    Sub FieldStringToCode()
    ' Converts "textual" field codes into real field codes. To do the conversion, simply paste the "textual" 
    ' field codes into your document, select them and run the macro.
    ' Sourced from: http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks
    Dim RngFld As Range, RngTmp As Range, oFld As Field
    Dim bFldCodes As Boolean, StrTmp As String, TrkStatus As Boolean
    Const Msg1 = "Select the text to convert and try again."
    Const Msg2 = "There are no field strings in the selected range."
    Const Msg3 = "Unmatched field brace pairs in the selected range."
    Const Title1 = "Error!"
    bFldCodes = ActiveDocument.ActiveWindow.View.ShowFieldCodes
    If Selection.Type <> wdSelectionNormal Then
      MsgBox Msg1, vbExclamation + vbOKOnly, Title1
      Exit Sub
    End If
    If InStr(1, Selection.Text, "{") = 0 Or _
      InStr(1, Selection.Text, "}") = 0 Then
      MsgBox Msg2, vbCritical + vbOKOnly, Title1
    End If
    If (Len(Replace(Selection.Text, "{", vbNullString)) <> _
      Len(Replace(Selection.Text, "}", vbNullString))) Or _
      (Len(Replace(Selection.Text, "«", vbNullString)) <> _
      Len(Replace(Selection.Text, "»", vbNullString))) Then
      MsgBox Msg3, vbCritical + vbOKOnly, Title1
      Exit Sub
    End If
    With ActiveDocument
      TrkStatus = .TrackRevisions
      .TrackRevisions = False
    End With
    Application.ScreenUpdating = False
    ActiveDocument.ActiveWindow.View.ShowFieldCodes = True
    Set RngFld = Selection.Range
    With RngFld
      .End = .End + 1
      With .Duplicate.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Format = False
        .Forward = True
        .Text = "»"
        .Replacement.Text = "}"
        .Execute Replace:=wdReplaceAll
        .Forward = False
        .Wrap = wdFindStop
        .Text = "«"
        .Replacement.Text = "{MERGEFIELD "
        .Execute Replace:=wdReplaceAll
      End With
      Do While InStr(1, .Text, "{") > 0
        Set RngTmp = ActiveDocument.Range(Start:=.Start + _
            InStr(.Text, "{") - 1, _
            End:=.Start + InStr(.Text, "}"))
        With RngTmp
          Do While Len(Replace(.Text, "{", vbNullString)) <> _
              Len(Replace(.Text, "}", vbNullString))
            .End = .End + 1
            If .Characters.Last.Text <> "}" Then .MoveEndUntil cset:="}", _
              Count:=Len(ActiveDocument.Range(.End, RngFld.End))
          Loop
          .Characters.First = vbNullString
          .Characters.Last = vbNullString
          StrTmp = .Text
          Set oFld = ActiveDocument.Fields.Add(Range:=RngTmp, _
              Type:=wdFieldEmpty, Text:="", PreserveFormatting:=False)
          oFld.Code.Text = StrTmp
        End With
      Loop
      ActiveDocument.ActiveWindow.View.ShowFieldCodes = bFldCodes
      .End = .End - 1
      If bFldCodes = False Then .Fields.ToggleShowCodes
      .Select
    End With
    Set RngTmp = Nothing: Set RngFld = Nothing: Set oFld = Nothing
    ActiveDocument.TrackRevisions = TrkStatus
    Application.ScreenUpdating = True
    End Sub

    Split Merged Output to Separate Documents
    Execute the merge to a new document and then run the following macro over that document.
    Code:
    Sub SplitMergedDocument()
    ' Sourced from: http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks
    Sub SplitMergedDocument()
    Dim i As Long, j As Long, k As Long, StrTxt As String
    Dim Rng As Range, Doc As Document, HdFt As HeaderFooter
    j = InputBox("How many Section breaks are there per record?", "Split By Sections", 1)
    With ActiveDocument
       ' Process each Section
      For i = 1 To .Sections.Count - 1 Step j
        With .Sections(i)
           '*****
           ' Get the 1st paragraph
          Set Rng = .Range.Paragraphs(1).Range
          With Rng
            ' Contract the range to exclude the final paragraph break
            .MoveEnd wdCharacter, -1
            ' Construct the destination file path & name
            StrTxt = ActiveDocument.Path & Application.PathSeparator & .Text
           '*****
          For k = 1 To 255
            Select Case k
              Case 1 To 31, 33, 34, 37, 42, 44, 46, 47, 58 - 63, 91 - 93, 96, 124, 147, 148
              StrTxt = Replace(StrTxt, Chr(k), "")
            End Select
          Next
          End With
           ' Get the whole Section
          Set Rng = .Range
          With Rng
            If j > 1 Then .MoveEnd wdSection, j - 1
             'Contract the range to exclude the Section break
            .MoveEnd wdCharacter, -1
             ' Copy the range
            .Copy
          End With
        End With
         ' Create the output document
        Set Doc = Documents.Add(Template:=ActiveDocument.AttachedTemplate.FullName, Visible:=False)
        With Doc
           ' Paste contents into the output document, preserving the formatting
          .Range.PasteAndFormat (wdFormatOriginalFormatting)
           ' Delete trailing paragraph breaks & page breaks at the end
          While .Characters.Last.Previous = vbCr Or .Characters.Last.Previous = Chr(12)
            .Characters.Last.Previous = vbNullString
          Wend
           ' Replicate the headers & footers
          For Each HdFt In Rng.Sections(j).Headers
            .Sections(j).Headers(HdFt.Index).Range.FormattedText = HdFt.Range.FormattedText
          Next
          For Each HdFt In Rng.Sections(j).Footers
            .Sections(j).Footers(HdFt.Index).Range.FormattedText = HdFt.Range.FormattedText
          Next
           ' Save & close the output document
          .SaveAs FileName:=StrTxt & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
           ' and/or:
          .SaveAs FileName:=StrTxt & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
          .Close SaveChanges:=False
        End With
      Next
    End With
    Set Rng = Nothing: Set Doc = Nothing
    Application.ScreenUpdating = True
    End Sub
    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
    Code:
            ' Construct the destination file path & name
            StrTxt = ActiveDocument.Path & Application.PathSeparator & (i + j - 1) / j
    Last edited by macropod; 2014-11-19 at 18:31.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  2. The Following User Says Thank You to macropod For This Useful Post:

    quetzalc0atl (2014-09-10)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •