Directory Mail Merge to Email Messages?
April 30, 2015 10:01 AM Subscribe
I have a spreadsheet that has students’ names and their class schedules. We want to be able to email the class schedule to the students using an email mail merge. I am willing to use Excel/Word or Google Docs/Sheets. I think I have figured out how to use a directory mail merge in Word to print the schedules, but we really need to email the schedules to the students instead of print them.
I just learned how to write a very simple script in Excel, so if it involves some kind of scripting, I may be able to make it happen.
I made some sample spreadsheets to give you an idea what the originals look like:
Schedules: http://tinypic.com/r/33wte1s/8
Email Addresses: http://tinypic.com/r/4rrsc4/8
I just learned how to write a very simple script in Excel, so if it involves some kind of scripting, I may be able to make it happen.
I made some sample spreadsheets to give you an idea what the originals look like:
Schedules: http://tinypic.com/r/33wte1s/8
Email Addresses: http://tinypic.com/r/4rrsc4/8
If you have Outlook this looks to be very doable.
posted by something something at 10:14 AM on April 30, 2015
posted by something something at 10:14 AM on April 30, 2015
I can't give you any technical tips, unfortunately, but I wanted to chime in to recommend you run the final plan by the technology specialist at your school to make sure it complies with FERPA laws. I say this as a fellow educator: it's probably fine but worth checking to be sure!
posted by smorgasbord at 10:20 AM on April 30, 2015 [1 favorite]
posted by smorgasbord at 10:20 AM on April 30, 2015 [1 favorite]
Response by poster: @something, if I use that method, it will send a separate email for each row in the spreadsheet. I went all rows in the spreadsheet for a particular student to be in one email to that student.
posted by allison00 at 10:25 AM on April 30, 2015
posted by allison00 at 10:25 AM on April 30, 2015
Concatenate the rows for each student into one row using this. Or heck, just use semicolons. You can probably write a script to do it, matching on the student ID.
posted by brainmouse at 10:34 AM on April 30, 2015
posted by brainmouse at 10:34 AM on April 30, 2015
If you have their email addresses in the Excel spreadsheet, you can probably do this from within Excel. Here's some macro code that sends email:
Sub send_email()
mail_subject = "Insert Your Subject"
' Working in Office 2000-2010
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
Set a range of the information you want to print - this is specific to my app
Set rng = Sheets("KT Summary").Range("report_rows_cols").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
sendit = 1 ' ended up not using
If sendit Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "your.email@address.com"
.CC = ""
.BCC = ""
.Subject = mail_subject
.HTMLBody = RangetoHTML(rng)
.Send
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End If ' sendit
End Sub
posted by achrise at 12:18 PM on April 30, 2015
Sub send_email()
mail_subject = "Insert Your Subject"
' Working in Office 2000-2010
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Set rng = Nothing
On Error Resume Next
Set a range of the information you want to print - this is specific to my app
Set rng = Sheets("KT Summary").Range("report_rows_cols").SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If
sendit = 1 ' ended up not using
If sendit Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "your.email@address.com"
.CC = ""
.BCC = ""
.Subject = mail_subject
.HTMLBody = RangetoHTML(rng)
.Send
End With
On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
End If ' sendit
End Sub
posted by achrise at 12:18 PM on April 30, 2015
Response by poster: @brainmouse, I don't think I know how to concatenate only those rows that match a specific ID number. It might be 3 rows for one student and 5 rows for another student.
@Nevin, I think MailChimp might work for us, but we have 2500 students at our school. I would have to pay for it, right?
@achrise, I like the idea of your code, but after studying it for a while, I'm not sure I understand where I would change the code to fit my data. So, where do I tell the code that the email addresses are in column D? And how does it know to include all rows that have a matching ID number?
posted by allison00 at 1:26 PM on April 30, 2015
@Nevin, I think MailChimp might work for us, but we have 2500 students at our school. I would have to pay for it, right?
@achrise, I like the idea of your code, but after studying it for a while, I'm not sure I understand where I would change the code to fit my data. So, where do I tell the code that the email addresses are in column D? And how does it know to include all rows that have a matching ID number?
posted by allison00 at 1:26 PM on April 30, 2015
My code would be included in a loop that goes through your people one-by-one. Looking back I'd probably move some things around (specifically defining the range AFTER the On Error. You'd start your loop after the On Error (after everything is defined), and within the loop you'd step through your people, defining for each the range (which is the message that gets emailed) and the .To address. then end the loop after the End With and before the On Error Goto 0.
Taking another step back, you might want to have a separate sheet with a format laid out where you could poke each person's schedule into. Then you could define the range once to be the nice formatted area, do all the Dims and Sets and enter your "people" loop. Then take each person's individual information and poke that into the nice formatted sheet and mail that. repeat until done.
posted by achrise at 5:44 PM on April 30, 2015
Taking another step back, you might want to have a separate sheet with a format laid out where you could poke each person's schedule into. Then you could define the range once to be the nice formatted area, do all the Dims and Sets and enter your "people" loop. Then take each person's individual information and poke that into the nice formatted sheet and mail that. repeat until done.
posted by achrise at 5:44 PM on April 30, 2015
This thread is closed to new comments.
posted by brainmouse at 10:10 AM on April 30, 2015