since 1999
To get started quickly, watch the video above, study the demo workbook examples, and use this documentation.
This is the easiest way to see how the 3 merge types work.
Tip: Use your browser's 'Find' (often Ctrl+F) to search this page.
On this page:
This is the list of data to be merged into the output template, and can be text or formulas.
It should be on a separate worksheet from the output template.
The columns can be in any order. You can have blank columns or columns that are not used for merging.
For Mail Merge, it must be in the format of field names in row 1 with the data itself in rows below that (without gaps). Optionally the data headers may reside in the first row of a block of data or any row you select, so this enables you to run different merges from the same set of source data, or allows you to arrange your source data the way you require.
For Label Merge no headers are needed.
Hidden rows, or rows hidden by auto-filtering, will be ignored. For Mail Merge there is an option to mark rows to exclude with a formula or text in Column A - any rows marked "X" will be ignored.
The basic rule is that the row 1 field names will be replicated in the output template, but surrounded by << >> to identify them eg Source data cell A1 might contain Name and the corresponding merge field in the output template will be <<Name>>.
For certain merge types (known as B and C) there are special rules as follows (with the exceptions of the reserved field names listed below):
B. Header and Body rows
The field names must begin with either "h." or "b." (no quotes) depending on whether they will be treated as header or body merge fields.
The difference between header and body fields is best seen by looking at the demo workbook examples. Each merge will consist of header merge fields which will be merged just once for each group of merge rows (note - they can be anywhere in the output template and do not have to be at the top), and body fields which must be in a separate row on the output template and will each be merged to form a list.
A new merge is started each time the data changes in the first header column in or nearest to column A (though a blank cell is assumed to be the same as the last header data above).
C. Multiple rows per merge
To distinguish this type of merge, the merge field names must be denoted as m. So, for a merge field called 'm.Name' in the source data, the merge fields in the output template must be in the format <<m.Name01>>, <<m.Name02>> etc up to a maximum of 99 ie <<Name99>>. Make sure you use the digit zero and not the letter O, and not <<Name1>> but <<m.Name01>>.
There are a few reserved field names (all optional), some listed below for merging into emails, and the following more general names. For merge type B, the data will be identified on the first row encountered for each new merge.
PrintCopies - when printing, you may select a different number of printouts for each merge. Blank cells will use the number selected within the software.
UsePrinter - to specify a different printer for each merge. Tip - to get your printer name easily and in the right format, select your printer in the Output to section of our software and then click the name to put it on the clipboard; then paste it into your source data. Blank cells will use the printer selected in the software
TabName - to specify a name for an output worksheet. It may not be possible depending on the length of the text, whether it includes disallowed characters, and whether it is a duplicate of another worksheet. Blank cells may be left.
DstWorksheet - this must be the name (case sensitive) of an output worksheet in the same workbook, to allow different rows to be merged into different templates. Blank cells will cause a failure. NB You must select '* As per Source Data *' in the template dropdown.
SaveFileName - to specify a full path and file name for each merge. Blank cells may be left. Please note:
SaveFilePwd - to specify a password required to open an Excel output file.
InsertImage - the data must have the full path and filename for an image file (jpg, gif etc); images will be inserted into Cell A1 by default, but you can specify a different cell location (eg D2) in the column immediately to the right - this column containing the cell location does not require a header. Blank cells may be left.
MergeNo - this is not strictly a reserved name, but is mentioned here and below for completeness. If you wish each merge to contain a sequential number from 1 for each merge this can be included in any cell in the Output Template as <MergeNo> (note single < and > - this is different from the main merge fields which require << and >>).
This is the template for the form, letter etc which is to receive the source data. At first the software lists all the (non-hidden) worksheets in the same workbook as the source data, defaulting to the adjacent sheet. There is a checkbox to list worksheets from all open workbooks, so your template may reside in a different workbook.
The worksheet can contain graphics, formulae, etc - in fact anything that a normal worksheet can contain.
Alternatively if you select the last item in the drop-down (* Word document * >>>) then you can choose either an open or saved Word document.
For Mail Merge the merge fields must be in the format <<Name>> where Name is the merge data field name. For Label Merge they must be <>.
For merging into a Word document, please note that you create merge fields within the text as above (eg <<Name>>) - you do not create Word's merge fields which are for use only with the basic Word mail merge facility.
For Mail Merge type B, there are 2 types of merge fields - Header (h.) and Body (b.). The difference between header and body fields is best seen by looking at the demo workbook examples. Each merge will consist of header merge fields which will be merged just once for each group of merge rows (note - they can be anywhere in the output template and do not have to be at the top), and body fields which must be in a separate row on the output template and will each be merged to form a list. When merging to Word, you can select how the b. fields are joined in the Merge Options eg joined by a new line character, or perhaps a comma and a space - you choose.
The merge fields can be the sole text in a cell, can be contained within other text, or can be contained within a formula. The formulae will be computed afresh before each merged document is produced - this is a particular strength of using this tool within Excel for mail-merging.
Tip. To concatenate (put together) some text with a calculated formula in a single cell in Excel you can use a formula like this:
="Machine <<h.Machine1>> gave a reading of "&12.2*D1&" mm."
ie Use the equals sign to designate a formula, the text (including the merge field) goes inside double quotes, and the formula part is adjoined to text using the ampersand character.
A cell may contain one or more merge fields.
Merge fields can be contained within cells that are merged together.
Merge fields can be anywhere on a sheet, can be contained more than once, and they do not need to be in any order.
If you wish each merge to contain a sequential number from 1 for each merge this can be included in any cell as <MergeNo> (note single < and > - this is different from the main merge fields which require << and >>).
Tip: When you prepare your output template make sure that any cells containing merge fields have sufficient space to allow the merge field(s) to be replaced with the longest piece of source data without having overlap problems or text disappearing out of the print area.
If in doubt, try it! - best with a single merge, using Print Preview.
The results of the mail merge can be output in various ways. Please note that you may also use some optional Reserved Field Names to override some settings.
1) Print Preview is probably most useful for testing a few samples. And don't worry - if you accidentally send a large number of merges you will have a chance to cancel after each set of 5 outputs.
2) Printer sends each output to your selected printer.
Note: In some cases the software may send output faster than your printer can print - in this case it will usually buffer the output, but in some cases the buffer may fill up. If this happens you may need to run the merge in batches. Hence the software always leaves the active cell on the next row to be run. To assist with this you may find it useful to put a few seconds delay between each merge using the merge option.
3) Email will work with most MAPI email systems. If in doubt, try it with just one or two merges, but be sure to save all your data first.
PATools recommends that you have your email software open when running this option, though you may not wish to have an open connection for testing purposes so you can see the email results in your Outbox but not necessarily send them.
You can produce two forms of emails.
In order for the software to know the detail for the emails you must use the following headers (reserved field names for emails) on row 1 of your source data:
The following are all optional reserved field names for emails and apply only to users of Microsoft Outlook:
There is an option to allow the software to print merges where the email address is blank.
In addition there is an option to zip up attachments, and you can enter a location for copies of attachments to be saved.
4) Files will save the merges results into
5) New workbook will create the merged documents in one new Excel workbook with a separate worksheet for each merge and leave this workbook open. Note: The software does not save this file so you may wish to do that manually at the end, and there are (varying) limits on how many worksheets a workbook can contain so proceed with large merges with care!
6) New Word doc will create the merged documents in one new Word document with each merge on a new page. Note: The software does not save this file so you may wish to do that manually at the end.
7) The template will merge into the actual template itself. Obviously this is dangerous because it is likely to corrupt the template and prevent it working in the future ie you will be overwriting the merge fields with data. There is no undo. Note: The merge does not save the template, so you may wish to use it and either discard at the end, or save as a new file.
This Excel workbook contains no macros, passwords or other protection.
The demo workbook is supplied with the software and can be opened in read-only mode by clicking on the link on the software.
You can then see how it works and experiment with different options. Many users find it easier to adapt one of our examples to create their own more quickly.
In the demo you will see how the formulae show errors but then compute to give the correct data once a merge is completed. As an alternative you could of course have these formulae computed in the source data and then merge the computed results into the output template.
For most options you can right-click to set default values. For some options the software remembers your last setting where this may be useful.
1) General
2) Source data
3) Outputs
4) Excel workbook outputs
These options only apply where the output results in an Excel workbook.
5) Merge to Word
These options only apply where the output results in a Word document.
For Merge Type B you can select how the b. merge field data are joined. In an Excel-only merge each b. field appears on a new row; however in Word, you may wish to do this using the codes explained within the software (eg <NL> for New Line) or you may wish to join the b. data using perhaps a comma and space.
Users of Microsoft Outlook can choose the format of emails to be sent as either HTML, Plain Text or Rich text. The default is HTML. Use simple html is a further option which may be helpful and slightly quicker in producing html emails - in this instance PATools generates basic html coding of your output whereas otherwise Excel produces the html. These options are ignored if you do not use Microsoft Outlook.