How To Merge Addresses From Excel To Word Labels
07-09-2014, 12:49 AM
Mailmerge Tips & Tricks
Introduction
The post-obit tips address a broad range of issues people take with mailmerges.
There are also 4 useful macros towards the terminate of this thread, to:
• Transport Mailmerge Output to Individual Files;
• Run a Mailmerge from Excel, Sending the Output to Private Files;
• Split up Merged Output to Split up Documents; and
• Convert Text Representations of Fields to Working Fields.
For PC macro installation & usage instructions, see: Installing Macros.
For Mac macro installation & usage instructions, see: Word:mac - Install a Macro
Notation 1: The field brace pairs (i.e. '{ }') for the post-obit examples are all created in the mailmerge main document itself, via Ctrl-F9 (Cmd-F9 on a Mac); yous tin't simply type them or copy & paste them from here. Nor is it practicable (for the most office) to add together them via the standard Word dialogues. Besides, y'all can't type or copy & paste the chevrons (i.due east. '� �') - they're function of the actual mergefields, inserted via the 'Insert Merge Field' dropdown. The spaces represented in the field constructions are all required. Instead of the →, ↵ and � symbols shown in the examples, you lot should apply real tabs and line/paragraph breaks, respectively.
Note 2: Mailmerge previews are not a reliable indicator of how compound fields are processed past a mailmerge, especially where provisional output is concerned. For testing & validation purposes, always send the output to a new document.
Note three: Some of the field examples below employ IF tests (e.grand. {IF�MyData�= "MyText" "True" "Faux"}). If the �MyData� mergefield outputs alpha-numeric content (e.g. 123 ABC), you lot may demand to enclose information technology in double quotes (e.g. {IF "�MyData�" = "MyText" "True" "False"}).
Mailmerge Data Format Bug
By default, Word 2002 & afterwards employ OLE DB to get records from the information source. Because the OLE DB provider is designed to return data in a way that is compatible with databases, information technology requires a specific data type for each field, and every record in that field must be of that data blazon. When using other data sources, the OLE DB provider queries the first viii records to decide the information type for each field. This can lead to unexpected results with data sources such as Excel workbooks, where rows (records) in a cavalcade (field) accept mixed information types. When the OLE DB provider gets data from a column with mixed data types, records that don't conform to the determined data blazon for the column may not exist handled correctly. To complicate matters, at that place are two text information types: "text" (upwards to 255 characters) and "memo" (upward to 65,536 characters - one Gigabyte in Access if the text is entered programmatically).
Some common mailmerge problems arising out of this include:
• Numbers but not text or dates existence output; and
• Text information being truncated at 255 characters.
Ideally, one would ensure each field has but i data type. Workarounds include:
• Inserting a dummy outset record containing data in the format that is not being output correctly; or
• Reordering the data so the first tape has content in the format that is not otherwise being output correctly.
Thus, if numbers announced 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 than 255 characters.
The utilise of field switches to the mailmerge primary certificate is the preferred method of controlling the output format (amongst other things information technology allows the mailmerge to format the data differently than the source). However, y'all tin can also preserve the source data's formatting by using Dynamic Data Commutation (DDE). DDE is only useful if the mailmerge data are in the outset sheet in the workbook and the data first on the commencement row of that sheet. To use DDE when connecting to an Excel worksheet during a mail service merge:
• In Word 2003 & before, on the Discussion Tools menu, click on Tools|Options|General;
• In Word 2007, click on Function Button|Options|Advanced>Full general;
• In Word 2010 & later on, click on File|Options|Advanced>General,
and so bank check the 'Ostend conversion at Open up' option.
At the step in the mail-merge process where you connect to your data file, after yous locate the file you want to connect to, the Ostend Data Source dialog box opens. Click "Awarding via DDE (*.???)", and and then click OK. In the Application dialog box, select the table/range that contains the data you want to merge, and so click OK. The data will now accept the aforementioned formatting in your merged documents as they have in the source file.
Although the Windows DDE dialog box allows and presents range names for the data source, information technology just presents names that reference the outset sheet and have either Workbook telescopic or the same scope as the sail.
Hint: Turn off the Confirm conversion at Open choice after you end your post merge.
Another mutual 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, appointment, number, etc.) just, regardless of whether the information source is an Excel workbook, a Give-and-take table, an Admission database, or a text file. Formatting like font colours, background shading, etc. are neither data values nor data types.
Exam Whether a Text Mergefield Contains Numeric Data
The following field lawmaking allows you to examination whether a given mergefield is text or numeric. This is useful if the field can incorporate mixed information and you lot desire to control the formatting of the data.
{QUOTE{SET Val {MERGEFIELD Information}}{IF{REF Val}= {=Val} "{Val} is Numeric" "{Val} is Text"}}
so:
{QUOTE{SET Val {MERGEFIELD Data}}{IF{REF Val}= {=Val} {=Val \# 0.00} {REF Val}}}
Note: This arroyo fails if the numeric data take more than a thirteen digit decimal.
Convert Mailmerge Date Fields to Evidently Text
The DATE field commonly survives the mailmerge process and remain active in the output document, which can be problematic, since such fields update to the electric current appointment every time you open up the document. Conversely, CREATEDATE fields from the mailmerge main document reverberate that document'due south creation date (not that of the mailmerge output).
To force DATE fields to convert to their results (ie and then that they become static dates), reflecting the engagement on which the merge was performed, simply embed them in a QUOTE field coded along the lines of:
{QUOTE{Engagement}}
plus any formatting switches you lot might want for the date format.
Managing Mailmerge Graphics
To insert variable images in a mailmerge, you lot need to embed the relevant mailmerge field in a INCLUDEPICTURE field. Withal, 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:
i. the file paths to the fields need to accept the separators expressed as '\\' instead of the usual '\';
2. the pictures ordinarily won't prove until yous refresh the fields (eg Ctrl-A, then F9) in the output certificate afterwards completing the merge; and
3. even after updating the images, they remain linked to the paradigm files, which can be an consequence if you later delete the image or y'all need to send the merged output to someone else.
The following field constructions and accompanying macro address all iii issues (i.eastward. you don't need to do anything special to the paths, or refresh the fields, and they'll no longer exist linked to the source files):
• If the path to the images isn't included in the data source and you tin can't exist sure they will ever be in the aforementioned binder as the mailmerge chief certificate, y'all could use a field coded as:
{INCLUDEPICTURE {IF Truthful "C:\Users\My Document Path\Pictures\�Image�"} \d}
or:
{INCLUDEPICTURE {IF TRUE "C:\Users\My Certificate Path\Pictures\{MERGEFIELD Prototype}"} \d}
• If the path to the images is held in a split up field in the mailmerge data source, you could use:
{INCLUDEPICTURE {IF TRUE "�FilePath�\�Image�"} \d}
or:
{INCLUDEPICTURE {IF TRUE "{MERGEFIELD FilePath}\{MERGEFIELD Prototype}"} \d}
Note: You need a path separator betwixt the filepath mergefield and the prototype mergefield. If that separator is included in the source data, it tin be omitted from the field above construction only leaving it there has no adverse furnishings either.
• If the path to the images is held in the same field as the prototype name in the mailmerge data source, you could utilize:
{INCLUDEPICTURE {IF Truthful "�Epitome�"} \d}
or:
{INCLUDEPICTURE {IF True "{MERGEFIELD Image}"} \d}
• If the path to the images isn't included in the data source but you tin can be sure they will always be in the same folder as the mailmerge primary document, y'all can incorporate a FILENAME field thus:
{INCLUDEPICTURE {IF TRUE "{FILENAME \p}\..\{MERGEFIELD Image}"} \d}
or:
{INCLUDEPICTURE {IF Truthful "{FILENAME \p}\..\�Image�"} \d}
By adding the post-obit macro to your mailmerge master document, clicking on the 'Edit Private Documents' push button will intercept the merge, finishing it and unlinking the pictures (and whatsoever other residual fields except for hyperlinks).
Code:
Sub MailMergeToDoc() Awarding.ScreenUpdating = False Dim i As Long ActiveDocument.MailMerge.Execute With ActiveDocument For i = .Fields.Count To 1 Step -one If .Fields(i).Type <> wdFieldHyperlink Then .Fields(i).Unlink Next End With Application.ScreenUpdating = Truthful Terminate Sub
If you're using Word 2007 or later, your mailmerge main certificate volition demand to be saved in the .physician or .docm formats, equally documents using the .docx format cannot comprise macros. 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 tin can however achieve the same outcome - and with greater control - via the 'Edit Recipient List' tools.
For what it'southward worth, *provided* the path has the separators expressed as '\\', you lot can retain the links by omitting the macro.
Mailmerge Hyperlink 'Click Here' Insertion
Past default, if y'all insert a mailmerge field into a hyperlink field, the hyperlinks will all show the first record's accost as the 'Text to brandish' text. Here's how you tin can do get a mailmerge to display your preferred default 'Text to display' text instead:
i. Disregarding mergefield issues for the moment, insert a hyperlink into the document in the normal style, choosing whatever 'Click Hither' text you want in the 'Text to display' box.
ii. Select the inserted hyperlink and press Shift-F9 to expose its field code.
three. Supersede everything in the field later 'HYPERLINK' with your mergefield.
4. Select the field and press F9 to update the display.
In Give-and-take 2007 & subsequently, you can brand the display text variable also, by following these additional steps:
five. Position the cursor anywhere within the display text.
6. Insert a mergefield pointing to whatever information field you desire to utilise for the display text (this could even be the aforementioned field as used at step 3 above).
7. Delete all of the previous brandish text either side of your concluding-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 to a higher place 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 a mail merge on e-mail messages in HTML format in Word 2002 and Word 2003
Notation: Hyperlink fields modified this style are liable to stop functioning once the merge has been executed. Accordingly, information technology's best to salvage mailmerge main document earlier doing the merge and not re-save it afterwards. If y'all need to make changes to the mailmerge main document, don't make/salve them after doing a merge; brand/save them beforehand.
Conditionally Merge Spouse Data
The following construction can be used to manage data where some recipients may be living in a marital human 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 structure tin can exist used to manage data where one or more than related fields (A, B, C, D, Due east, & 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 Due east}= "" " and {MERGEFIELD D}" ", {MERGEFIELD D}{IF{MERGEFIELD F}= "" " and {MERGEFIELD E}" ", {MERGEFIELD East} and {MERGEFIELD F}"}"}"}"}
Note: It is assumed that none of the fields after the beginning empty one volition be populated.
Mailmerge CheckBox Insertion
To apply a mergefield to toggle the land of a checkbox, insert an IF field coded every bit:
{IF{MERGEFIELD CheckState}= "X" {SYMBOL 254 \f Wingdings \u } {SYMBOL 253 \f Wingdings \u }}
or:
{IF�CheckState�= "Ten" {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 Cord Formatting
to control mailmerge string formatting, add a 'Charformat' picture switch to the mergefield equally follows:
1. select the mergefield;
2. printing Shift-F9 to betrayal the field coding. It should look something similar {MERGEFIELD MyData}, where 'MyData' is your data field'south proper name;
3. delete everything betwixt 'MyData' and the closing field caryatid;
4. add together ' \* Charformat' after 'MyData', so that you end up with {MERGEFIELD MyData \* Charformat};
v. format at to the lowest degree the 'M' in 'MERGEFIELD' with the font attributes yous desire;
six. 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 flick switch to the mergefield. To do this:
one. select the mergefield;
2. press Shift-F9 to reveal the field coding. It should look something similar {MERGEFIELD MyData};
3. edit the field and so that y'all become {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you adopt - run into beneath);
4. position the cursor anywhere in this field and printing F9 to update it.
Note 1: The '\# $,0.00' in the field is referred to as a numeric moving 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
Annotation 2: The precision of the displayed value is controlled by the '0.00'. You can apply anything from '0' to '0.000000000000000'.
If you use a last ';' in the formatting switch with aught post-obit, (eg \# "$,0.00;($,0.00);") null values will exist suppressed. Annotation that this suppresses 0s resulting from empty fields and from fields containing 0s.
Note 3: If y'all use a decimal tab or correct-aligned tab to align the values, wrap the switch in quotes (i.e. \# "$,0.00") and insert a tab into the field lawmaking after the $ sign, y'all can have the values output with the decimal alignment occurring after the $ sign.
Force '0' Exchange for Empty/Missing Records
There is a variety of ways to force the commutation of 0 for missing records. For example:
1. select your mergefield, which will await something like �Value�
two. printing Ctrl-F9 to embed your mergefield in another field, so that y'all get {�Value�}
3. blazon 'Set up Val' into the {�Value�} field, and so that you end up with {Set Val �Value�}
four. subsequently the newly-inserted field, printing Ctrl-F9 over again to insert another (empty) field, and then that you become {Fix Val �Value�}{ }
v. type '=Val \# "$,0.00"' into the second field, so that you lot end up with {Set Val �Value�}{=Val \# 0}
6. position the cursor anywhere in this field and press F9 to update it.
Alternatively:
1. insert a pair of your mergefields so you run into something similar �Value� �Value�
two. select both mergefields.
three. press Ctrl-F9 to embed both mergefield in some other field, so that you get {�Value� �Value�}
iv. fill up in the field, then that you end up with {IF�Value�= "" 0 �Value�}
five. position the cursor anywhere in this field and press F9 to update it.
Yet another approach is to:
1. select your mergefield, which will expect something like �Value�.
two. press Ctrl-F9 to embed your mergefield in some other field, and so that you go {�Value�}
3. blazon 'QUOTE 0"' into the {�Value�} field before the �Value� and '"' after it, and then that you end upward with {QUOTE "0�Value�"}
iv. position the cursor anywhere in this field and press F9 to update it.
This last arroyo works past prepending the mergefield'southward result with a 0, so the results of any calculations should be the same.
Basic Mailmerge Maths
Y'all can create the formula in Word to perform maths on a mergefield. For case, suppose your data include the final price of an particular for which you need to show how much is the base price and how much is the tax component, where the tax is x% of the base of operations cost (ie 1/11th of the final cost):
1. select your mergefield, which will look something like �Price�;
2. press Ctrl-F9 to wrap another field around it, thus { �Cost� };
three. to calculate the tax component, edit the field then that you get {=�Toll�/11 \# "$,0.00"};
4. to summate the base price, edit the field so that yous go {=�Price�*10/11 \# "$,0.00"};
5. position the cursor anywhere in this field and press F9 to update it.
Annotation: the precision of the displayed value is controlled by the '0.00'. You lot can employ anything from '0' to '0.000000000000000'.
To see how to exercise a broad range of calculations in Word, check out my Microsoft Give-and-take Field Maths Tutorial, available in the 'Pasty' thread at the top of the Give-and-take forum (https://world wide web.msofficeforums.com/word/...-tutorial.html).
Dealing With Null Values in Mailmerge Arithmetics
If your source data sometimes has empty fields, a formula referencing those fields is liable to fail with a Syntax Mistake. For example:
{={MERGEFIELD Data1}+{MERGEFIELD Data2} \# 0}
and:
{=�Data1�+�Data2� \# 0}
will fail in such cases. You lot can overcome this by just prefixing the mergefields with 0s, as in:
{=0{MERGEFIELD Data1}+0{MERGEFIELD Data2} \# 0}
and:
{=0�Data1�+0�Data2� \# 0}
Mailmerge Per centum Formatting
To command percentage formatting in Word, add a formula and numeric picture switch to the mergefield, as follows:
one. select your mergefield, which will look something like �Percentage�;
2. printing Ctrl-F9 to wrap another field around it, thus { �Pct� };
3. edit the field and then that you get {=�Percent�*100 \# 0.00%};
4. position the cursor anywhere in this field and press F9 to update information technology;
5. run your mailmerge.
Annotation: the precision of the displayed value is controlled by the '0.00'. You tin utilize annihilation from '0' to '0.000000000000000'.
Mailmerge Telephone Number Formatting
To command Phone Number formatting in Discussion, all you need to practise is to add a picture show switch to the mergefield, as follows:
one. 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 proper noun;
3. delete everything betwixt 'MyData' and the closing field brace;
four. add together ' \# "(000) 0000 0000"' after 'MyData', then that you end up with {MERGEFIELD MyData \# "(000) 0000 0000"}. Other phone # formats can too be specified this way;
5. position the cursor anywhere in this field and press F9 to update it.
Mailmerge Appointment Formatting
Word has a number of different methods of connecting to mail merge data sources, including DDE and OLE DB. Word 2002 and afterwards use the OLE DB connection by default, though you can change this (to DDE, for example). To piece of 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 desire, regardless of whether you employ OLE DB or DDE, you can add a formatting moving picture switch every bit follows:
ane. 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;
three. delete anything appearing after the mergefield'due south name and add '\@ "d MMMM yyyy"' to the field, every bit in {MERGEFIELD MyDate \@ "d MMMM yyyy"}. With this switch your dates will come out similar '2 August 2008'.
4. position the cursor anywhere in this field and press F9 to update it.
Other possible engagement formatting switches include:
• \@ "dddd, d MMMM yyyy";
• \@ "ddd, d MMMM yyyy";
• \@ "d MMM yyyy";
• \@ "dd/MMM/yyyy";
• \@ "d-MM-yy".
Annotation: You can swap the d, M, y expressions effectually, but you must use uppercase 'M' sequences for months - lowercase 'm' sequences are for minutes.
Mailmerge Date Calculations
To see how to do simply virtually everything you might desire to exercise with dates in Word, cheque out my Microsoft Word Date Calculation Tutorial, available in the 'Sticky' thread at the top of the Word forum (https://www.msofficeforums.com/word/...-tutorial.html). For mailmerges, in particular, look at the item titled 'Date and Time Calculations In A Mailmerge'. Exercise read the document's introductory material, as it contains important data regarding configuring the fields for different date formats.
Mailmerge Time Formatting
To get the time format you lot want, yous can add a formatting movie switch as follows:
ane. select the mergefield;
2. printing Shift-F9 to expose the field coding. Information technology should expect something similar {MERGEFIELD MyTime} where 'MyTime' is your mergefield's proper name;
3. delete anything appearing afterwards the mergefield'due south proper name and add '\@ "h:m"', as in {MERGEFIELD MyTime \@ "h:m am/pm"};
4. if you want the hours and/or minutes to brandish leading 0s, alter 'h' to 'hh for hours and 'g' to 'mm' for minutes;
5. position the cursor anywhere in this field and press F9 to update it.
Note: you must use lowercase 'thousand's for minutes - uppercase 'G'due south are for months.
Mailmerge US Social Security Number Formatting
The following field suppresses all except the concluding iv digits in a mergefield where the data are formatted as '123-45-6789'.
{QUOTE{Gear up ID {MERGEFIELD SSN}}{Ready Part3 {=({ID}*(-1)-ID)/2}}"XXX-20-"{Part3 \# 0000}}
Mailmerge US Null Lawmaking Formatting
The following field code correctly formats five-digit and v+iv-digit Zip Code information, even where the source data may have incorrect formatting such every bit a hyphen followed past abaft 0s subsequently plain v-digit Zip Codes (eg because a db just accepts Nothing Codes in the v+4-digit format) or is formatted as a 5-digit or 9-digit string without hyphens.
{QUOTE
{Set up Zip {MERGEFIELD ZipCode}}
{IF{Zip}> 99999 {Prepare Zilch {Cypher \# "00000'-'0000"}}}
"{=-{=-{Zip}-Nada}/2 \# 00000;;}{=-({Zip}*(-1)-Cypher)/2 \# ;-0000;}"}
Annotation: the line breaks depicted here are non needed - they're merely used to help with visualizing the lawmaking'southward construction.
Mailmerge String Testing
You tin exam the contents of a mergefield by doing the post-obit:
1. select the field and Press Ctrl-F9 to enclose it in a new field, thus {�MyData�};
two. fill in around the new field braces so that yous stop up with: {IF�MyData�= "MyText" "String/field to output if Truthful" "String/field to output if False"};
3. position the cursor anywhere in this field and press F9 to update information technology.
Test Whether a Mergefield Contains a Given Cord
When doing a mailmerge, the 'If The Else' Rules offer the following options in the driblet downwardly menu:
• Field Name Equal To
• Field Name Not Equal To
• Field Proper noun Less Than
• Field Name Greater Than
• Field Proper name Less Than or Equal
• Field Name Great Than or Equal
• Field Proper noun is Blank
• Field Proper noun is Not Blank
Word doesn't accept a 'Field Proper name Contains' option. The simple reason is that Word has no such field. That said, there are limited means for testing whether a field contains a given string.
For example a field coded every bit:
{IF{MERGEFIELD MyField}= "MyText*" "True" "False"}
or:
{IF�MyField�= "MyText*" "True" "Fake"}
tests whether the results of the mergefield starts with 'MyText'
Similarly, a field coded equally:
{IF{MERGEFIELD MyField}= "*MyText" "Truthful" "False"}
or:
{IF�MyField�= "*MyText" "True" "False"}
tests whether the results of the mergefield ends with 'MyText'
You tin can besides test whether the text you lot're interested in exists a pre-divers number of characters from the showtime or end. Thus, a field coded equally:
{IF{MERGEFIELD MyField}= "???MyText*" "True" "Simulated"}
or:
{IF�MyField�= "???MyText*" "True" "Fake"}
tests whether the results of the mergefield contains whatsoever three characters followed past 'MyText'
Similarly, a field coded as:
{IF{MERGEFIELD MyField}= "*MyText???" "True" "Imitation"}
or:
{IF�MyField�= "*MyText???" "Truthful" "False"}
tests whether the results of the mergefield ends with whatsoever 3 characters after 'MyText'.
If a mergefield contains a mix of alpha-numeric text, with only a unmarried, non-zero, number, you lot can extract that number for testing via a field switch such equally:
{MERGEFIELD MyField \# 0} (with the appropriate decimal provisions, if whatever)
or ,via a formula such as:
{={MERGEFIELD MyField}}
or:
{=�MyField�}
Having extracted the number, you tin can then test its value or perform other mathematical functions on in it the normal way. To see how to exercise a broad range of calculations in Word, check out my Microsoft Word Field Maths Tutorial, available in the 'Sticky' thread at the top of the Word forum (https://www.msofficeforums.com/word/...-tutorial.html).
Mailmerge Empty Space Suppression
On PCs, you can utilise the mergefield \b and/or \f switches to suppress a infinite earlier or after an empty mergefield. For example, suppose you have:
�Championship� �FirstName� �SecondName� �LastName�
but �SecondName� is sometimes empty and you don't want that to leave 2 spaces in the output. To deal with that:
1. select the �SecondName� field and press Shift-F9 so that y'all get {MERGEFIELD SecondName};
2. edit the field code so that you terminate upwardly with-
{MERGEFIELD SecondName \f " "}
or:
{MERGEFIELD SecondName \b " "}
depending on whether the space to be suppressed is following or before the mergefield;
three. delete, as appropriate, the corresponding space post-obit or earlier the mergefield;
4. position the cursor anywhere in this field and press F9 to update information technology.
Annotation: the \b and \f switches don't work on Macs or in conjunction with other switches. In such cases you demand to use and IF test instead, coded along the lines of:
{IF{MERGEFIELD SecondName}<> "" " {MERGEFIELD SecondName}"}
or:
{IF{MERGEFIELD SecondName}<> "" "{MERGEFIELD SecondName} "}
Even and then, yous can use the \b and \f switches to express other mergefields that do accept switches of their own. For instance, suppose yous have iv fields 'Product', 'Supplier', 'Quantity' and 'UnitPrice', and you don't desire 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 Production}→" \f "→{MERGEFIELD Quantity \# 0}→{MERGEFIELD UnitPrice \# "$0.00"}�
"}
Mailmerge Empty Line Suppression
On PCs, you tin can use the mergefield \b and/or \f switches to suppress a paragraph interruption (or a line break) before and/or after an empty mergefield.
Suppose yous 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};
two. edit the field code then that y'all get {MERGEFIELD Address2 \f ""} or {MERGEFIELD Address2 \b ""}, depending on whether the line to be suppressed is post-obit 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 "�
"}
if you movement the intermission following the field, or, if you lot move the break preceding the field:
{MERGEFIELD Address2 \b "�
"}
iv. position the cursor anywhere in this field and press F9 to update it.
Notation: 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 y'all demand to use and IF examination instead, coded along the lines of:
{IF{MERGEFIELD Address2}<> "" "{MERGEFIELD Address2}�
"}}
or:
{IF{MERGEFIELD Address2}<> "" "�
{MERGEFIELD Address2}"}}
Similarly, for conditionally outputting formatted phone numbers, you might utilise:
{MERGEFIELD Home_Phone \# "'h. '(##) #### ####'�
';;"}{MERGEFIELD Work_Phone \# "'w. '(##) #### ####'�
';;"}{MERGEFIELD Mobile_Phone \# "'thousand. '#### ### ###'�
';;"}{MERGEFIELD e-mail}
Conditionally Format Mailmerge Output
To conditionally format mergefield outputs, you could use a series of fields coded every bit:
{
I F�MyField�= "1" �MyField� \* Charformat}
{ I F�MyField�= "2" �MyField� \* Charformat}
{ I F�MyField�= "iii" �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, y'all could utilize a series of fields coded as:
{
I F�MyField�= "i" "Text for 1" \* Charformat}
{ I F�MyField�= "2" "Text for ii" \* Charformat}
{ I F�MyField�= "three" "Text for 3" \* Charformat}
or:
{IF�MyField�= "one" "Patently Text and coloured text for 1 "}
{IF�MyField�= "2" "Plain Text and coloured text for two "}
{IF�MyField�= "3" "Evidently Text and coloured text for 3 "}
The advantage of the second approach is the ability to mix the formats of the output.
Notation i: Although the to a higher place examples use coloured text, you can utilize whatsoever text formatting you desire.
Notation two: Although the above examples are laid out on carve up lines, that is for presentation purposes only; your mailmerge main document would have the fields all on the aforementioned logical line.
Note 3: Where a mergefield outputs only numeric data, information technology may be possible to use numeric motion-picture show switches to apply the colouring. This is described in my Microsoft Word Field Maths Tutorial, available in the 'Viscous' thread at the top of the Word forum (https://www.msofficeforums.com/word/...-tutorial.html) and in Conditionally Colour/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 piece of work on systems with Office fix for use with a RTL keyboard - fifty-fifty if that keyboard is not enabled. A workaround would be to apply the desired formatting to the entire field (preferably via a Graphic symbol Fashion).
Conditionally Shade Table Cells
Discussion doesn't take a conditional shading function like Excel has, only you can attain a similar result in a mailmerge via field coding. To do this, you demand a set of fields (one for each condition) in the relevant cell(s), along the lines of:
{
I F{MERGEFIELD Condition}= "one" "→→↵
→Condition 1 Text→↵
→→" \* Charformat}
{ I F{MERGEFIELD Condition}= "2" "→→↵
→Status two Text→↵
→→" \* Charformat}
{ I F{MERGEFIELD 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 correct-aligned tab-stop set to at least the cell width.
Note: All the fields depicted above should be inserted i afterwards the other, without line/paragraph breaks between them - the in a higher place 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 exist used to expand the colour differentiations even further.
Similarly, if you have numeric information that can be +ve, -ve, or 0, and utilize shading as follows, you gain access to all of Word's shading options:
{QUOTE{={MERGEFIELD Value1}-{MERGEFIELD Value2} \# "
' →↵
→' ; ' →↵
→' ; ' →↵
→' "}}
Conditionally Color/Shade Numeric Output Ranges
Y'all can use field coded to utilise upwardly to three colour/highlight formats to numeric information falling into various ranges. In the following examples, values upwardly to 200 become coloured green, values over 240 go coloured red and values between these get coloured orangish.
{QUOTE{=INT({MERGEFIELD Val}/forty)-5 \# "{ G ERGEFIELD Val \* Charformat }
- High';'{ M ERGEFIELD Val \* Charformat } - Low';'{ M ERGEFIELD Val \* Charformat } - Medium'}}
The above structure allows for up to 64 characters in the field switch. For fifty-fifty more characters, you can use coding along the lines of:
{QUOTE{=INT({MERGEFIELD Val}/40)-5 \# "{QUOTE"{ K ERGEFIELD Val \* Charformat }
- High"};{QUOTE"{ M ERGEFIELD Val \* Charformat } - Low"};{QUOTE"{ M ERGEFIELD Val \* Charformat } - Medium"}"}}
Catalog/Directory Mailmerge to Label Stationery
To use a catalog mailmerge with label stationery, all you lot need to do is to fix the page layout in the mailmerge main document so that you've got the same cavalcade layout and margins as the labels, and then put the mergefields within a single-cell tabular array 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 characterization merge, even afterward 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 exist use with the conditional display of other mergefields in conjunction with the default text. For example:
{IF{MERGEREC}<> "" "Default Text: {IF�MyField�= "" "No Tape" �MyField�}"}
or:
{IF{MERGEREC}<> "" "Default Text: {IF{MERGEFIELD MyField}= "" "No Record" {MERGEFIELD MyField}}"}
Transport Mailmerge Output to Individual Files
Past adding the following macro to your mailmerge master document, you can generate one output file per record. Files are saved to the aforementioned folder as the mailmerge primary certificate, using the 'Last_Name' & 'First_Name' fields in the data source for the filenames (modify these to suit your needs). PDF & DOCX formats are catered for.
Code:
Sub Merge_To_Individual_Files() ' Sourced from: https://www.msofficeforums.com/mail service-merge/21803-mailmerge-tips-tricks.html Application.ScreenUpdating = Fake Dim StrFolder As String, StrName Equally String, MainDoc As Document, i As Long, j As Long Const StrNoChr As String = """*./\:?|" Prepare MainDoc = ActiveDocument With MainDoc StrFolder = .Path & "\" With .MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True On Mistake Resume Next For i = 1 To .DataSource.RecordCount With .DataSource .FirstRecord = i .LastRecord = i .ActiveRecord = i If Trim(.DataFields("Last_Name")) = "" Then Leave For 'StrFolder = .DataFields("Binder") & "\" StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name") End With On Fault GoTo NextRecord .Execute Intermission:=Faux For j = ane To Len(StrNoChr) StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_") Next StrName = Trim(StrName) With ActiveDocument 'Add the name to the footer '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False ' and/or: .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=Imitation .Shut SaveChanges:=False End With NextRecord: Next i End With End With Application.ScreenUpdating = True End Sub Note 1: If you rename the to a higher place macro as 'MailMergeToDoc', clicking on the 'Edit Individual Documents' push button will intercept the merge and the process volition run automatically. The potential disadvantage of intercepting the 'Edit Private Documents' process this manner is that you no longer go to choose which records to merge at that stage. Nevertheless, you tin can withal achieve the aforementioned outcome - and with greater control - via the 'Edit Recipient Listing' tools.
Note 2: If you're using Word 2007 or later, your mailmerge main document volition demand to be saved in the .dr. or .docm formats, every bit documents using the .docx format cannot contain macros.
Annotation 3: The above code defaults to saving the output to the mailmerge main certificate's folder. You can alter the destination folder past editing:
Code:
StrFolder = .Path & "\"
If destination folders are specified in the data source, you could delete or comment-out that line and un-comment the line:
Code:
'StrFolder = .DataFields("Folder") & "\" where the binder the output is to be saved to is in a information field named 'Binder'.
If there is a risk that the output folder does not exist, it tin exist created on the wing by adding:
Code:
If Dir(StrFolder) = "" Then MkDir StrFolder
Alternatively, to save the output to the same folder as the information source, y'all could supersede:
Code:
StrFolder = .Path & "\"
with:
Code:
StrFolder = .MailMerge.DataSource.Name i = InStrRev(StrFolder, "\") StrFolder = Left(StrFolder, i)
Illegal filename characters are replaced with underscores.
Note iv: The in a higher place code too provides for the filename to be output to the folio 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 accept a document named 'MailMergeMainDocument.docx' stored in the aforementioned folder as the Excel workbook. That document should non contain macros or protection and should be saved as an ordinary document or as a mailmerge main certificate.
As coded, the macro also assumes a standard query, processing all records from Sheet1. Modify the sheet references, as appropriate. If you're using filtering, you lot'd have to add that to the macro's SQLStatement, too.
Each record's output is sent to a new file in the same binder as the Excel workbook, using the 'Last_Name' & 'First_Name' fields in the data source for the filenames (change these to conform your requirements). Illegal filename characters are replaced with underscores.
Code:
Sub RunMerge() ' Sourced from: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html ' Note: this code requires a reference to the Discussion object model to be set up, via Tools|References in the VBE. Awarding.ScreenUpdating = Fake Dim StrMMSrc As String, StrMMDoc As Cord, StrMMPath As String, StrName Equally String Dim i Every bit Long, j As Long Const StrNoChr Equally String = """*./\:?|" Dim wdApp As New Word.Application, wdDoc As Give-and-take.Certificate wdApp.Visible = Imitation wdApp.DisplayAlerts = wdAlertsNone StrMMSrc = ThisWorkbook.FullName StrMMPath = ThisWorkbook.Path & "\" StrMMDoc = StrMMPath & "MailMergeMainDocument.doc" Set up wdDoc = wdApp.Documents.Open up(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False) With wdDoc With .MailMerge .MainDocumentType = wdFormLetters .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=Faux, _ LinkToSource:=False, Connexion:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _ "Data Source=StrMMSrc;Style=Read;Extended Properties=""HDR=YES;IMEX=1"";", _ SQLStatement:="SELECT * FROM `Sheet1$`" For i = 1 To .DataSource.RecordCount .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = i .LastRecord = i .ActiveRecord = i If Trim(.DataFields("LAST_NAME")) = "" And so Exit For StrName = .DataFields("LAST_NAME") & "_" & .DataFields("FIRST_NAME") End With .Execute Suspension:=False For j = ane To Len(StrNoChr) StrName = Supersede(StrName, Mid(StrNoChr, j, i), "_") Next StrName = Trim(StrName) With wdApp.ActiveDocument 'Add together the name to the footer '.Sections(ane).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName .SaveAs Filename:=StrMMPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False ' and/or: '.SaveAs Filename:=StrMMPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=Imitation .Close SaveChanges:=False Stop With Next i .MainDocumentType = wdNotAMergeDocument Cease With .Shut SaveChanges:=Imitation End With wdApp.DisplayAlerts = wdAlertsAll wdApp.Quit Set wdDoc = Zip: Set wdApp = Nothing Application.ScreenUpdating = False Cease Sub Annotation 1: For testing purposes, y'all might want to change the line 'wdApp.Visible = False' to 'wdApp.Visible = True'.
Annotation ii: The above lawmaking also provides for the filename to exist output to the page footer. This, of course, assumes the footer is suitably formatted. Simply uncomment the line concerned.
Note iii: The above code defaults to saving the output to the workbook's folder. Y'all can modify that by editing:
Code:
StrMMPath = ThisWorkbook.Path & "\"
For example, to salve the output to the user'southward 'Documents' folder, you could use:
Code:
StrMMPath = "C:\Users\" & Environ("Username") & "\Documents\" If you already have a mailmerge main document set up upwards with (or without) filtering, you could use the following Word macro to remember the SQL statement:
Lawmaking:
Sub GetSQL() With ActiveDocument.MailMerge If .MainDocumentType <> wdNotAMergeDocument So MsgBox "Post Merge Query String:" & vbCr & .DataSource.QueryString Else MsgBox "Not A Merge Document" Stop If Stop With End Sub
Split Merged Output to Separate Documents
Execute the merge, sending the output to a new document, then run the following macro over that document.
Code:
Sub SplitMergedDocument() ' Sourced from: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html Awarding.ScreenUpdating = False Dim i As Long, j Equally Long, k Every bit Long, StrTxt As String Dim Rng As Range, Doc As Document, HdFt As HeaderFooter Const StrNoChr As String = """*./\:?|" j = InputBox("How many Department breaks are there per record?", "Split By Sections", 1) With ActiveDocument ' Procedure each Section For i = 1 To .Sections.Count - i Pace j With .Sections(i) '***** ' Get the 1st paragraph'south text StrTxt = Split(.Range.Paragraphs(1).Range.Text, vbCr)(0) For k = 1 To Len(StrNoChr) StrTxt = Replace(StrTxt, Mid(StrNoChr, g, ane), "_") Next ' Construct the destination file path & name StrTxt = ActiveDocument.Path & "\" & StrTxt '***** ' Go the whole Section Set up Rng = .Range With Rng If j > 1 So .MoveEnd wdSection, j - 1 'Contract the range to exclude the Section break .MoveEnd wdCharacter, -i ' Copy the range .Re-create End With Cease With ' Create the output document Set Dr. = Documents.Add(Template:=ActiveDocument.AttachedTemplate.FullName, Visible:=Imitation) With Doc ' Paste contents into the output document, preserving the formatting .Range.PasteAndFormat (wdFormatOriginalFormatting) ' Delete trailing paragraph breaks & folio breaks at the end While .Characters.Final.Previous = vbCr Or .Characters.Concluding.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:=Simulated .Close SaveChanges:=False Finish With Side by side End With Gear up Rng = Zero: Set Doc = Nothing Application.ScreenUpdating = Truthful Cease Sub Illegal filename characters are replaced with underscores.
As coded, information technology is assumed the output filename consists of the first paragraph in each tape. If non, you could apply a dissimilar range or supersede all of the content between the ***** strings with code like
Lawmaking:
' Construct the destination file path & name StrTxt = ActiveDocument.Path & "\" & (i + j - ane) / j
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.
Code:
Sub FieldStringToCode() ' Sourced from: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html Dim RngFld As Range, RngTmp As Range, oFld Every bit Field Dim bFldCodes As Boolean, StrTmp As Cord, TrkStatus Every bit Boolean Const Msg1 = "Select the text to convert and endeavor once more." 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.Blazon <> wdSelectionNormal So MsgBox Msg1, vbExclamation + vbOKOnly, Title1 Get out Sub End If If InStr(1, Selection.Text, "{") = 0 Or _ InStr(1, Option.Text, "}") = 0 So MsgBox Msg2, vbCritical + vbOKOnly, Title1 End If If (Len(Replace(Pick.Text, "{", vbNullString)) <> _ Len(Replace(Selection.Text, "}", vbNullString))) Or _ (Len(Supervene upon(Choice.Text, "�", vbNullString)) <> _ Len(Supersede(Selection.Text, "�", vbNullString))) And so MsgBox Msg3, vbCritical + vbOKOnly, Title1 Get out Sub Finish If With ActiveDocument TrkStatus = .TrackRevisions .TrackRevisions = Imitation End With Awarding.ScreenUpdating = False ActiveDocument.ActiveWindow.View.ShowFieldCodes = Truthful Set RngFld = Choice.Range With RngFld .Text = Replace(Replace(Supervene upon(Replace(Supercede(.Text, vbCr, ""), Chr(eleven), ""), ChrW(&H2192), vbTab), ChrW(&H21B5), Chr(11)), Chr(182), vbCr) .End = .End + 1 With .Duplicate.Notice .ClearFormatting .Replacement.ClearFormatting .Format = False .Frontward = True .Text = "�" .Replacement.Text = "}" .Execute Replace:=wdReplaceAll .Forward = False .Wrap = wdFindStop .Text = "�" .Replacement.Text = "{MERGEFIELD " .Execute Supervene upon:=wdReplaceAll End With Exercise While InStr(i, .Text, "{") > 0 Prepare RngTmp = ActiveDocument.Range(Starting time:=.Start + _ InStr(.Text, "{") - 1, _ End:=.Start + InStr(.Text, "}")) With RngTmp Do While Len(Replace(.Text, "{", vbNullString)) <> _ Len(Supplant(.Text, "}", vbNullString)) .End = .Finish + 1 If .Characters.Last.Text <> "}" Then .MoveEndUntil cset:="}", _ Count:=Len(ActiveDocument.Range(.Finish, RngFld.Finish)) Loop .Characters.Start = vbNullString .Characters.Last = vbNullString StrTmp = .Text Gear up 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 Finish With Set RngTmp = Null: Set RngFld = Goose egg: Ready oFld = Zilch ActiveDocument.TrackRevisions = TrkStatus Application.ScreenUpdating = Truthful Cease Sub __________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Source: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
Posted by: woodsdoperat.blogspot.com
0 Response to "How To Merge Addresses From Excel To Word Labels"
Post a Comment