This is the list of data to be merged into the destination template.

It should be on a separate worksheet by itself.

It must be in the format of field names in row 1 with the data itself in any rows below that without gaps.

The columns can be in any order. Blank columns can be left.

Hidden (or filtered as hidden) rows will be ignored.

The field names must NOT have the brackets around them on this sheet ie for a merge field called "<<h.Surname>>" in the destination template, the header on this sheet must be "h.Surname" (no quotes). This must be an exact match and it is case sensitive.

Note.

• If there is data on this sheet that is not required on the destination template then it is ignored.
• Hidden rows will be ignored - useful if you wish to use Excel's Autofilter to filter out certain rows.

The field names (with the exceptions of the special cases listed below) must begin with either "h." or "b." (no quotes) depending on whether they will be treated as header merge fields or body merge fields.

The difference between header and body fields is best seen by looking at the examples provided. Each merge will consist of header merge fields which will be merged just once for each group of merge rows (note - despite their name, they can be anywhere in the destination template and do not have to be at the top), and body fields which must be in a separate row on the destination 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 from the left ie in or nearest to column A.

Special Merge Field Names

1. For merging into one new Excel workbook with a separate worksheet for each merge you may use the following header on row 1 of your source data:

TabName - this is optional, but if present will be used (if possible*) to name the worksheet for that merge

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

2. For merging into different Destination Template worksheets for each line of data:

DstWorksheet - this must be the name (case sensitive) of a destination worksheet in the same workbook.

3. To insert an image (jpg, gif etc) into the destination worksheet you may use the following header on row 1 of your source data:

• InsertImage - the data must have the full path and filename; images will be inserted into Cell A1 by default, but you can specify a different cell location (format D2 for example) in the column immediately to the right - this column containing the cell location does not require a header.

4. When printing, you may select a different number of printouts for each merge, using the header on row 1 called "PrintCopies".

5. Use the header SaveFileName to specify a full path and file name for each merge. Please note:

• This must be a complete path and filename eg C:\My Merges\abc.xls
• Any existing files will be overwritten without warning
• You should use a drive letter rather than server name path ie c:\etc rather than \\server1\etc
• If you do not provide a proper file name then the merge will fail
• You can use a file name for some rows and leave it blank for others, in which case the blank ones will revert to the filename given in the Merge window
• You can produce 'dynamic' filenames using a formula such as ="C:\test\merge" & A2 & ".xls" (see more here)

6. For merging into emails there are special field names available. See the section below on merging to emails for details of these.

top

This is the template for the form, letter etc which is to receive the source data.

The worksheet can contain graphics, formulae, etc - in fact anything that a normal worksheet can contain.

The merge fields must be in the format <<h.abc>> where h.abc is the merge data field name.

All merge fields must be either header or body fields (except for the special cases mentioned above), identified as such by starting with either "h." or "b." (no quotes, case-sensitive).

All body merge fields must be on one row so that new rows can be inserted for each merge of body field data. (If this is not clear, look at the examples provided.)

The merge fields can be contained within other text in a cell, be by themselves in cells, or be contained in a formula in a cell. Because the software always works in automatic recalculation mode 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 merged cells.

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 in doubt, try it! - best with a single merge, using Print Preview.

Important Note: When you prepare your destination template you should ensure 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.

top

The results of the mail merge can be output to the following.

1) Print Preview is probably most useful for testing a few samples. If you accidentally send a large amount you will have a chance to cancel after each set of 5 outputs.

2) Printer sends each output to your 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 option on the screen.

3) File will save the merges results into

• text file(s). Unformatted text files do not look smart and lose any graphics etc, but they have the advantage of being small, being readable by any PC and easily imported to most software packages. You can save either
  • one text file with page breaks between each (- this can be opened in Microsoft Word or other software as required), or
  • individual text files for each merge. These will use the file name you specify but with a numeric suffix eg C:\My Documents\Results.txt will give you files C:\My Documents\Results00001.txt, C:\My Documents\Results00002.txt, C:\My Documents\Results00003.txt, etc. NB If any of these files already exist they will be overwritten without any warning.
• Excel workbooks - each one with just one worksheet with your merged data. These will use the file name you specify but with a numeric suffix eg C:\My Documents\Results.xls will give you files C:\My Documents\Results00001.xls, C:\My Documents\Results00002.xls, C:\My Documents\Results00003.xls, etc. NB If any of these files already exist they will be overwritten without any warning.
• PDF file - this option is not available pre Excel 2007. It requires a Microsoft add-in available here:
  Download 2007 Microsoft Office Add-in: Microsoft Save as PDF
  which you will need to download and install.

4) Email will work with most MAPI email systems (eg Microsoft Outlook, Outlook Express). If in doubt, try it with just one or two merges, but be sure to save all your data first since the results may not be predictable and occasionally may even crash your PC (though no permanent damage should be done).

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 three forms of emails.

• Emails with a text file attachment of the merge results. Unformatted text files do not look smart and lose any graphics etc, but they have the advantage of being small, being readable by any PC and easily imported to most software packages. (This software is not primarily designed for producing smart letters - Microsoft Word is more appropriate for this.)
• Emails with an Excel workbook file attachment of the merge results. Each workbook will contain just one worksheet with your merged data.
• Emails with the merge results in the body of the text and no attachment. The disadvantage of this option is that each one is opened as its own window requiring you to click Send (or not as you wish). Because of this it is recommended that this option is used for only a few merges at a time. However, if you use Microsoft Outlook you can send the emails direct to the Outbox.

In order for the software to know the detail for the emails you must use the following headers on row 1 of your source data:

• EmailTo - this is a required field.
• EmailSubject - optional. You may wish each email to have personalised subject data. If this is missing or blank then the attachment filename is used, or the first 12 characters of the body text if the third option is used.
• EmailFilename - optional. It will be used for the first two options and ignored by the third. This will be the main part of the file attachment name (eg if you enter File1 under this header, then the email will have the attachment File1.txt or File1.xls). If this is missing or blank then the attachment file name will be Read_Me.txt or XLFile.xls.
  Tip. To use the email addressee as the file name use this formula =LEFT(A2,FIND("@",A2)-1) for the file name, assuming the EmailTo data is in column A. Just change A2 for C2 if this data is in column C, etc

The following are all optional and apply only to users of Microsoft Outlook:

• EmailBody - to be used as the body of the email if merging to an attached file; if merging to the body of the email this text will be inserted in front with a blank line between.
• EmailCC - whoever you want copied on the email.
• EmailBCC - whoever you want blind copied on the email.
• EmailAttachment - if you are merging to the email body then you can attach a file by entering the path and file name here, or multiple files by separating each with a semicolon.
• EmailReadReceipt - enter True or False if you require a read receipt or not.
• EmailImportance - enter 0 for Low, 1 for Normal, or 2 for High.
• EmailSensitivity - enter 0 for Normal, 1 for Personal, 2 for Private, or 3 for Confidential.
• EmailSignatureStart - enter a path and file name for a Signature file (can be a flat text file from Notepad, or an HTML or RTF file) to insert into the email body before EmailBody.
• EmailSignatureEnd - enter a path and file name for a Signature file (can be a flat text file from Notepad, or an HTML or RTF file) to add to the email body after EmailBody.
  [Tip: If you save signatures in Outlook you should find them in either
  C:\Documents and Settings\[User]\Application Data\Microsoft\Signatures or
  C:\Users\[Name]\AppData\Roaming\Microsoft\Signatures for Vista]
• EmailAccount - enter the email address to send from if you have several accounts (applies to Outlook 2007 only - earlier versions will ignore this header)
• EmailFrom - enter the email address to send from if you want to send on behalf of another email address (this is the same as entering an alternative into the 'From' field when creating an email
• EmailAttZipName - email zip files containing your attachments will be given this filename (over-riding the default name of Attached.zip)

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 using Windows 2000 or later (no other zip software is required).

5) Separate sheets in 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.

top

This file contains no macros, passwords or other protection.

Instructions:

• Simply open this file and then run PATools Advanced Mail Merge. It can be opened by clicking on the link.

You can try it with different options.

See how the formulae 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 destination template.

Use this file (or make your own) to try out the tools. If you corrupt it you can always reinstall it or download a new copy from the PATools website.

top

Run Options

For each merge there is a checkbox called "Remove formulas from outputs". This may be useful when you create Excel file results and do not wish your result to include formulae but simply to show the values.

By clicking on 'More run options'

• you can also select protection options (including passwords) for your resulting workbook
• you can select for worksheets to be sorted by name in the final workbook (when you are merging into multiple sheets in one workbook)

Options

In addition, you can select 'Options' for the general use of the software.

1) Use the following cell content (default value is Text) may help when you do not get the exact merge results you need. The difference between the three is best explained by a cell, formatted to 2 decimal places, that contains the formula "=SUM(A1:A2)" will show the value as 26 and the text as 26.00.

In order to find what works best for you we recommend good old trial and error!

2) Users of Microsoft Outlook can choose the format of emails to be sent as either Plain Text or HTML. The default is Plain Text, and this setting is ignored if you do not use Microsoft Outlook.

3) Automatically select last used row (default value is ticked) lets you specify whether the default value of 'To Row' automatically selects the last row used on the source worksheet.

4) Save copy of workbook before each merge: By default, before you run a merge, the software automatically saves a backup copy of your workbook in the folder where you installed the software; the file is called LastMergeFile.xls. The advantage of this is that in the unlikely event that your workbook gets corrupted during the merge, and you did not previously save a copy, you have this backup copy to retrieve. The disadvantage is that there is a slight delay at the start of each run whilst Excel saves this backup file.

5) When zipping attachments for Outlook you can use either Windows zip (no additional software required), or Winzip or the free 7-Zip. We recommend you only use Winzip if it is licensed otherwise you will have to maximise the Evaluation window and click the button for each and every zip. You can also use the Winzip Command Line AddOn which is faster (requires a licensed copy of Winzip but this is then free to use). Windows zipping is free and requires no additional software but has the disadvantage of being slow (due to running in  a separate process - we cannot work around that); Winzip and the AddOn are fine (see http://winzip.com) but you need a licensed copy which is not free; 7-Zip is fast and free for private or commercial use (see http://7-zip.org). You select your preferred option by clicking on the button and it will then validate the location of the installed zip program - if you chose a different location from the default you will be prompted to locate it.

top