Building a Mass-Emailer in WSH - Creating a mailing list
(Page 2 of 4 )
This script won’t be doing us a whole lot of good if we can’t tell it where to send our email. For the purposes of this article I’m going to read my email list from a comma-delimited text file. With a little more code, you could easily query a database or read them from a spreadsheet. You could even read them from your Outlook Address Book if you wanted.
emaillist.csv
John Doe,john@mymail.com
Sales Group,sales@mycompany.com
Jane Wilkens,janew@outsourcing.com
Frank,frank_k@yahoo.co.uk
Here I just created a simple CSV file with names and corresponding email addresses. I’ve named it emaillist.csv and saved it to the same directory as my script. It should be apparent that we’re going to need to do a little parsing to be able to use this list so let’s make a call to the FileSystemObject and open our text file.
strFromEmail = "My Name <me@mymail.com>"
strSubject = "Daily Sales Recap"
strBody = "C:salesdailyreport.htm"
Set objMessage = CreateObject("CDO.Message")
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objList = objFso.OpenTextFile("emaillist.csv")
Now we need to create a small parsing script to read in our names and email addresses. We’ll use a simple Do…Loop like you’ve probably seen a dozen times.
Do Until objList.AtEndOfStream
strLine = objList.ReadLine
SendMail
Loop
objFile.Close
This simple snippet loops through our file one line at a time and then calls our SendMail routine for each contact. Of course, this doesn’t do us much good at the moment because all we’ve done is read the information into our script. Now let’s parse it.
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
arrContact = Split(strLine, ", ")
strName = arrContact(0)
strToEmail = arrContact(1)
ReDim arrContact(0)
SendMail strName, strToEmail, strBody
Loop
objFile.Close
Each line in our text file contains a name and an email address separated by a comma. VBScript’s Split function works nicely to split our text line into a simple array. The first array item contains the contact’s name and the second contains the email address. Don’t forget that array items are zero-based.
After assigning the variables we use a ReDim statement to empty the array’s contents for the next loop iteration. This isn’t completely necessary, but it’s a good habit to get into because it can be a problem spot in more complex scripts.
Finally, we send those variables off to our SendMail subroutine. That means we’ll need to make a slight change to its declaration so that it accepts our variables as attributes. While we’re making changes, let’s add a little extra code to prevent problems.
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
arrContact = Split(strLine, ", ")
strName = Trim(Replace(arrContact(0), Chr(34), ""))
strToEmail = Trim(Replace(arrContact(1), Chr(34), ""))
ReDim arrContact(0)
SendMail strName, strToEmail, strBody
Loop
objFile.Close
Sub SendMail(strName, strToEmail, strBody)
In this piece I added a little precautionary code. I’ve made use of the Replace function to strip any unwanted quotation marks from our strings. I finish up by using Trim to make sure there’s no leading or trailing spaces.
Why have I done this? I’m assuming that many CSV files are exported from some other program. It’s not uncommon for programs to export text strings in quotation marks.
Next: Customizing a form message >>
More Windows Scripting Articles
More By Nilpo/Developer Shed Staff Writer
/ 4 
