Inside Technique : Sending an Automated Newsletter : The Code

All subscribers are kept in a database. Each subscriber has a flag that specifies whether the user receives the HTML or plain text newsletter. Since we want to eventually provide a personalized newsletter we need to stop sending one e-mail BCC'ed to thousands of users and instead send each newsletter individually. As with most of our solutions, we avoid reinventing the wheel and leveraged VBScript with the CDO SendMail object, a DSN connection to our database, and simple file access.

If you use VBScript and understand ASP, you have the knowledge to create VBScript'ed batch files that can be executed by calling the CSCRIPT.exe interpretor. To send the newsletter we just invoke CSCRIPT.exe newsletters.vbs on the server and the newsletter is automatically sent. We can also schedule this task to run unattended using Windows NT scheduler.

The script looks very similar to an ASP page. You can quickly convert many .vbs batch files into ASP page simply by changing the extension from .vbs to .asp and wrapping the code in <%...%>. We chose not to create an ASP page because this script is not intended to be run as a web page. Furthermore, for a large newsletter list, the page would take a very long time to run (easily up to an hour depending on the number of subscribers).

Now let's look at the script. This is very similar to the one we use to send our newsletter.

' This script should be saved with the extension ".vbs"
' Execute the cscript by calling CSCRIPT.exe scriptname.vbs

' The HTML Newsletter
const htmlpath = "d:\newsletters\current.htm"
' The plain text newsletter
const textpath = "d:\newsletters\current.txt"

' This can be automated - currently hard coded subject
const subjectString = "SiteExperts.com Update for November 1, 1999"

' Create a connection to the mail DSN
Dim oConn
set oConn = CreateObject("ADODB.Connection")
oConn.open "mail"

Dim oRS, objFSO, objFile,sHTML,sText

' Get the e-mail addresses
set oRS = oConn.execute("select * from address")

' Read and cache the HTML and Plain Text newsletters
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(htmlpath)
sHTML = objFile.readall
objFile.close()

Set objFile = objFSO.OpenTextFile(textpath)
sText = objFile.readall
objFile.close()

Dim objMail

while not oRS.eof
	' Create a mail object
	set objMail = CreateObject("CDONTS.NewMail")
	objMail.from = "newsletters@siteexperts.com"
	objMail.To = oRS("email")
	objMail.subject = subjectString
	' What format to send
	if (oRS("html")=0) then
		objMail.bodyFormat = 1
		objMail.Body = sText
	else
		objMail.bodyFormat = 0
		objMail.ContentBase = "http://www.SiteExperts.com"
		objMail.MailFormat = 0
		objMail.Body = sHTML
	end if
	objMail.send
	set objMail=nothing
	oRS.movenext
wend

When using the NewMail object, you need to create a new instance for each e-mail message. Once you call the send() method, the object is no longer usable. This is why for each subscriber we need to create a new instance of the object. Also, as we plan to add personalized features to the newsletter, we simply need to enhance the script to generate a custom message body.

This solution is elegant and simple. Keep in mind that this is not a performance-tuned solution and you need to evaluate your own needs to determine when this solution becomes impractical.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.