Every trimester, the school I go to sends its students an email with a list attached to it. The list shows all students, the exams they took, and whether the student passed or failed the exam. Quite nice, but we 'd like to know our exact score.
For some reason, the school does not want to put exact scores on that list. They also claim that they can not send an email message to each individual student with his or her score for this or that exam.
Fair enough, I would't expect some secretary to hit 'compose new message' 500 times, type in a different email address 500 times, write the same message 500 times, but changing the name of the student, the exam he/she took, and his/her score.
I would, however, expect a computer to be capable of doing just that : create any number of mail messages, customize them so that they show an individual student's name and score for a given exam, and send them to that student's email address. Computers are supposed to be good at things like that. And the amount of customized junk mail we all get, by letter or email, shows it can be done.
I then remembered a some Visual Basic scripts that were used to create Outlook email messages. That would cover the mailing. To create personalized messages, I thought it shouldn't be too difficult to put students' names, email addresses, exams and scores in a file, have that file read by the script, and let the script include the suitable values in a prefab mail message and send it.
So here it is : a Visual Basic script that creates personalized mail messages, to be sent one by one to individual email addresses, telling each individual student something like "Dear George, For the exam Datacommunication and Computer Networks, you scored 95 points out of 120."
The script requires a text file with separated values, like this :
Apers;Karine;karineapers@hotmail.com;Progammeren 1;30;60 Apers;Karine;karineapers@hotmail.com;Netwerkbeheer en Datacommunicatie;45;60 Noens;Koen;koen.noens@yahoo.fr;Datacommunicatie;92;120 Peeters;Erik;erik_peeters@pi.co.uk;Netwerkbeheer 2;105;120
So the format is
LastName;FirstName;Emailaddress;Exam;Score;MaximumScore
separated by ';'. the separating character allows to have names with
spaces in it, but it does not necessarily need to be a ;. It can be any
character.
It is fairly easy to create such a text file, eg. from an excel worksheet saved as comma-separated values, or by querying a database.
The first part of the script will deal with reading this file line by line, and putting each item on that line (items are separated by ; ) into a variable to be used to compose a mail message.
In the first example, we assume there is Microsoft Outlook installed on your computer, and the script will use Outlook to create the customized messages. This 'proof of concept' code also shows, on the screen, what message would be sent. You can of course comment that out.
'mass mailing script
'Koen Noens - December 2003 -
'Assumptions
' a working configuration of Microsoft Outlook
' data in a text file, values separated by ; (see const Separator)
'__________________________________________________________________________
'const
const ForReading = 1
const Separator = ";"
'init
Set fso = CreateObject("Scripting.FileSystemObject")
Set objOutlook = CreateObject("Outlook.Application")
strDataFile = "results.txt" 'this is name and location of txt file with data
intCounter = 0
'Read data to create mail msg
Set objFile = fso.OpenTextFile (strDataFile, ForReading)
Do
'1- Read records from textfile
strTextLine = objFile.ReadLine
arrItem= Split(strTextLine, Separator)
'waste some memory, for clarity
strStudentLastName = arrItem(0)
strStudentFirstName = arrItem(1)
strStudentEmail = arrItem(2)
strModule = arrItem(3)
intScore = arrItem(4)
intModuleMaxPoints = arrItem(5)
'2- Create email message
strMsgBody = vbCrLf & _
"Beste " & strStudentFirstName & "," & vbCrLf & _
vbCrLf & _
"Op het examen " & strModule & vbCrLf & _
"behaalde je " & intScore & " op " & intModuleMaxPoints & _
vbCrLf & vbCrLf & _
"mvg" & vbCrLf & _
"---signature---" & vbCrLf
' debug : msgbox to see if it works so far
MsgBox strStudentEmail & vbCrLf & vbCrLf & strMsgBody, vbOKonly, strTitle
If objOutlook = "Outlook" Then
Set objMailMsg = objOutlook.CreateItem(0)
objMailMsg.To = strStudentEmail
objMailMsg.Subject = "examenresultaat " & strModule
objMailMsg.Body = strMsgBody
objMailMsg.DeleteAfterSubmit = True 'don't keep sent mail
If objMailMsg.To <> "" Then
objMailMsg.Send
intCounter = intCounter + 1
End If
End If
Set objMailMsg = Nothing
Loop While objFile.AtEndOfStream = False
'cleanup
objFile.Close
Set objFile = Nothing
Set fso = Nothing
objOutlook.Quit
Set objOutlook = Nothing
strFeedback="Script completed" & vbCrLf & _
intCounter & " messages placed in Outlook OutBox"
MsgBox strFeedback, vbOKonly, StrTitle
Wscript.Quit
That wasn't too hard now, was it ?
Now, we do not really need Outlook to send those messages. VBS knows
'CDO', objects that provide access to Microsoft Exchange Server, or to
the build-in default virtual SMTP server in Microsoft Internet
Information Server (IIS). So if you have Windows NT or 2000 with ISS or
Exchange Server, you can try it this way :
'mass mailing script
'Koen Noens - December 2003
'The mailing function in this script is copied from Microsofts Portable Script Center
'Sending Email from a script
'Demonstration script that uses CDO to send email from a computer
'where the SMTP Service has been installed.
'
'This script also demonstrates how to read data from a text file to create custom messages
'Assumptions
' SMTP service on the computer where this script runs.
' CDO assumes Microsoft Exchange Server
' or Default virtual SMTP server that comes with Microsoft Internet Information Server (IIS)
' data in a text file, values separated by ; (see const Separator)
'
'____________________________________________________________________________________________
'const
const ForReading = 1
const Separator = ";"
const MsgFROM = "secretary@school.be" 'edit suitable FROM address
'init
Set fso = createobject("Scripting.FileSystemObject")
strTitle = "Mass Mailing Script"
strDataFile = "results.txt" 'edit or use input box or so
intCounter = 0
'Read data to create mail msg
set objFile = fso.OpenTextFile (strDataFile, ForReading)
Do
'1- Read records from textfile
strTextLine = objFile.ReadLine
arrItem= Split(strTextLine, Separator)
'waste some memory, for clarity
strStudentLastName = arrItem(0)
strStudentFirstName = arrItem(1)
strStudentEmail = arrItem(2)
strModule = arrItem(3)
intScore = arrItem(4)
intModuleMaxPoints = arrItem(5)
'2- Create and send email message
strMsgBody = vbCrLf & _
"Beste " & strStudentFirstName & "," & vbCrLf & _
vbCrLf & _
"Op het examen " & strModule & vbCrLf & _
"behaalde je " & intScore & " op " & intModuleMaxPoints & "." & _
vbCrLf & vbCrLf & _
"mvg" & vbCrLf & _
"---signature---" & vbCrLf
' debug : msgbox to see if it works so far
MsgBox "To: " & strStudentEmail & vbCrLf & vbCrLf & _
"From: " & MsgFROM & vbCrLf & vbCrLf & _
strMsgBody
Set objEmail = CreateObject("CDO.Message")
objEmail.From = MsgFROM
objEmail.To = strStudentEmail
objEmail.Subject = "resultaat examen" & strModule
objEmail.Textbody = strMsgBody
objEmail.Send
'count send msgs
'debug : should get return code from SMTP server to
'count success / fail sending mesgs
intCounter = intCounter + 1
Loop While objFile.AtEndOfStream = False
'cleanup and quit
objFile.Close
Set objFile = Nothing
Set fso = Nothing
Set objEmail= Nothing
strFeedback="Script completed" & vbCrLf & intCounter & " messages sent"
MsgBox strFeedback, vbOKonly, StrTitle
Wscript.Quit
Lastly, Microsoft's Portable Script Center also provides the following script code to use an SMTP server on a remote machine, in case you don't have SMTP service running on the same computer as where you're running this script :
Demonstration script that uses CDO to send email from a computer where the SMTP Service has not been installed. Replace the name smarthost with the name of your SMTP server..
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "admin1@fabrikam.com"
objEmail.To = "admin2@fabrikam.com"
objEmail.Subject = "Server down"
objEmail.Textbody = "Server1 is no longer accessible over the network."
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"smarthost"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.Send
So. That's all there is too it.