Populating a PDF Form with Data From a Spreadsheet

AutoMailMerge plug-in for Adobe® Acrobat®

What is a Mail Merge?

A mail merge is a computer term describing the production of multiple (and potentially large numbers of) documents, from a single template form and a structured data source. This technique is used to create personalized letters, documents, bills and pre-addressed envelopes or mailing labels for mass mailings (or document creation).

The steps below outline a basic mail merge procedure - for help with more advanced operations, see this list of mail merge tutorials.

Introduction

In this tutorial, we use the AutoMailMerge™ plug-in to populate a sample PDF form with data from an Excel spreadsheet. The form contains multiple text fields, as well as one yes/no checkbox option. We will look at all available settings pages used to configure the merge. The goal is to create individual filled forms for each record in the spreadsheet data source. The spreadsheet used here contains weather data for multiple sport venues, as well as a data value (Yes/No) that will be used to mark one of two checkboxes.

See this separate tutorial for help with creating PDF forms.

The checkboxes operate as radio-buttons. Depending on the data values relating to checkboxes ("Yes” or “No”), only one of them will be selected (or none at all if there is no value). See the separate checkboxes in PDF forms tutorial to configure checkboxes correctly (see the "Options" tab under "Check Box Properties" to set the export value).

The image below shows the desired outcome - different filled forms making up a unique sample weather report for each location.

Prerequisites

You need a copy of Adobe® Acrobat® along with the AutoMailMerge™ plug-in installed on your computer in order to use this tutorial. Both are available as trial versions.

Step-by-Step Tutorial

Step 1 - Open the "Mail Merge" Dialog

First, open the PDF form that you want to fill in Acrobat.

Select "Plug-Ins > Mail Merge…" to open the "Mail Merge" dialog.

If you are using a new Adobe Acrobat interface, then you can locate Mail Merge menu here:

Please see the following page for video how to locate Plugins menu in new Adobe Acrobat interface.

If you are using a "classic" Adobe Acrobat interface (pre-2022), then you can locate Mail Merge menu here:

Step 2 - Edit Mail Merge Settings

If necessary, clear current settings and then press the "Edit Settings…" button.

Press the "Browse…" button in the "Data Source" dialog to select the input data file.

Select a data type to use from the list (e.g.: Microsoft Excel Worksheet), then press "OK". Use the dialog that opens to locate and select the source file, then select the worksheet in the final dialog.

The file path for the data source will now be displayed. Optionally process only specific records in the data source by using the "View and Filter Records..." button - see the separate tutorial on sorting records here.

Step 3 - Add Field Mappings

Any fields in the PDF form will now be listed here. Use the buttons provided to assign correspondence between spreadsheet data fields and PDF form fields.

Press "Add Mapping..." to link an individual form field to the correct field in the data source - this may be necessary when fields have unusual names and the "Guess Mapping..." button cannot be used.

All available data fields are listed in the dialog that opens. Select the corresponding field and press "OK". Multiple-field expressions can also be used to create composite fields (adding data from multiple data fields to a single form field). This populating multi-line form fields tutorial shows an example of using this method.

Press "Guess Mapping" to automatically assign data fields to form fields by identifying name similarities. This is much faster than manually linking fields together.

Press "Yes" to proceed.

The number of successfully guessed mappings is displayed - in this example, the remaining three have been mapped to eachother. Press "OK" to proceed.

Step 4 - Save and Reuse Mappings

Optional: use the "Save Mappings..." button to save the current mapping configuration for future reuse.

Choose a suitable location, filename, and press "Save" to save it as an *.fmaps settings file.

The "Load Mappings..." button can be used to load a saved configuration. If mapping particularly long or complex field names, consider using this visual field mapping tool tutorial to create a mapping profile that can be loaded in the same way.

Press "Next >" to proceed.

Step 5 - Specify Field Options

Use this dialog to configure form field options in output documents. Most importantly, use the "Flatten output PDF documents..." option to convert form fields into regular text in the output merged document(s) so that they are no longer "editable". If this option is unchecked, filled forms can still be manually edited after processing. Use the checkboxes in the lower section to manually set specific fields to "read-only" status.

Press "Next >" to proceed.

Step 6 - Specify Output Options

In the "Output Files" page, press "Browse..." to select an output folder for filled forms. The file path will be displayed here - in this example, filled forms will be saved in the "C:\Documents\'Output_Files'" folder.

Ensure that the "Save each copy of the filled form as a separate PDF document" option is selected if you want individual filled forms saved in the output folder.

Optional: use the 'Compound Document' feature to produce one file containing all filled forms. Check this option and enter a filename - here we have used "CompoundDocument.pdf".

IMPORTANT: Uncheck "Create a separate document..." as ALL form fields will contain different data in the output. We also have the "Add bookmarks..." option selected so that the compound document can be easily navigated.

Use the "File Naming" section to create a file naming scheme for the output files. Use the drop-down list to see all options for base filenames, and optionally place custom text before or after it. Data fields can also be referred to in these boxes by entering them within [....] brackets. In this example, files will be named using the original filename followed by their corresponding location code from the "Code" data field (e.g.: "Report_K863.pdf").

Press "Next >" to proceed. (Skip through the remaining screens if further settings are not required - the configuration is now ready to execute the mail merge.)

Step 7 - Add Watermarks

Use this dialog to add customized watermarks to each page of the output forms. See the separate watermarking filled forms tutorial for help with these settings.

Press "Next >" to proceed.

Step 8 - Secure Documents

Use this dialog to add passwords to restrict opening, editing, or printing the output files. See the separate securing output files tutorial for help with these settings.

Press "Next >" to proceed.

Step 9 - Email Filled Forms

As well as saving them in the chosen output folder, this dialog can be used to send filled forms to email addresses within the data source by checking the "Send e-mails..." option. For example, you may be filling employee/business documents and want to automatically email them to the correct recipient(s). See this separate tutorial on creating and emailing certificates for help creating an accompanying email message. These can be written as "Plain Text", or it's also possible to configure the plug-in to send HTML-formatted messages.

Press "Next >" to proceed.

Note that if you have chosen to email documents, you will now be prompted to configure an emailing method via an SMTP server or your default email application (such as Microsoft Outlook).

Step 10 - Customize Processing with JavaScript Code (Advanced)

Use this final settings dialog to add user-specified JavaScript code to customize the processing further. JavaScript can be used to complete various tasks - press the "Help..." button to see some examples ("JavaScript Code Samples" topic).

Press "Finish" to confirm the current settings.

Step 11 - Complete the Mail Merge

Review the settings summary listed with green circles. Press "Edit Settings..." again to make any changes.

Optionally save the configuration as an *.mms file for later reuse by pressing the "Save Settings" button - use "Load Settings" to load them. Settings can also be saved to the open PDF form by pressing "Save Into Form". They would then be auto-loaded the next time this form is used for a mail merge (load them manually via "Load From Form").

Press "OK" to continue.

Press "Run Mail Merge" to confirm.

Step 12 - Open the Output Files

A results box confirms the success of the operation. By default, pressing "OK" will open the selected output location.

Open the output file(s) to inspect the filled forms.

Each filled form contains the corresponding data from the spreadsheet source, and one of two checkboxes has been checked.

Click here for a list of all step-by-step tutorials available.