Are you tired of having to manually send the same email to multiple people? Or maybe you have a spreadsheet full of contacts and would like an easy way to send out a form letter without having to import all of the addresses into your email program. Or perhaps you’re just looking for a way to schedule an email that emails current sales totals to your staff. In any case, almost everyone has some kind of use for a mass-mailer.
You’re going to need the email script you we built in my last article, “Sending Emails Using CDO in WSH.” In case you missed it, you can download a base script here.
If you haven’t read the previous article, you may feel a little lost in this one. I will be recapping some important points, but for the most part I will only be covering new material.
Let’s get started by taking our previous script and packaging it into a nice subroutine. Since we’re going to be sending multiple emails at once, we’ll need an easy way to keep recalling this portion of our script. I’ve also replaced all data portions with corresponding variable names. You can see my subroutine here.
With that done, we can now piece together the beginning of the script. All I’m going to do is list my default variables and create the CDO message object.
strFromEmail = "My Name <me@mymail.com>"
strSubject = "Daily Sales Recap"
strBody = "C:salesdailyreport.htm"
Set objMessage = CreateObject("CDO.Message")
Here, I’m just assuming that dailyreport.htm is an HTML formatted document that I want to send as the body of my email. You could easily append to this as necessary or even include a text string containing a custom HTML message.
With our base script in place, we’re ready to get down to business.
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.
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.
We have a functioning mass-mailing script but that’s never good enough for me. Let’s add some extra bells and whistles, shall we?
If you’ve ever used any kind of mass-mailing system before you’ve probably noticed that they let you use variables in your message that are replaced with each contact’s information as the message is sent. This is known as form mailing. An example message might look like this.
message.txt
Dear $name,
<p>
We're sending this email to notify you that your name has been added to our mailing list. If you would like to be removed, please contact us at <a rel=nofollow target=_blank style=color:blue; href= "mailto:remove@mymail.com ">remove@mymail.com</a>.
I’ll call this file message.txt and save it in the same directory as my other two files. We’ll need to add some code to read in its contents. We’ll add a small snippet to the beginning of our script to do that.
strFromEmail = "My Name <me@mymail.com>"
strSubject = "Daily Sales Recap"
Set objMessage = CreateObject("CDO.Message")
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objList = objFso.OpenTextFile("emaillist.csv")
Set objBody = objFso.OpenTextFile("message.txt")
strBody = objBody.ReadAll
objBody.Close
In my text example, the variable $name is used as a form element. It should be replaced with the name of our recipient before the message is sent. To do that we’ll need to add some code to our subroutine so that it’s replaced before sending each message.
Here I’ve added a line that uses the VBS Replace function to find every occurrence of $name in our body text and replace it with the contents of the strName variable. You would need to add another line to replace any additional form elements as well.
Running the script now will replace our body text with the name of our first contact, but then we run into a little problem. After the first replacement, strBody no longer contains any of our form elements. How do we go about changing it for each of the other contacts?
The answer is simple. We could write some code to determine the name of the last contact and do a replacement on that, but that’s too much work. The easiest way is to change our Sub statement one last time.
Sub SendMail(strName, strToEmail, ByVal strBody)
I’ve added the ByVal statement. The ByVal statement will create a duplicate instance of strBody in memory. One will be the original strBody in the Global namespace, and the duplicate will only be available in the SendMail namespace.
What does this do? This means that any changes we make to strBody in the SendMail subroutine will only affect its copy. This means that when we exit the subroutine, we will be left with the global strBody in its unaltered form.
Now that you have your base script, you should figure out other ways to implement it. You can use it as a network logon script or perhaps set it up as a scheduled task. Maybe you’re running a website on IIS and want to email daily logs to yourself. Whatever the case may be, your options are pretty much endless.
I would recommend adding to the script. As it is now, the script runs silently in the background. It would be nice to know when it completes or even to be able to monitor its progress. A simple Echo at the end of the subroutine would be better than nothing.
I would recommend incorporating full logging. Take a look at my article “Logging Events in WSH” to learn how to make your script maintain a text log file. Try listing each of the recipients as they are processed.
Unfortunately, error-handling with CDO is extremely complex. Short of knowing whether or not the CDO object was instantiated, there’s not much else you can do in script. You’ll have to rely on returned emails or your mail server logs to know whether or not the mail was successfully delivered.
Thanks for playing along again. I hope you were able to get something useful out of this. If you need any help customizing this to your needs, feel free to leave a message in the article blog. Have fun playing around with this script.
Oh, and please—no spamming. That’s it for me. Until next time, keep coding!
Windows Scripting 
/ 5 
Sending Emails Using CDO in WSH
