PATools

Help for PATools Merge Suite

We strongly recommend you read this help file in conjunction with studying and trying the demo workbook examples.
This is the easiest way to see how the different merge types work.

Tip: Use your browser's 'Find' (often Ctrl+F) to search within this page.


 

1. The Source Data

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 for R1.23 onwards the data headers may reside in the first row of a block of data or any row you select- you select this under Merge Options > Source data; for example 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 merge types B & 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

For a merge field called 'Name' in the source data, the merge fields in the output template must be in the format <<Name01>>, <<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 <<Name01>>.

Reserved Field Names

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.

SaveFileName - to specify a full path and file name for each merge. Blank cells may be left. 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 you selected in the software
  • You can produce 'dynamic' filenames using a formula such as ="C:\test\merge" & A2 & ".xls"

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

 

top

2. The Output Template

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.

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.

 

top

3.Output to

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.

  1. Emails with an attachment of the merge results, in various formats (see the next section on Files for more information on the types of attachments).
  2. Emails with the merge results in the body of the email. 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, and if you disconnect first then you can review/edit/delete before reconnecting and sending.

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:

  • 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 there is no attachment.
  • EmailFilename - optional. 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 etc). If this is missing or blank then the attachment file name will be Read_Me.txt etc or XLFile.xls / XLFile.xlsx.
    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 reserved field names for emails 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 (format x@y.com) to send from if you have several accounts (applies to Outlook 2007 or later 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), and you can enter a location for copies of attachments to be saved.

4) Files will save the merges results into

  • 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, etc. NB If any of these files already exist they will be overwritten without any warning.
  • PDF files - this option is not available pre Office 2007. For Office 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.
    Note - if pdf file creation fails, an mht file will be created in its place.
  • HTML files
  • MHT files (single file web pages) - a single file that includes any graphics, though you should note that not all web browsers will necessarily support mht format
  • Word documents (naming as for Excel workbooks above)
  • text file(s). Unformatted text files do not look smart and lose any graphics etc, but they have the advantage of being small, able to be read 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, etc. NB If any of these files already exist they will be overwritten without any warning.

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.

 

top

4. Demo workbook

Mail Merge download | Label Merge download

Theses Excel workbooks contains no macros, passwords or other protection.

Instructions:

  • Simply download and open the relevant workbook and then run the software. Alternatively they can be opened in read-only mode by clicking on the link on the software.

You can then see how they work and experiment with different options.

Use and adapt these workbooks, or make your own, to try out the software. If you corrupt the workbooks you can always download a new copy from our website.

Tips for the Mail Merge demo:

  1. you need to make sure that you select the correct merge type for each example. See the text box on each data page.
  2. 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.

 

top

5. Merge Options

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

  1. You can add a delay between merges of 0-20 half-seconds. This may help if the printer buffer fills too quickly for some older printers.
  2. Run with Autocalculation will run merges more slowly, but may assist if you require calculations mid-merge to decide on later merge values. By default the software runs in Manual Calculation mode, only recalculating just prior to output after all merging has been completed.

2) Source data

  1. You can select for column A in your source data to be a marker for rows to ignore when merging. You may wish to create a formula (perhaps referencing other source data in that or another row) that results in an X (letter X) for any rows not to be merged. Anything apart from X (or lower case x) will be merged.
  2. Normally the source data headers must be in row 1 of the source data sheet. There is an option to use the first row of a block of data instead.

3) Outputs

  1. You can opt for the first row or column (or both) of your output template to be treated as a marker for rows/columns to be deleted when the merge is complete but before output. You may wish to create a formula that results in an X (letter X) for any rows/columns to be deleted. Anything apart from X (or lower case x) will be left. Finally the row 1 and/or column A will also be deleted from the output.
  2. You can opt for the cell formatting of the source data to be carried over into the output cell for each merge replacement. Note that if there are two merge fields in a cell then the latter format will prevail.

4) Excel workbook outputs

These options only apply where the output results in an Excel workbook.

  1. You can opt for all formulas to be replaced with the resulting values in your output.
  2. For Excel 2007 and later the software will normally create an xlsx workbook when saving the output. You can force it to use xls format.
  3. You can opt for worksheets to be sorted by name in the final workbook when you are merging into multiple sheets in one workbook.
  4. You can select protection options (including passwords) - all self-explanatory

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.

 

top

6. Software Options

If you change any settings here don't forget to click on 'Save these settings'.

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

3) In the Merge setup tab the software attempts to find the correct 'row to' value, such as the last row used on the source worksheet or the last row in a set of data before a break. If you find this unhelpful you can turn it off here.

4) Each time you open the software it will automatically select the last merge type you had selected. This can be useful if you only ever run one type of merge, but can be dangerous if you often run different types of merge. Turn it off here if you wish, so you will never attempt to run the wrong type of merge by accident and will always have to specify which type you are running.

5) When zipping attachments for Microsoft 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 attempt to validate the location of the installed zip program - you may need to locate where the program is installed.


Top of page

International Copyright © 2014-2016 Smada Ltd - PATools.

PATools is a trading style of Smada Ltd, Registered in England and Wales No 9122922. All rights reserved.

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.