Class emailer

Over the summer I’ll be running online teaching sessions via Zoom to try and support some of our MSc students through their transition from ‘student learners’ to ‘active researchers’. The material I used in previous years was partly ‘activity-based’: probelem solving using sheets of paper, or objects etc.

However, that’s not going to work this year, which is a SUPER-PAIN-IN-THE-GLOOTS* because in an online environment I need to engage the class with dialogue and activities even more that I do in a workshop room/lecture hall. The technical familiarsation, socialiation and dialogue embedded in problem solving activities are the first two (or maybe three) steps of Salmon’s Five stage model of online learning. So how do I get activities out to students individually? VBA – that’s how.

* Hey. It’s a family blog!

Three important points before I go any further: 1) this is a live, beta version so comments welcome; 2) you need to have outlook and Excel installed on the computer you’ll run this on; 3) my blog won’t host macro-enabled files so the link is to my web-space at Strathclyde Uni; 4) Sorry, I don’t think it’ll work on Macs.

The Excel spreadsheet which is downloaded from this link has a macro coded into it that goes through the rows of a spreadsheet and emails each address in the A column, with an email containing the subject title in column B and the contents (or body) in column C. The macro is run by selecting cells (anywhere on the worksheet) and clicking the ‘Email to selected’ button at the top of column C. The macro works by using the rows of the selected cells (so you can highlight different groups of students or email subject or body).

This is in beta version, so any comments for improvement would be great. Email or tweet me, or use the comments below.

The VBA code for the emailer was based largely upon a youtube video by teachexcel.

If you don’t want to download an Excel spreadsheet with a macro embedded in it, you can create your own by using this file (Emailer draft_no macro). The file has a text box on the woksheet with the VBA code inside it so you can copy it straight into the VBA editor (some help for how to do this is here).


I’ve had some problems with the emailer in practice, which I think are caused by my virus protection programs being a few days out of date. I had to ‘accept’ a non-Outlook program sending an email for every email in the list.

Categories: Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s