Variable Document Merge

Many people access the material from this web site daily. Most just take what they want and run. That's OK, provided they are not selling on the material as their own; however if your productivity gains from the material you have used, a donation from the money you have saved would help to ensure the continued availability of this resource. Click the appropriate button above to access PayPal.

Merge Excel data to assorted merge documents

This process enables an Excel data source which contains a list of records each of which may to be merged to a different merge document identified in a field of that record. There is no limit to the number of merge documents which may be used. The only stipulation is that each document used contains fields from the same Excel data source used to identify the templates.

The process can also emulate a conventional merge from Excel using a single common merge document selected from the add-in's dialog.

The add-in puts a button group on the Mailings tab of the Word ribbon:

Activating the button for the first time displays the usual disclaimer followed by a configuration dialog so that the user may confirm that PDF Creator is present to allow the selection of protected PDF files.

The add-in is tested with PDF Creator 2.5.2 which has less bloat than later versions. Ensure that if you install this product, you do not accept the offer to update to the current version and after installation, its automatic update option should be deselected. Additional programs included with the file are not required.

The selection made above will be retained until the add-in is reset from the add-in's ribbon group, or an updated version is installed.

The main dialog is then presented

If the 'Use the same merge document template for all records' option is checked, the dialog changes to require the selection of the merge document to use. That document must contain merge fields from the chosen Excel data source.

Note that documents used in conjunction with the process must have merge fields from the chosen data source worksheet and the merge document type should be set as 'Normal Word Document', from the 'Mailings' ribbon tab (see next illustration).

Complete the various fields

The 'Letters template path' field data containing the merge document to be used for a particular record may contain:

1.) The name of the merge document e.g. Filename.
or
2.) The name of the merge document and its extension e.g. 'Filename.docx'.
or
3.) The full path and filename of the merge document (with or without its extension) e.g. 'C:\Path\filename.docx' or 'C:\Path\filename'.

Whichever option is used, it must be the same option for all the records and for each record there must be an associated value.

Mandatory fields are coloured pink and optional fields are coloured cream, when they have no value selected or entered. When completed they are coloured green. The process will not permit completion if there are pink fields displayed

Based on the content of the 'Letters template path' field, the process determines from the first record whether there is a path and filename extension and where there is a path included, the path selector is omitted.

Note the additional tab (arrowed above) which displays the following dialog (shown here with the 'Merge to PDF format' option selected).

See the context sensitive help for more information on the use of a macro.

Merge to PDF Option

If the 'Merge to PDF format' option is selected the 'Secure PDF format' option becomes available (assuming PDF Creator is installed and confirmed in the configuration).

This displays the following dialog to the right of the main dialog

Entering a Master password, allows security settings to be established, this includes the option to include a common user password (required to open the PDF) or individual passwords derived from a field in the data source.

The label indicated shows as a reminder whether or not a user password is applied.

Send via E-mail

The add-in also provides the option to send the resulting documents by e-mail to an e-mail address in the data source.

The add-in provides for one selected field to be used in the message body text to personalise the covering message.

To utilise the field in the message text enter the following tag '[SALUTATION]' (without the quotes) at the appropriate place in the message text as shown above, and the tag will be replaced during the merge with the value from the field.

The '[SALUTATION]' tag is not case sensitive.

e.g. 'Dear [SALUTATION]' would be reproduced in the merge with (say) 'Dear John', where 'John' is the value from the chosen field.

Additional message options

The additional message options tab provides some further e-mail options:

Clicking the indicated check box provides further options to add attachments either identified by their full paths in the data source or listed in the text box, each on a separate line. In the case of the latter. The listed attachments are applied to all the messages.

If the 'Include default Signature' check box is checked, the signature associated with the sending account is added to the covering message.

The output would look like the following, with the merged document and the listed attachment attached.

All preferences, as far as practicable, are retained, but may be cleared by clicking the red button:

The process converts the merge fields in the various documents to content controls which it then populates with the appropriate data. It makes no changes to either the merge documents used as templates or to the data sheet

Where there is no data available for a particular field used in one of the templates, the content control may optionally be filled with a zero length space, which renders it invisible - otherwise the content control placeholder text is displayed.

- Click here to download the add-in