Graham Mayor

... helping to ease the lives of Microsoft Word users.

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.

Avery™ Name Label & Postcard Duplex Mail Merge Add-in

Some time ago I prepared an add-in to aid the duplex (printing on both sides) mail merging of postcards. Postcard blanks based on a table with only one column are relatively simple to process. Things get a whole lot more complicated when merging label formats that have more than one column, so when a user reported that he wanted to mail merge double sided name labels, I decided to bite the bullet and produce a separate add-in dedicated to the task of producing double sided name labels. This then grew to encompass the postcard merge functions.

Fortunately all the Avery™ name label blanks have only two columns (and three or four rows), so the job was a little less complex than it might have been had I had to cater for variable columns.

I decided from the outset that as most people seem to use Excel as a data source, that I would base the process on Excel data.

For printers that can print on both sides of the paper, the add-in will create data sources with appropriately duplicated records to ensure the fronts and backs have data from matching records.

For printers that can only print on one side of the paper, the add-in can also produce a data source suitable for printing the backs of the labels created by using the original data source for the fronts.

The add-in provides three basic functions:

  1. Create a data source to ensure the data on the rear of the labels matches that on the font
  2. Label 1 Label 2
    Label 3 Label 4
    Label 5 Label 6

    On the reverse side the corresponding labels read right to left as follows:

    Label 2 Label 1
    Label 4 Label 3
    Label 6 Label 5
  3. Create a merge document with the required spacing for producing the labels from a selection of available name label formats.
  4. Provide a propagation function that ensures that the data on the fronts and backs of the labels is correctly reproduced in the appropriate cells, where duplex merge documents are created.

As with all add-ins available from this site it is supplied as a template for manual installation in the Word Startup folder, and also as a self extracting zip file for those who prefer a simple installation method. Either way Word must be closed during the installation (or restarted to load the add-in).

If you have not changed the preferred startup folder it can be located (in English language versions of Windows) by typing
%appdata%\Microsoft\Word\Startup
in the Windows Explorer Address bar and pressing Enter.

The add-in puts a button on the 'Mailings' tab of the Word ribbon.

Running the add-in process for the first time the user is presented with the usual disclaimer text:

Hiding the dialog accepts the disclaimer text and opens the main form:

The first step is to select one of the label types by its Avery™ pattern number.

Only the listed labels are compatible. I will add formats as time and inclination allow, but if you have a desperate requirement to use an unlisted label/postcard format then let me know and I will see if it is possible to include it.

Create Data File

Select the original data file and if the named file is a valid workbook, the worksheet combo-box is filled with the names of the worksheets from that workbook.

The process does not allow for merging only part of the data source, so if you need to merge a subset of the original data, create a worksheet containing only that subset BEFORE creating the duplex data file.

Check the check box 'Data for backs only' to create a simplex data file and merge document for the backs of the labels only. The dialogs will change to indicate the process currently available.

If the file path is especially long (longer than 127 characters) the add-in will be unable to add the modified data source to the Duplex Merge Document. The modified data file will still be usable, but you would have to add it manually to the document. The dialog warns of this fact, as does the creation of the merge document.

Click the Create Duplex Data File (or Create Simplex Data File) button and the process will create a new workbook in the same folder as the original. The process will not overwrite any existing file of the same name, and that name will reflect the layout of the label. The workbook will have a single worksheet, the name dependent on whether the process is for printing both front and backs or backs only. It should be pretty obvious from the names, what the data is for.

On completion of the process you will see a message similar to the following messages:

 

Create Duplex Label Merge Document

The process creates a two page merge document containing a table in which page 1 represents the front of the labels, and page 2 represents the backs of the labels. The process also adds the modified data source to the merge document, except where the path is longer than 127 characters as already discussed, and invites the user to enter the data for the fronts and the backs of the labels in the first cell of pages one and two respectively.

Note that the Duplex merge is a 'Letters' merge document and not the 'Labels' merge document you might expect. This is necessary to avoid accidental propagation from Word's built-in mail label mail merge function that would wreck the discrimination between front and back formatting,

The Simplex merge for the backs only is a 'Labels' type of merge and can be propagated either with Word's built-in mail merge 'Update Labels' function or the add-in's propagate function.

Insert the merge fields in the table cells as shown. With one page 'Simplex' documents fill only the first cell on the page. For 'Duplex' documents, fill the first cells on each page of the two pages and format to taste along with any fixed text you may wish to include.

If you require cell alignment different from the default then apply it to the table at this stage.

 

 

Propagate Duplex/Simplex Labels

This is the final step provided by the add-in and replaces the 'Label' merge propagate function with a function specifically targetted at this two page document. The function duplicates the page 1 labels and (where applicable) page 2 labels in their respective cells and adds NEXT RECORD fields as appropriate.

When complete the final message is shown:

Reset

At the bottom of the main dialog, there is a button marked 'Reset'

The data used by the add-in is stored in the add-in template in the form of docvariables. These variables are used to check the various processes. You may however wish to remove the stored variables from the template to start again from scratch. This button will do that. As it is completely destructive, and cannot be reversed, the function button is coloured red and comes with a confirmation message:

 

- Click here to download the add-in

 

 

 Headline 3

insert content here