Creating and Emailing Achievement Certificates or Diplomas
This tutorial shows how to automatically generate and email achievement certificates or diplomas using a PDF form template and data from an Excel spreadsheet using the AutoMailMerge™ plug-in for the Adobe® Acrobat®.
Tutorial Overview
Multiple PDF documents will be 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 on the Excel spreadsheet. Each row of spreadsheet contains data for a single certficate. The resulting PDF certificates will be saved into a folder and automatically emailed to recipients using the email addresses stored in the spreadsheet.
Tutorial overview
Data Description
The tutorial is using an Excel spreadsheet with 5 fields (columns): "First name", "Middle name", "Last name", "Issue date" and "E-mail". The data from each row will be used to populate a PDF form template to produce a separate achievement certificate that will be stored as a PDF file. The "E-mail" column contains email addresses and will be used to email completed certificates to the corresponding recipients.
Mail merging process
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. Select the "Tools" from the main toolbar. Select the "Prepare Form" tool to open a form editor. Select an existing PDF file that will be as used as a base for the PDF form. It is common to design documents in the regular 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.
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®.
Create a PDF form
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 and drag a rectangle to size the field accordingly. Type a desired 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.
Create a PDF form
 Select the "Options" tab and choose "Center" in the "Alignment" field. Click "Close" to save and exit the "Text Field Properties" dialog.
Center the text field
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.
Place text fields into a PDF form
Click "Save file" to save changes to the file. Now a previously static PDF document became an interactive PDF form.
Save file
Step 2 - Prepare Data in Microsoft Excel
We are going to use an Excel spreadsheet to store the data for the mail merge. 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 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.
Prepare data in the Microsoft Excel
Step 3 - Configure Mail Merge Parameters
Start the Adobe® Acrobat® application and using "File > Open..." menu open a PDF form template that was prepared.
Open a PDF form
Select "Plug-Ins > Mail Merge..." to open the "Mail Merge" dialog.
Open the Mail Merge dialog
Press the "Edit Settings..." button to configure the mail merge parameters.
Edit mail merge settings
Press the "Browse..." button in the "Data Source" screen to select an input spreadsheet.
Select an input data
Select "Microsoft Excel Worksheet (*xls.*xlsx)" from the list:
Select Microsoft Excel Worksheet
Select prepared data source file (use file prepared at the step 2). Click "Open".
Select a source file
Select a sheet from the list. Click "OK".
Select a source sheet
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.
Click Guess Mapping
The confirmation dialog appears on the screen. Click "Yes" to proceed.
Confirm the operation
The dialog showing the number of field mappings will be displayed. In this tutorial, only one field mapping has been guessed. Click "OK".
Close the reporting dialog
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 source fields and the "Name" text form field. The goal is to put text from these fields into a single text field.
Click Add Mapping
Check the "Use a multiple-field expression to create a composite field" option to use an "expression". Type  [First name] [Middle name] [Last name] into the "Enter Expression" text box. The text from these 3 data fields will be used to fill a single "Name" form field. Click "OK" to save the field mapping.
Specify field expression
Examine the field mappings and click "Next >" to advance to the next screen.
Examine the field mappings
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 required. 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.
Specify field options
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.
Specify output options
Click "Next >" to proceed. Skip the "Add Watermarks" and the "Security" screens by clicking the "Next >" button multiple times.
Step 6 - Specify E-mail 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.
Enable E-mail screen customization
Select data source field to use for the the email addresses. The values from this field should contain email addresses for the recipients.
Check the "User interaction is required for sending e-mails" option to display a confirmation dialog before sending each e-mail message.
Select data source field
Enter e-mail "Subject" into a corresponding entry box. Subject text can be customized by using references to the data field values. Use [field name] to insert to a data source field named field name into the text.
Enter e-mail message text into the "Message" field. Use [field name] to insert  text from a data field. For example, if data source 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.
Specify message text
Select emailing method in the "E-mail Server Type" screen. The plug-in provides two different emailing methods:
Select emailing method
  1. SMTP Email Server: This method allows sending emails without any user interaction while using a secure communication with the email server.
  2. Default Email Client: This method is using an email client that is already installed on the computer. Most email applications (such MS Outlook or Thunderbird) support a simple emailing method via MAPI protocol. Typically, this method does not require any configuration, but has some drawbacks.
You will be always prompted to confirm each outgoing e-mail if you are using a default e-mail application (such as Microsoft Outlook). This is a security measure imposed by Microsoft Windows to prevent unauthorized use of your email account. Use SMTP e-mail server to send e-mails without any user interaction.
There are two different modes for sending emails via a default email client:
  • If the "Use Simple MAPI protocol" option is ON. The emails will be send via a default installed email application and Windows will prompt to confirm each outgoing email. This is a Windows security restriction that cannot be removed. This method is a default option, because it is almost always available and does not require any setup.
  • If the "Use Simple MAPI protocol" option is OFF, then emails will be sent silently via a default installed email application via "Extended MAPI". However, this option can be and often is disabled by IT departments due to high security risk. If this option is allowed, then any application can silently send emails on the user behalf. If you are not able to send emails with this option unchecked, then most likely it is disabled by your IT department. The only other option would be using an SMTP server to send emails without user interaction.
You can read more about these two different email methods.
Click "Next >" to proceed. Skip the rest of the screens and click "Finish" on the last "Advanced" screen.
Finish configuring the mail merge settings
Step 7 - Save Settings
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.
Save settings
Step 8 - Start Mail Merge
Press the "Run Mail Merge" button in the "Confirm Processing" dialog to start a mail merge process.
Start the 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. Note, that this screen will not appear if a different emailing method is used.
Start the mail merge process
If the "User interaction required before sending each email" option was turned on, 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.
Start the mail merge process
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 is not desired, then use an SMTP emailing method.
Start the mail merge process
The "Mail Merge Results" dialog will appear once a mail merge is completed. Click "OK" to close this dialog and open an output folder.
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.
Examine output files
Here are the examples of the output certificates generated by the mail merge:
Output PDF achievement certificates examples
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:
Output PDF achievement certificates examples
Click here for a list of all step-by-step tutorials available.