Mail Merge for CPE Certificate Issuance

Description: The Office of the University Controller offers Continuing Professional Education (CPE) courses to University employees as well as professionals from the local community. The courses are offered on a regular basis throughout the year. A majority of the course attendees are Certified Public Accountants (CPAs) and require these courses to maintain CPA certification.  As a result, the University is required to issue certificates to each of the attendees to document completion of the course.  When the courses were first offered, issuing the certificates was a manual process. Each certificate had to be hand signed by the CPE administrator. The attendee name, course name, date, location and total credits had to be entered on each certificate. For some courses this could result in over 100 certificates being entered manually and hand signed, one-by-one. The need for an automated, faster process was clearly demonstrated. Upon review of the manual process, the need for a certificate that could automatically fill in the course name, the date, the location, the course credits, the attendee name and the administrator’s signature was addressed.  First, a certificate template was created in Microsoft Word (Word 2010). The template includes a merge field for the attendees first and last name, course name, course credits, date and location. A .jpg file with the administrator’s signature was also added to the signature line on the template.  This template can now be used for all CPE courses, regardless of location, date, or number of attendees. A Microsoft Excel (Excel 2010) file was created to extract the attendees’ names, email addresses and course name from the sign-in sheet completed by the attendees at the course. Now that the Excel file and Word template have both been created, the issuing of the certificates can now be complete in less than five minutes for any CPE course, regardless of the number of attendees. A simple mail merge extracts the information from the Excel file and loads it into the Word template. Each attendee now has a signed certificate issued with name of course and number of credits along with date and location of course. The certificate can now be immediately issued to the attendee in an electronic version delivered via email to the attendee. If the attendee prefers a hard-copy of the certificate, it can simply be printed at the attendee’s convenience. 

How does this impact the University? Lisa Vallad, the University’s CPE Program Manager, had this to say:  “Brian’s work on the CPE certificates changed my work demands from 1-1.5 days of work to about 1.5 hours of work, however, the streamlining of the process is priceless.”

Obviously, the new process is a time saver. It also saves paper and printer resources, including toner and maintenance, as all of the certificates are now created, processed and distributed in a digital format and are no longer printed and mailed to attendees.

The concept is very simple and is available for any University department to use and implement for the department’s internal processes.   Any department that presents conferences, organizes board meetings, offers training courses, distributes certifications to users for achievements and awards, can make use of this process to simplify and organize the attendee tracking, distribution of meeting minutes, issuing of certificates and awards, and validating the attendance of mandatory programs. Not only does this streamline the process, the automating of the process helps ensure there are no errors and offers a digital output format and is available immediately to all users. The digital format reduces wear and tear on equipment and reduces paper costs with a significant decrease in employee time and cost. Using this process, attendees can now be reminded of upcoming meetings or trainings using Microsoft Outlook (or any other online calendar program), attend the meeting or training, and receive an email immediately after attending with an attachment relevant to the meeting or training (including, but not limited to, minutes, certificates, awards, certifications, etc.).

Implementation Status:  This process was implemented in October, 2013 and has been used on a regular basis since that time. 

Submitted by Brian Dyet, Senior Help Desk Consultant, Office of University Controller, System Administration

Add new comment