Mail Merge - Programming

Motivation

As a Student Associate at Yale-NUS College, I noticed that staff had to send out mass personalised emails regularly. The college was using Microsoft Outlook as their client, and the mail merge program has been shown to be unreliable (especially on Macs!) - sometimes emails don't get sent and the user won't even be notified of that! Furthermore, there are feature limitations - you can only send emails to one recipient, and you cannot CC or BCC to other recipients. These issues caused inconveniences and problems for the staff.

Solution

I set out to make a better mail merge program. My program asks the user to input an excel spreadsheet containing the data, as well as the email template to be used. The email template should be created in Microsoft Outlook and saved as a ".msg" file, with merge tags (chosen to be "<<" and ">>") indicating the column data that needed to be pulled from the spreadsheet. The program will then open a browser showcasing a preview of the final merged emails that will be sent out, so that the user can click through and check before sending out the mail merge. Multiple recipients (whether it be "To", "CC" or "BCC") are allowed.

Furthermore, a common use case among staff was to have multiple versions of a template - the version that is sent out to a particular person is based on their specific situation. To cater for this, I added in functionality for the program to accept multiple templates, and for each row in the spreadsheet to indicate what category (i.e. template) it falls under. Then, the program will select the correct template for each recipient based on the provided data, and complete the mail merge. This saves time and effort for the staff, because they don't need to perform multiple mail merges (which they have to if they use the traditional Outlook mail merge). Using my program, college staff sent out mass personalised emails to over 200 students multiple times without a hitch!

I developed the program in Python, making use of platform-specific packages to execute Outlook actions (e.g. pywin32 for Windows and appscript for Mac). In the future, I plan to create a graphical user interface to improve usability.