Creating and Emailing Achievement Certificates or Diplomas

AutoMailMerge plug-in for Adobe® Acrobat®

Introduction

This tutorial shows how to automatically generate and email achievement certificates or diplomas. The tutorial is using a PDF form template and data from an Excel spreadsheet with the AutoMailMerge™ plug-in for the Adobe® Acrobat®.

Tutorial Overview

Multiple PDF documents are generated by filling a single PDF form template with a first, middle, and last name and an issue date. The data for each certificate is stored in the Excel spreadsheet. Each row of spreadsheet contains data for a single certficate. The resulting PDF certificates are saved into a folder and automatically emailed to recipients using the email addresses stored in the spreadsheet.

Data Description

The tutorial is using an Excel spreadsheet with 5 columns: "First name", "Middle name", "Last name", "Issue date" and "E-mail". The data from each row is used to populate a PDF form template to produce a separate achievement certificate that is stored as a PDF file. The "E-mail" column contains email addresses and will be used to email completed certificates to the corresponding recipients.

Prerequisites

You need a copy of the Adobe® Acrobat® along with the AutoMailMerge™ plug-in installed on your computer in order to use this tutorial. You can download trial versions of both the Adobe® Acrobat® and the AutoMailMerge™.

Step 1 - Create a PDF Form Template

Start the Adobe® Acrobat® application. The first step is create a fillable PDF form that is going to serve as a template. It is common to design documents in the text editors such as Microsoft Word, Adobe InDesign, or in any other similar application and then export it into PDF format. The form editor in Adobe Acrobat is used only to add fillable forms fields to allow using the document in the mail merge process.

Open "Prepare Form" Tool (New Acrobat Interface)

Prepare Form tool is used in Adobe® Acrobat® to create and edit interactive PDF forms. Use it to add text fields, checkboxes, buttons and other interactive elements to the PDF documents.

If you are using a "new" Adobe® Acrobat® interface, then expand All tools and find Prepare Form tool located towards the bottom of the list. Depending on your screen size you may need to click on “View more” link to find it.

Open "Prepare Form" Tool (Classic Acrobat Interface)

If you are using a “classic” Adobe® Acrobat® interface, then expand Tools and find Prepare Form tool.

If you are not familiar with the "Prepare Form" tool in Adobe Acrobat, then please refer to the detailed step-by-step tutorial on creating interactive PDF forms in the Adobe® Acrobat®.

Select an existing PDF file that will be as used as a base for the PDF form. For example, you can create a PDF file by printing MS Word document to "Adobe PDF" printer.

Add Fillable Text Fields

Click "Add a text field" icon on the toolbar. Next, click on the page at the location you want to place a text field. Click on a page to place a field at desired location. Type a field name into a "Field Name" entry box (for example, enter "Name").
Click "All Properties" link to open "Text Field Properties" dialog. Use this dialog to customize text appearance such as font name, size and color.

 Select the "Options" tab and choose "Center" in the "Alignment" field. Click "Close" to save and exit the "Text Field Properties" dialog.

We have placed "Name" and "Issue date" text fields on the PDF form. Next, we are going to show how to fill these fields with data from a spreadsheet.

Use main menu to save changes to the file. Now a previously static PDF document became a fillable PDF form. It can be used in the mail merge process as a template to generate certificates for different recipients.

Step 2 - Prepare Data in Microsoft Excel

We are going to use an Excel spreadsheet to prepare data for the certificates. The "Name" text field is going to be populated with text combined from 3 data fields ("First name", "Middle name" and "Last name"). This way text from 3 different data fields will appear as a single line of text without any unwanted gaps. The sample spreadsheet below contains information for 10 persons. There are 6 data fields (columns): "Record", "First name", "Middle name", "Last name", "Issue date" and "E-mail". The "E-mail" column will be used for sending certificates via email to a corresponding recipient.

Step 3 - Configure Mail Merge Parameters

Make sure to open a PDF form template that was prepared at step 1.

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

Press the "Edit Settings..." button to configure the mail merge parameters.

Press the "Browse..." button in the "Data Source" screen to select an input spreadsheet.

Select "Microsoft Excel Worksheet (*xls.*xlsx)" from the list:

Select a spreadsheet file (prepared at the step 2). Click "Open".

Select a data sheet from the list. Click "OK".

Step 4 - Map Form to Data Fields

Click "Guess Mapping" in the "Data Source" screen. The software will automatically select field correspondence based on the field name similarity.

The confirmation dialog appears on the screen. Click "Yes" to proceed.

The dialog showing the number of field mappings will be displayed. In this tutorial, only one field mapping has been guessed ("Issue Date"). Click "OK".

Select the "Name" form field from the list of available fields. Click "Add Mapping..." button to establish a correspondence between the "First name", "Middle name" and "Last name" data fields and the "Name" text form field. The goal is to combine text from these data fields into a single form field.

Check the "Use a multiple-field expression to create a composite field" option.
Type  [First name] [Middle name] [Last name] into the "Enter Expression" text box. The text from these 3 data fields will be combined to fill a "Name" form field. Click "OK" to save the field mapping.

Click "Next >" to advance to the next screen.

Step 5 - Specify Field and Output Options

Optionally, use the "Specify Field Options" screen to control if the output form fields should remain editable. Check the "Flatten output PDF documents" option to make output documents non-editable (if necessary). This option is checked in the tutorial, because we do not want the resulting certificates to remain editable. The output files should be regular the PDF documents. Click "Next >" to advance to the next screen.

Use the "Select Output Options" screen to specify where and how to save output PDF documents. Click "Browse..." to specify an output folder.

Check the "Save each copy of the filled form as a separate PDF document" option to save each certificate as a separate PDF file.

Specify a desired file naming by using the "File Naming" controls. We are going to name output PDF files by appending a value of "Last Name" data field to the original PDF file name. The output files will be named such as "Certificate_Doe.pdf", "Certificate_Smith.pdf", "Certificate_Swenson.pdf" and so on.

Click "Next >" to proceed. Skip the "Add Watermarks" and the "Security" screens by clicking the "Next >" button multiple times.

Step 6 - Specify Emailing Options

Use the "E-mail" screen to configure the email settings. Check the "Send e-mails to the recipients using addresses from the data source" option to distribute the output PDF files via email.

Select data source field to use for the the email addresses. The values from this field should contain email addresses for the recipients.

Optionally, check the "User interaction is required for sending e-mails" option to display a confirmation dialog before sending each e-mail message.

Enter e-mail "Subject" into a corresponding entry box.

Enter e-mail message text into the "Message" field. Use [field name] to insert  text from a data field into the email message. For example, if the spreadsheet has data fields "First name" and "Last name", then type [First name] [Last name] to personalize each message with a first and last name of each recipient.

Click "Next >" to proceed.

Step 7 - Select Emailing Method

Select emailing method in the "E-mail Server Type" screen. The plug-in provides multiple different emailing methods. The best method is to send emails via locally installed copy of Microsoft Outlook (Classic Edition).

You can read more about these different emailing methods.

Click "Next >" to proceed. Skip the rest of the screens and click "Finish" on the last "Advanced" screen.

Step 8 - Run Mail Merge

Check the settings summary. Optionally, save the mail merge settings into the *.mms file for a later reuse by pressing the "Save Settings" button. Use the "Load Settings" button to load them back later. Click "OK" to advance to the mail merge process.

Press the "Run Mail Merge" button in the "Confirm Processing" dialog to start a mail merge process.

Since we are using the most basic emailing method via a default email client, we are prompted by Microsoft Outlook before emailing of each document. This is due Windows Security policy to prevent unauthorized use of the email. Note, that this screen will not appear if a different emailing method is used.

If the "User interaction required before sending each email" option was turned on (in the "Select Email Options" screen), then the following screen will be shown for each message. Use it to review the correctness of the email message and attached PDF document. If this step is not required, then turn this option off.

Again, the following screen will be shown while using the most basic emailing method. Press "Send" button to actually send the email. If user interaction and verification are not desired, then use an SMTP emailing method.

The "Mail Merge Results" dialog will appear once a mail merge is completed. Click "OK" to close this dialog and open an output folder.

Step 9 - Examine Output Files

Examine output files by opening them in the Adobe� Acrobat�. There are 10 individual PDF achievement certificates that have been created in this tutorial. They are stored in the output folder.

Here are the examples of the output certificates generated by the mail merge:

All documents have been emailed via a default email client (Outlook) and appear in the "Sent" folder. Note that each email has a customized greetings line using a first and last name of each recipient. The certificate is attached to each email as a PDF file:

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