How to mail merge using a Word Template with rows from the data source acting as line items.

Adjacent rows in the spreadsheet with the same email address are used to produce a single PDF invoice with line items. This is an advanced example where the 'rows to email' method is 'One or more rows generates a email'.

Step 1. Create the data source

This can be a CSV file or Excel spreadsheet. It must have merge fields as column headings. Each row must have an email address. Create consecutive rows with the same email address for each line item that you want in the PDF.

In this example 4 emails will be sent each with a single attachment.

  • The first (to Mr Fred) has 5 line items.
  • The second (to Zac) has 4 line items.
  • The third (to Mrs Flinstone) has 2 line items.
  • The fourth (to Jack) has 1 line item.

Step 2. Create the Word template.

pdfMachine merge installs with ready made profiles to get you started.
This example is "Test Profile - Word invoice template using repeat_row filter"

Create your document using merge fields as place holders for the data. Surround all merge fields with {{ }}. The names of the merge fields must exactly correspond to the column names in the data source. The merge fields can be formatted using filters.

Step 3. Create the merge fields for the line items.

Line items are configured using either the 'repeat_row' or 'repeat_par' filter. The 'repeat_row' filter is used to display repeated rows of a table, 'repeat_par' is used to display repeated paragraphs.

To use the 'repeat_row' filter create a table for the repeated data. Insert headings if desired. You only need one row of data in the table. pdfMachine merge will create a row for each corresponding row in the data source during the merge operation.

In each cell of that row put the merge fields in {{}} along with any formatting filters. These merge fields must have a prefix of 'row.' added to the merge field to indicate the merge field is to change with each row of the data source. For example a merge field of 'description' must be referred to as 'row.description' in a repeat block.

Add the 'repeat_row' filter to the first merge field in the row/paragraph you want repeated.

To display any calculated field (eg total), you need to have a column in your data source which represents that merge field. Calculations can only be done in HTML templates using Javascript.

Step 4. Configure the profile.

Create a new profile from the profiles window. If you are unfamiliar with profiles then review the setup of a simple profile here.

Attach the data source you created in step 1.

Set the 'rows to emails method' to 'One or more rows generates an email'.

Configure the email and body settings. If you wish you can use the 'repeat_par' or 'repeat_row' filters in the email body to display line items.

Attach the Word template you created in step 2 as a dynamically generated template.

Step 5. Start sending emails

Do a test first, sending a few emails to yourself. When happy with the results, start sending emails.

The emails and attachments are all personalized the data from the data source.