Creating and Emailing Achievement Certificates or Diplomas
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.
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 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 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.
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 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.
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 at step 1.
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 a spreadsheet file (prepared at the step 2). Click "Open".
Select a source file
Select a data 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 ("Issue Date"). 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 fields and the "Name" text form field. The goal is to combine text from these data fields into a single form field.
Click Add Mapping
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.
Specify field expression
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 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.
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.
Optionally, 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.
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.
Specify message text
Select emailing method in the "E-mail Server Type" screen. The plug-in provides two different emailing methods: SMTP server and use of default email client such as Outlook. Use SMTP server method to securely send emails without any user interaction. The default email client method may alway prompt user to confirm each outgoing email due to security restrictions.
Select emailing method
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. 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.
Start the mail merge process
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.
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 are 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.