Context
When teachers miss parents’ evenings because of illness on the day, we offer parents the chance to contact the teacher by email with any specific queries they were going to raise. However, sometimes the teacher knows in advance that they will miss a parents’ evening and then it is sometimes appropriate to email home a bespoke summary for each student instead. This needs to be quick and easy for the teacher to do, though, so as to minimise extra workload.
The solution in steps
In forming a solution, I wanted to leverage as many familiar technologies as possible, resulting in the following workflow:
- The teacher enters just three 1-4 scores (engagement, classwork, homework) into an Excel spreadsheet for each student in the class. The sheet converts these into sentences from a comment bank.
- A generic interim report is waiting in Word and Mail Merge is used to incorporate the three sentences from the Excel spreadsheet for each student.
At this point, the substitute reports are ready to go. Staff at my school have Google-based email accounts rather than Microsoft ones, though, so the Mail Merge can’t automatically email then out, unfortunately. Instead:
- A macro in Word saves each student’s bespoke copy as a PDF with the student’s School ID number as its unique filename.
- These are uploaded to a folder in Google Drive.
- A Sheet in Google Drive has some student information, including the parental email address.
- A script bound to the sheet sends an email to each parent, attaching the student’s unique PDF.
1. The Excel spreadsheet and comment bank
The School ID and name information is just copied from SIMS, etc, as are the most recently reported grades. The teacher enters just three numbers in the central three columns. Lookup formulae in the final three columns convert these to sentences using the comment bank. These sentences are reusable, but also editable if the teacher wishes to alter any of them.
2. The Word document and macro
Field codes are used to personalise a generic report by pulling each student’s name, grades and sentences from the Excel spreadsheet. To perform the merge: Mailings > Finish & Merge > Edit Individual Documents…
The following macro is then run to save these individual documents in PDF format, using the Student ID to create a unique filename.
To create a macro in Word: Developer > Macros, type a name in the Macro name box (e.g. MailMergeToPdf) and click Create. If you can’t see the Developer tab on the Ribbon, you might need to unhide it (right click anywhere on the Ribbon and choose Customize).
Sub MailMergeToPdf()
Dim mainDoc As Document, recordNum As Integer, singleDoc As Document
Set mainDoc = ActiveDocument
For recordNum = 1 To mainDoc.MailMerge.DataSource.RecordCount
mainDoc.MailMerge.DataSource.ActiveRecord = recordNum
mainDoc.MailMerge.Destination = wdSendToNewDocument
mainDoc.MailMerge.DataSource.FirstRecord = recordNum
mainDoc.MailMerge.DataSource.LastRecord = recordNum
mainDoc.MailMerge.Execute False
Set singleDoc = ActiveDocument
singleDoc.ExportAsFixedFormat _
OutputFileName:=mainDoc.Path & "\" & mainDoc.MailMerge.DataSource.DataFields("StudentId").Value & ".pdf", _
ExportFormat:=wdExportFormatPDF
singleDoc.Close False
Next recordNum
End Sub3. The Google Sheet
This has the same three columns from the Excel spreadsheet, plus the parents’ email addresses, also exported from SIMS, etc. Multiple addresses for the same student should be comma-separated in the same cell.
The following script is added via the Extensions > Apps Script menu in the Sheet. The first function adds a bespoke menu so that the main function can be run from the Sheet. This function should be run by the teacher, ideally, as the emails will come from whoever runs the script.
/**
* Inserts a custom menu when the spreadsheet is opened.
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Send reports').addItem('✉ Send', 'emailPDFs').addToUi();
}
/**
* Sends the emails.
* Called by the user from the custom menu.
*/
function emailPDFs() {
// Read the data from the spreadsheet into an array, removing the header row:
const sheet = SpreadsheetApp.getActive().getSheetByName("Data");
const data = sheet.getDataRange().getValues();
data.shift();
// Get the user's name, for use in the email:
const staffName = People.People.get('people/me', {personFields: 'names'}).names[0].displayName;
// Keep track of which row of the spreadsheet to write the outcome back to:
let rowNum = 1;
// Interate through the students (each row corresponds to one student):
for (const row of data) {
rowNum += 1;
// The student's name and file to be attached:
const studentForename = row[1];
const file = DriveApp.getFilesByName(row[0] + ".pdf").next();
// Read and trim the guardian email address, splitting into an array if more than one:
const parentEmails = row[3].split(",").map(a => a.trim());
// Interate through the parent emails for the current student:
for (const parentEmail of parentEmails) {
if (parentEmail.length > 0) {
// Begin the message to record the outcome of the invitation:
let statusMsg = parentEmail;
// Construct the email from the Email.html template:
let template = HtmlService.createTemplateFromFile('Email.html');
template.studentForename = studentForename;
// template.staffName = staffName;
let message = template.evaluate().getContent();
// Send the invite and add to the outcome message:
try {
MailApp.sendEmail({
name: staffName,
to: parentEmail,
subject: studentForename + "'s parents' evening for Science",
htmlBody: message,
attachments: [file.getAs(MimeType.PDF)]
});
statusMsg += " successfully sent.";
} catch (err) {
statusMsg += " FAILED to send: " + err;
}
// Write back the status message to the end of the current spreadsheet row:
const range = sheet.getRange(rowNum, 5);
let value = range.getValue();
if (value.length > 0) { value += "\r\n"; }
range.setValue(value + statusMsg);
}
}
}
}Line 47, above, creates the email using a template file, Email.html, which also resides in the Apps Script Editor. To create this, click the + Add a file button in the Editor (name it Email.html) and paste the following:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<p>Dear parent,</p>
<p>During the first two terms of Year 7, <?= studentForename ?> has been taught Science by Mrs Smith. However, Mrs Smith was providing temporary maternity cover for Mrs Jones, who will return to work after Easter and take over the teaching of the class. As this coincides with the timing of the Year 7 parents' evening, it will not be possible to provide a Science meeting, as Mrs Jones will not know the class well enough by then. However, Mrs Smith has produced a brief written report for you instead (attached below), summarising the key information that is usually conveyed during a parents' evening.</p>
<p>Kind regards,</p>
<p>Stuart Billington<br>
Head of Science</p>
</body>
</html>The text inside the HTML tags this can be written freely. Note the use of <?= studentForename ?> to include the student’s name in the email. This is defined on Line 48 of the script. As the commented out Line 49 indicates, other variables can be passed to the template in the same way and incorporated into the template.
When the script runs, it records success/failure in column E of the Sheet. The sent emails will also appear in the Gmail Sent folder of the person who ran the script.
