Automation of Mail Merge using PDF4me

Mail merge is a method of reading data from a data source and replacing corresponding variables in another document. It usually requires two files, one for storing the merge fields - the variable for the data to be inserted. The other containing both the instructions for formatting the variable data and the information that will be identical across each result of the mail merge.

E.g, data is read from a database, JSON, CSV, Excel spreadsheets, google sheets, or other forms of structured data and inserted into an existing document template for e-mails, Invoices, or any other that requires dynamic data to be populated. The templates can be often a Word document, PDF, emails, form letter, etc.

Let’s consider a scenario where you want to send email with dynamically populated data for a recipient list. In the data file, you might include instructions to insert the name of each recipient in a certain place; the mail merge would combine this email with a list of recipients to produce one mail for each person in the list.

The mail merge feature is also used very often to print documents with dynamic data. Say for printing invoices, you would construct a source document containing the addresses of the people you wish to print Invoices for and the main document that controls where each person's name, address, city, state, and zip code will go on the Invoice.

The main document would also contain information about how many invoices are on each page, the size of each invoice, the size of the paper, and the type of printer you will use to print the Invoices. Running a mail merge with the two files results in a set of Invoices, one for each entry in the source document, with each invoice formatted according to the information in the main document.

PDF4me has developed powerful connectors designed to provide mail merge capabilities with different forms of data sources. PDF4me Mail merge lets you create a batch of documents that are personalized for each recipient. Using the template file as PDF or Word Doc, PDF4me accepts data from a file or list of files to populate dynamic data for the mail merge process.

Mail Merge with Zapier and PDF4me

In Zapier mail merge feature has been implemented in four ways.

• Mail Merge with Data File (List of Docs): Uses a word or PDF as a template and merges with data contained in a file in excel or JSON format and returns multiple files in a zip
• Mail Merge with Data File (Single Doc): Similar to the above action but returns only a single file in the same format as the template file
• Mail Merge with Data String (List of Docs): Uses a word or PDF as a template and merges with data as text in JSON format and returns multiple files in a zip
• Mail Merge with Data String (Single Doc): Similar to the above action but returns only a single file in the same format as the template file

In case of returning multiple files, also there is an option to merge output files if the template file is PDF. This is will give a single PDF as an output file.

The template type currently supported in Word and PDF. The data file used for merging with a template can be provided only in excel or JSON format file. For excel it can be in the following format.

For creating JSON structure it could be in this format

Data Array

[ ["FieldName1", "FieldName2", "FieldName3"], ["FieldValue1", "FieldValue2", "FieldValue3"], ["FieldValue4", "FieldValue5", "FieldValue6"] ]

or

Data List

{ "DataList":[ { "FieldName1": "FieldValue1", "FieldName2": "FieldValue2", "FieldName3": "FieldValue3" }, { "FieldName1": "FieldValue4", "FieldName2": "FieldValue5", "FieldName3": "FieldValue6" } ] }

Word template of Mail merge should be created using the mail merge feature of Microsoft Word. For creating the structure of JSON, you could use Code by Zapier feature to construct JSON text. Refer screenshot below

Mail Merge features with Power Automate

PDF4me power automate connector has an important feature for the users of small to large organizations who deal with everyday dynamic data and prepare documents with those dynamic data for their business process. Mail merge is now elevated to automate your day-to-day document generations along with your dynamic data items.

For example, when a new customer data has added to a SharePoint list item, there is a trigger to Power Automate from SharePoint to link with PDF4me Connector to generate documents by apply Mail Merge features.

PDF4me Mail Merge Actions

PDF4me Connect has four different combinations of mail merge actions to make it simple to the users. As we discussed earlier, data is the key player to decide which action is a better option for your need. PDF4me enables wide options to bring data from any source like OData, SharePoint, Spreadsheet, Json or Structured string, etc.

• Mail Merge with Data File (List of Docs)

This action produces multiple documents as output and accepts data as a file. It could support .xlsx and .json file formats.

• Mail Merge with Data File (Single Doc)

This action produces a single document as output and accepts data as a file. It could support .xlsx and .json file formats.

• Mail Merge with Data String (List of Docs)

This action produces multiple documents as output and accepts data as a string. This is an open option to users who can bring data from any source and prepare JSON structure as given below in the samples section.

• Mail Merge with Data String (Single Doc)

This action produces a single document as output and accepts data as a string. This is an open option to users who can bring data from any source and prepare JSON structure as given below in the samples section.

Sample Data Structure

Data Array

[[“FieldName1”, “FieldName2”, “FieldName3”], [ “FieldValue1”, “FieldValue2”, “FieldValue3”], [“FieldValue4”, “FieldValue5”, “FieldValue6”]]

or

Data List

{“DataList”:[{“FieldName1”:“FieldValue1”, “FieldName2”: “FieldValue2”, “FieldName3”: “FieldValue3”}, {“FieldName1”:“ FieldValue4”, “FieldName2”: “FieldValue5”, “FieldName3”: “FieldValue6”}]}

Sample Work Flow with Mail Merge with Data String (List of Docs)

In this sample workflow, we get data from SharePoint List items. Added trigger to get list items when an item is added to your specified SharePoint data list item. Then get your document template to apply mail merge. Then initialize the variable to prepare data as proper JSON structure with String functions. Finally, map the fields with PDF4me connect to perform the Mail Merge and produce documents as you wish.

Finally, you can add your business apps to take forward the produced documents according to your business process life cycle.