How to mail merge and send emails with rows from the data source bundled as line items in the attached PDF

Use the "One or more rows generates an email" method to achieve this.

HTML templates are ideally used for this, as they can use Javascript or the Liquid Templating language to format the document.

There is partial support for Word, Excel and PowerPoint templates in the form of liquid filters.

Step 1. Create the template (layout).

Using HTML (or Word, PowerPoint, or Excel if necessary) create a template with the layout, formatting, text, and graphics of the document that is to be attached to an email.

In HTML use Javascript or the Liquid Template Language to configure the layout of the line items in the template.

Insert 'merge fields' as placeholders in the template where the personalized information for will be inserted.

2. Create a data source where "Rows form line items within a PDF". See how.

Create or download an Excel or CSV file that has the data that will change for each document.

Each column is a merge field. Multiple rows hold the information for a single attachment. Consecutive rows with the same email address will become line items in the email and attachment.

3. Create a profile in the pdfMachine merge software to link the template and the data source

Select "One or more rows generates an email" so that consecutive rows in the data source which have that email address are converted to line items.

4. Define the body of the email

Create a body with the layout, formatting and text that will be common to all emails. Insert merge fields as placeholders for the information that will be personal to each email. Configure the line items to repeat.

5. Choose which email method (Outlook or SMTP) will be used to send the attachments

6. Test the result

7. Send the documents

Watch the Video

Video : How to mail merge with a PDF attachment which has line items produced from rows in the data source

An email is sent with an invoice attached, the line items come from rows in the data source.

Video tutorial demonstrating how to use pdfMachine merge. A mail merge sends batches of emails with personalized PDF attachments. Each email has a PDF attachment which has line items built from rows in the data source. Emails can be sent using Outlook or SMTP.

One or more rows generates an email

(HTML with Javascript)

pdfMachine merge installs with ready made profiles to get you started.
This example is "Test Profile - HTML template with line items"

pdfMachine merge uses a HTML template and an Excel data source. Consecutive rows in the data source file that have the same email address are "bundled" together to form a single email with a single attachment. The rows become line items for either the body or the attachment. Javascript is used to iterate through the bundled rows, displaying them as line items within the PDF. An email with a single PDF attachment will be sent. Both the email and the PDF attachment will be personalized with information from the corresponding rows.

Javascript is a procedural language that supports such things as "for loops", "conditionals", and "math operations". More info here

Steps to create a merge profile

Template

Create the layout for your dynamically generated PDF attachment.

Step 1 : Create the template

Data Source

Create the data to personalise the emails and PDFs.


Step 2 : Create the data source

Merge Profile

Configure pdfMachine merge to create and send the emails, use the "One or more rows generates an email" method.

Step 2 : Create the profile

One or more rows generates an email

(HTML with Javascript)

pdfMachine merge installs with ready made profiles to get you started.
This example is "Test Profile - HTML template with line items"

Create a template using HTML

Surround the merge fields that are not to be used as line items 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 the Liquid Templating Language.

<tr>
   <td>Invoice Number</td>
   <td>{{invoice_id}}</td>
</tr>

Create the javascript to iterate through the bundled rows (as shown below).

Sample Javascript

Your HTML template should contain the following special tag:

var data = {{json_here}};

When processed by pdfMachine merge HMTL templates will have this substituted with a JSON (JavaScript Object Notation) string containing all the data for the bundled rows. This allows a you to write javascript that iterates through the data, for calculation or rendering. This allows a you to write javascript that iterates through the data, for calculation or rendering. The "json_here" tag will look like:
var data = [
{"email": "wayne@acme.com","customer": "Acme Corp","invoice_id":223,"invoice_date": "23/01/2011 12:00:00 AM","item": "blue widget","unit_cost":23,"quantity":4,"amt_due":391},
{"email": "wayne@acme.com","customer": "Acme Corp","invoice_id":223,"invoice_date": "23/01/2011 12:00:00 AM","item": "red widget","unit_cost":21,"quantity":2,"amt_due":391},
];

Javascript can then be used to iterate through the data array to create HTML to display the line items.

function generateLineItems()
{
   var html = "<table class='hor-minimalist-b'><thead><tr><th>Item</th><th>Quantity</th><th>Unit Cost</th><th>Price</th></tr></thead>\
   <tfoot><tr><td></td><td></td><td></td><td class='amountDue'>${{amt_due}}</td></tr></tfoot><tbody>";

   for (var i = 0; i < data.length; i++)
   {
      var ob = data[i];
      html += "<tr><td>" +
      ob.item + "</td><td>" +
      ob.quantity + "</td><td>" +
      ob.unit_cost + "</td><td>" +
      "$" + ob.quantity*ob.unit_cost + "</td></tr>";
   }
   html += "</tbody></table>";

   document.getElementById("lineItemsTable").innerHTML =html;
}
        

When using HTML templates, don't forget set your page setup in Internet Explorer for things such as Paper Size and Orientation, Headers, Footers, Margins, Background printing, and shrink to fit page. You should also set your Broadgun pdfMachine print preferences. This example should be set up as :

 

One or more rows generates an email

(HTML with Javascript)

pdfMachine merge installs with ready made profiles to get you started.
This example is "Test Profile - HTML template with line items"

Create a data source (this one is a CSV file) that has 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 3 emails will be sent each with a single attachment.

  • The first will have 5 line items
  • The second will have 4 line items
  • The third will have 1 line item

One or more rows generates an email

(HTML with Javascript)

pdfMachine merge installs with ready made profiles to get you started.
This example is "Test Profile - HTML template with line items"

Create a profile within pdfMachine merge. Merge fields can be used - surrounded by {{ }}.

The Rows To emails method is set at "One or more rows generates an email".

. . .