Mail Merge for Teachers | Jadrian Wooten

Using Mail Merge As an Educator to Personalize Student Communication

I have always been frustrated with the messaging platform associated with my institution's learning management system "LMS" and the announcement feature was too limiting. If I wanted to announce a change to an upcoming due date, the announcement feature was perfect. If I wanted to one or two emails about issues, the messaging feature was fine. But what about sending individualized emails with student-specific information? One option is to copy and paste the information from student to student, but I'm clumsy. Mail merge is a common practice outside of academic, but easy to implement with current LMS resources and Microsoft Office programs.

Software, Programs, & Requirements

The process outlined below is using Microsoft Office programs: Excel, Word, and Outlook. If your institution uses Gmail, you can still use Outlook as your mail client. You'll need to make sure you set that up correctly to use mail merge features in Word. Alternatively, you can use the mail merge for built into Gmail. The workflow below is based on Canvas features & is used to send an "early welcome" email to students before the course opens.

Step 1: Export an Excel file using LMS gradebook with personalized information

Our LMS (Canvas) gradebook includes student names and user names for all students enrolled in the course. Some universities may use email addresses as user names, which makes this process easier. My current university sets a student's username as the first part of the email address but leaves off the @vt.edu portion. My default gradebook settings list students as "Last Name, First Name," which changes the results below slightly if your settings are such that the student's First Name is listed first.

I've created an anonymized gradebook below that would look similar to an exported Canvas gradebook:

Step 2: Format Excel sheet for eventual mail merge

You'll want to keep the first row with column information, but then delete any rows that don't have student-related information. Aside from the first row, each row should include student-specific information that would go in an email to the student. Keep columns that contain information you want to include in emails to students. For pre-course emails, this may only be their names and email addresses, but emails sent after exams may include their exam scores. You can always add columns of information that you want to include in the email.

Keep a blank column located after the name column so that you can split the student's name into two columns. This will allow you to send emails to "First Name" instead of "Last Name, First Name." No one wants to get an email that says "Hey Jadrian Wooten," or even worse, "Hey Wooten, Jadrian." We want to be able to send an email that says, "Hey Jadrian." Before splitting the names, your Excel should look like this:

In order to split a student's so that only their first name is in one column, use the text-to-column feature in Excel. The first name can be split off using "Delimited" since a comma separates the fields we want to create. In my data file, the delimiter is a comma. If your gradebook showed names as "First Last" then the fields are delimited by a space. You can select where the split-off column ends up, but having the blank column after the original name field keeps things simple.

My LMS also only creates an email stem as the student's username, so I'll need to create an email address for the student by appending @vt.edu to each stem. If your gradebook exports the email address then you can skip this step. To add the email domain onto each stem, I use =C2&"@vt.edu" and drag to fill the email list. Your file is ready to go if it looks something like the one below. If I were sending emails after an exam, I may include columns about their exam score or their current grade in the class. Each row is unique to the student.

Step 3: Write your email in Word

From here on out, the process below is similar to anyone else using mail merge. All of the features you need can be found in the Mailings tab at the top of the banner. You'll start by selecting your recipient list using the button in the menu ribbon and selecting your modified gradebook file. You likely don't need to change your encoding process since, so just click okay on the next screen.

From here, you can add different fields from your Excel file directly into your document when you'd like to include personalized information. When you get ready to email students, the fields (like <<Name>> below) will insert the student's actual name from the column associated with that title. Any other fields you add will also be updated specifically for that student.

Here's an example of an end-of-semester email that I sent to students who earn at least a B+ in the course. When I download the final gradebook, I recreate the steps above to separate out first names and email addresses. I sort my students by the final grade column that Canvas adds at the end of the file. I delete all of the columns associated with assignments or student IDs. I then delete each student's row if they earned below 87%. I have access to my student's classification, so I also delete students who are juniors or seniors.

Step 4: Preview & Send

Be sure to use the "Preview Results" option in Mailings before sending emails to your students. This will let you see how each email will go out to students, and you can check to ensure that your fields are updating properly with information you may have included. This is especially important if you're sending grade-related information in the email. When you're ready to send, you would use the Finish & Merge button to send emails. Word will ask which column contains email addresses and what you want the subject to be. If you included any formatting, you may want to send the emails as HTML emails instead of text emails, but you have the option to do either. It will then synch with Outlook to send individual emails to each student on the Excel file you imported into Word.

Since this feature is popular outside of academic settings, you'll find a lot of helpful guidelines on the internet. One issue I had when I first started was that the mail option was available when I clicked Finish & Merge. The email option was grayed out because I hadn't set Outlook as my default mail client. I'm on a Mac, so Mail was my default. A couple Google searches led me to the solution and I was good to go for the rest of the term.