|
There are full featured mass mailers out there, and
programs like Word that can do mail merge in a flash, but if you are on
a strict budget and want to do a custom e-mailing for your small
business or a non-profit then you can still do it yourself using a
script like this one. I won't be able to show
all the CDO methods here such as using a remote server with
authentication so please visit
http://www.paulsadowski.com/WSH/cdo.htm to learn about alternate
methods of using CDO so you can customize the SendMail function below as
needed.
In brief, the script below takes a text-based template
file that you create and does variable substitutions within the text
from data in an Access database and then sends the email using CDO.
This is not a complicated procedure but it does
require some knowledge of scripting database access and building SQL
queries. It is not a copy and paste script. It requires some work on
your part. This example uses a DSN-less connection to an Access database
(you can use ODBC as well) and later I will add examples that use Excel
and a plain text file as the data source.
The Template File
The template file is a plain text file that contains the email
text you want to send out. Within it is contained replaceable sections
of text (Variables) into which data from the database is substituted in
its place. You can use any format you like for the variables but they
must be unique and not likely to appear with in any normal text you do
not want substituted. I like using this format, [$VARIABLENAME$] Such
text is not likely to appear in any normal text and clearly shows itself
to be a replaceable variable at a glance. This script also uses
case-sensitive variable substitution to further minimize the possibility
of false substitutions.
The variable names you use can be anything but should
be related to the substitution content for easy understanding, and
you may even want to make them the same as the database field names they
are related to. Examples: [$FIRSTNAME$] [$LASTNAME$] and remember since
our script does case-sensitive substitutions, [$FIRSTNAME$] [$FirstName$]
and [$firstname$] are three different variables.
Variable substitution is global, meaning all instances
of a variable are replaced by a single substitution. So you could use [$FirstName$]
in the greeting of the email and in the body text as well and each
instance will be replaced with the same data.
This script allows you to add comment lines within
your template. Each line that begins with two pound signs, ## is
considered a comment and will not appear within the email the script
sends. The pound characters must be the first characters on the line
else they will not be interpreted as comment lines.
|
|
If you
find the content of this page useful,
please consider making a small donation
to show
your support and keep the content
updated and fresh. |
Here's a sample of a simple template, in fact the
template we will use for this example.
#############################################
## This is a comment line
## This file generated by Paul R. Sadowski
## on Thursday, April 14, 2005
## for demonstration purposes
#############################################
Hello, [$FIRSTNAME$] [$LASTNAME$]:
Thank you for your interest in our forthcoming lecture series,
Scripting In A Computerized World.
## Verify dates before emailing!
We are pleased to announce a three day session this July that
you may want to attend. Each of the three days will consist of
a morning and an afternoon session dedicated to one topic.
[$FIRSTNAME$], I'm sure you will find the small
investment of
$300 for all six sessions (or $100 per day) a valuable investment
in your career.
For additional information or to reserve your spot please
email me at [$MYEMAIL$] or telephone me at [$MYTELEPHONE$].
Thank you, [$FIRSTNAME$]. I look forward to seeing you
at the
lecture!
Sincerely,
[$SIGNED$]
## End of template.
And here's the text from a sample email,
copied & pasted from an actual email
created with the above template:
From: testing@test.com
To: joe@test.com
Cc:
Subject: Scripting News
Hello, John Doe:
Thank you for your interest in our forthcoming lecture series,
Scripting In A Computerized World.
We are pleased to announce a three day session this July that
you may want to attend. Each of the three days will consist of
a morning and an afternoon session dedicated to one topic.
John, I'm sure you will find the small investment of
$300 for all six sessions (or $100 per day) a valuable investment
in your career.
For additional information or to reserve your spot please
email me at hsmith@test.com or telephone me at 999-555-7890.
Thank you, John. I look forward to seeing you at the
lecture!
Sincerely,
Harry Smith
The Database
You'll be customizing your script to your database. In our
example this is the format of the database we are using.
I'll be showing you next the part of the script that you alter
to fit your database and your template email.
Our example database fields in a table named Clients:
|
FirstName |
LastName |
Street |
City |
State |
ZipCode |
Phone |
Email |
Active |
| John |
Doe |
123 Main
St |
Anycity |
NY |
12345 |
555-1234 |
joe@test.com |
Y |
| Mary |
Smith |
999
Tacoma |
Someplace |
CA |
54321 |
555-4321 |
msmith@test.com |
Y |
Const ForReading = 1, ForWriting =
2, ForAppending = 8
Const TEXTMSG = 0
Const HTMLMSG = 1
Const TemplateFile = "C:\Acme Inc\Promotions\Templates\MayMassMail.txt"
if FileExists(TemplateFile) <> 0 then
WScript.Echo "The email template file, " & Chr(34) & TemplateFile &
Chr(34) & ", does not exit!"
WScript.Quit
End If
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(TemplateFile, ForReading)
strBodyTextOrg = f.ReadAll
f.close
Set f = Nothing
set fso = Nothing
Set OBJdbConnection = CreateObject("ADODB.Connection")
OBJdbConnection.Open "DRIVER={Microsoft Access Driver " & _
"(*.mdb)};DBQ=C:\Acme Inc\Clients.mdb"
SQLQuery = "SELECT * FROM Clients Where Active=True"
Set Result = OBJdbConnection.Execute(SQLQuery)
if Not Result.EOF then
Do While Not Result.EOF
strBodyText = strBodyTextOrg
strBodyText = StripComments(strBodyText)
'This is the section of the script you
customize for your template and database fields.
strBodyText = ReplaceVariable(strBodyText, "[$FIRSTNAME$]",
Result("FirstName"))
strBodyText = ReplaceVariable(strBodyText, "[$LASTNAME$]",
Result("LastName"))
strBodyText = ReplaceVariable(strBodyText, "[$SIGNED$]",
"Harry Smith")
strBodyText = ReplaceVariable(strBodyText, "[$MYEMAIL$]",
"hsmith@test.com")
strBodyText = ReplaceVariable(strBodyText, "[$MYTELEPHONE$]",
"999-555-7890")
SendMail Result("Email"), "testing@test.com", "Scripting
News", strBodyText, TEXTMSG
'End customization.
Result.MoveNext
loop
end If
OBJdbConnection.Close
Set Result = Nothing
Set OBJdbConnection = Nothing
Function ReplaceVariable(strTheString, strReplace,
strReplaceWith)
ReplaceVariable = Replace (strTheString, strReplace,
strReplaceWith, 1, -1, 1)
End Function
Function StripComments(strTheString)
Dim objRegExp
Set objRegExp = New RegExp
objRegExp.Global = True
objRegExp.Multiline = True
objRegExp.Pattern = "^##.*\n"
StripComments = objRegExp.Replace(strTheString, "")
Set objRegExp = Nothing
End Function
Function SendMail(strTo, strFrom, strSubject, strBody,
msgType)
Set objMail = Wscript.CreateObject("CDO.Message")
objMail.To = strTo
objMail.From = strFrom
objMail.Subject = strSubject
if msgType = TEXTMSG then
objMail.TextBody = strBody
else
objMail.HTMLBody = strBody
end if
objMail.Send
set objMail = Nothing
End Function
'return 0 if a file exists else -1
function FileExists(Fname)
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
if fs.FileExists(Fname) = False then
FileExists = -1
else
FileExists = 0
end if
Set fs = Nothing
end function
|
|
