Problem

Every term at Carleton, the registrar office has to send every teaching professor an email asking them if they want their class to use self-scheduled exams that term. Until recently, this process used to be done by hand, composing each email from a template one at a time. In the last few years, the office has learned to use mailmerge, a powerful tool built into Office, to automate the process of sending out the emails slightly. However, the process of collecting responses was still done by hand. The office would get emails in one at a time and spend hours building up a table of the classes that opted in.

Solution

Mail merge is a very powerful tool and because the office already knows how to use the software, it makes sense to keep it in the workflow. The first change was to build a google form to collect responses. After we built it, it could be reused every term without having to make changes. Google forms has a useful feature where it can prefill form fields if a the user goes to a special url that encodes the values to fill. I took the format of the special urls and built a calculation into the excel sheet given by Colleague (the software Carleton uses to manage class enrolment) to encode the information about each class so teachers wouldn’t have to enter it in themselves in the form. This both saves time and enforces data cleanliness.

To get the url into the mail merge, I had to find a work-around for an issue that Microsoft has known about since at least 2002.  Now, the mail merge will send each professor a custom email with their name, the class they are teaching, instructions, and a link they can click that will take them to the prefilled form. Once professors fill out the form, the data is compiled by google and kept in a google sheet, auto-generating the list that used to have to be done by hand.