Friday, May 13, 2011

Windows VBScript To Send Email Using CDO

Category: Postfix, Windows — SkyHi @ Friday, May 13, 2011

Windows 2000 and Windows XP use CDO messaging as a replacement for CDONTS.

Sending email with CDO is a simple task. First we create a reference to the CDO component

Set objMessage = CreateObject("CDO.Message")
then fill-in Sender, Subject and Recipient (To) fields of the headers and the body text which can be either plain text or HTML. You can also add a file attachment. You then use the Send method to send the email.

Below I'll show all three types of emails, and how to send an email using a remote SMTP server in the event you are not running your own. I've added and example to illustrate how to request a return receipt and delivery status notifications.
Please note, when using the AddAttachment method in your scripts you must use a fully qualified pathname as the argument to the method. Using just a file name or a relative path will produce the error The specified protocol is unknown.
If you receive an error message related to objMessage.From then you should try replacing it with objMessage.Sender
I've added sample code to illustrate how to load the body of the email from a text file on your disk.
I've added a sample of how to load recipient data from a database.
I've added a sample illustrating how to use data from Excel in an email.
If you are looking for an ASP based email form processor then please look here.
If you are interested in a mass mailer using CDO and VBScript, look here.

This sample sends a simple text email that can be viewed in any email client.

Sending an HTML email.

Note the use of the Cc & Bcc properties to send using Blind Carbon Copy (Bcc) and Carbon Copy (Cc).
These properties can be used with either text or HTML email.

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = "me@my.com"
objMessage.To = "test@paulsadowski.com" 'The line below shows how to send using HTML included directly in your script
objMessage.HTMLBody = "

This is some sample message html.

"

'The line below shows how to send a webpage from a remote site
'objMessage.CreateMHTMLBody "http://www.paulsadowski.com/wsh/"
'The line below shows how to send a webpage from a file on your machine
'objMessage.CreateMHTMLBody "file://c|/temp/test.htm"

objMessage.Bcc = "you@your.com"
objMessage.Cc = "you2@your.com"
objMessage.Send

Sending a text email with an attached file. By repeating the .AddAttachment method you can attach more than one file.
When attaching files keep in mind that your recipient may be limited in their
ability to receive files above a certain size. Many ISPs limit emails to 8 or 10MB each.
You should not send large files to anyone before obtaining their permission.

Sending a text email using a remote server. Sometimes you need to send email using another server. It may be required by your
company, or your ISP may be blocking the SMTP port, or your dynamic IP may be
blacklisted for being in a dynamic pool.
This code shows you how to use a remotes server rather than the SMTP server
on your own machine.

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = "me@my.com"
objMessage.To = "test@paulsadowski.com"
objMessage.TextBody = "This is some sample message text."

'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP. objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.myserver.com"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

Sending a text email using authentication against a remote SMTP server. More and more administrators are restricting access to their servers to control spam or limit
which users may utilize the server. This example shows you how to use basic authentication,
the most commonly used authentication method, when the SMTP server you are using requires it.
This code is slightly more complex but not very difficult to understand or work with.

Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = """Me"" "
objMessage.To = "test@paulsadowski.com"
objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication."

'==This section provides the configuration information for the remote SMTP server.

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.your.com"

'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youruserid"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False

'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

Send using authentication against a remote server with a file attachment and return receipt and
delivery disposition notification requests. In order to use the Delivery Status Notifications (Return
Receipt and Delivery Disposition requests) we need to create a reference to the CDO Configuration
object in addition to the CDO Message object and set a small number of properties. You must
use cdoSendUsingPort (network connection) and not the SMTP server's pickup directory
(cdoSendUsingPickup).

Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification
Const cdoAnonymous = 0
Const cdoBasic = 1 ' clear text
Const cdoNTLM = 2 'NTLM
'Delivery Status Notifications
Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay

set objMsg = CreateObject("CDO.Message")
set objConf = CreateObject("CDO.Configuration")

Set objFlds = objConf.Fields
With objFlds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yourhost.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your-username"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your-password"
.Update
End With

strBody = "This is a sample message." & vbCRLF
strBody = strBody & "It was sent using CDO." & vbCRLF

With objMsg
Set .Configuration = objConf
.To = "test@paulsadowski.com"
.From = "me@my.com"
.Subject = "This is a CDO test message"
.TextBody = strBody
'use .HTMLBody to send HTML email.
.Addattachment "c:\temp\Scripty.zip"
.Fields("urn:schemas:mailheader:disposition-notification-to") = "me@my.com"
.Fields("urn:schemas:mailheader:return-receipt-to") = "me@my.com"
.DSNOptions = cdoDSNSuccessFailOrDelay
.Fields.update
.Send
End With

In real world usage you'll most likely want to load the text of the email from a file on your
computer. The sample code below shows you how to do this. The text can be either
plain text or HTML as needed.Our example assumes your text is in the file
C:\Temp\MyEmail.txt. This code loads the entire content of that file into a variable,
here named BodyText which you can then reference in your CDO code. We
assume BodyText is in the scope of your CDO code.

'These constants are defined to make the code more readable
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
'Open the file for reading
Set f = fso.OpenTextFile("c:\temp\MyEmail.txt", ForReading)
'The ReadAll method reads the entire file into the variable BodyText
BodyText = f.ReadAll
'Close the file
f.Close
Set f = Nothing
Set fso = Nothing

Once the text is loaded you can use it in your CDO code something like this...

objMessage.TextBody = BodyText
or
objMessage.HTMLBody = BodyText

Load Recipients from a Database As is the case with most thing in Windows there are many ways to accomplish a task. This is one method of many.
Our database is an Access format database that resides on the local disk. The table in our database that we are interested in is called Customers and each record consists of 4 fields named "ID", "Name", "Email", and "Customer", where ID is an autogenerated index, Name is the full name of our customer, Email is the customer's email address and Customer is their customer identification number.
We are only interested here in two fields, Name and Email.

ID	Name	Email	Customer
1	Bob Jones	bjones@test.com	12345
2	Jane Smith	jsmith@test.net	12346

Set OBJdbConnection = CreateObject("ADODB.Connection")
OBJdbConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\Acme Inc\Databases\Customers.mdb"
SQLQuery = "SELECT Name, Email FROM Customers"
Set Result = OBJdbConnection.Execute(SQLQuery)
if Not Result.EOF then
Do While Not Result.EOF
SendMail Result("Name"), Result("Email")
Result.MoveNext
Loop
end if
OBJdbConnection.Close

As you can see the code is simple. We create a database connection object then open the database and query it for the Name and Email fields of each customer. Those values are passed for each customer to a subroutine that sends the customer an email.

Sub SendMail(TheName, TheAddress)
Dim objMessage, Rcpt

Rcpt = Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">"
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "This Month's Sales" objMessage.From = """Acme Sales"" "
objMessage.To = Rcpt
objMessage.HTMLBody = TextBody
objMessage.Send

End Sub

If you are not accustomed to working with databases then this may have seemed a daunting task but as you can see from the code above, it's really quite simple.
We've already covered sending email so I'll just mention that this subroutine assumes the HTML body text is a variable called TextBody (see Loading email body text from a file)
Also we format the recipient's address in the standard format of "Name" for a more professional look to the recipient..

Remarks

As previously stated there are many ways to do this. I've presented one simple method here. Your own use may be with an ODBC connection; it may use mySQL or SQL Server; it may include personalization of the email body text and more. My intent here was to provide you with the basics to get you started.

Load data from an Excel Worksheet

There may be times when you want to generate an email using data from an application such as Excel. This is one simple illustration of how that could be done. In our example we will be using a Workbook with three columns starting at column A row 1. Each row represents one product in our inventory and the three columns contains the following data about each item: Part Number, Name of Part, Number of Items in Inventory. Graphically our Workbook looks like this:

Part	Name	Stock
4583586	Fliggalhopper	452
5898547	Looplonger	293

This particular script works by walking down each cell of column 1 till it finds an empty cell which it assumes is the end of the list of entries. If your file may contain empty cells then you can use the Worksheet's UsedRange.Rows.Count property to find the last row in which an entry is made. Your code would then use a for loop something like this:

rowLast = objSheet.UsedRange.Rows.Count
for x = rowStart to rowLast
' do stuff
next

Function GetData()
Dim x, strTemp, objExcel, objWB

Set objExcel = Wscript.CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open("c:\Acme Inc\Workbooks\Test.xls")
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Make Excel visible while debugging
objExcel.Visible = True

' This is the row of our first cell.
x = 1

do while objExcel.Cells(x, 1).Value <> ""
strTemp = strTemp & objExcel.Cells(x, 1).Value & _
Space(10 - Len(objExcel.Cells(x, 1).Value))
strTemp = strTemp & objExcel.Cells(x, 2).Value & _
Space(50 - Len(objExcel.Cells(x, 2).Value))
strTemp = strTemp & objExcel.Cells(x, 3).Value & vbCRLF
x = x + 1
loop

' This will prevent Excel from prompting us to save the workbook.
objExcel.ActiveWorkbook.Saved = True

' Close the workbook and exit the application.
objWB.Close
objExcel.Quit

set objWB = Nothing
set objExcel = Nothing

GetData = strTemp
End Function

' This is our main function.
Dim strBody

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Inventory report for " & Date
objMessage.From = "me@my.com"
objMessage.To = "bossman@my.com"
strBody = "Part" & Space(6) & "Item" & Space(46) & "Stock" & vbCRLF

' Here we call the function GetData to populate the body text.
strBody = strBody & GetData

objMessage.TextBody = strBody
objMessage.Send

The code above will produce an email that looks something like this:

To: bossman@my.com From: me@my.com Subject: Inventory report for 3/19/2005 Part Item Stock 4583586 Fliggalhopper 452 5898547 Looplonger 293

This sample sends a simple text email via GMail servers.

It's like any other mail but requires that you set the SMTP Port to 465 and tell CDO to use SSL

Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = """Me"" "
objMessage.To = "me@my.com"
objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication and SSL."

'==This section provides the configuration information for the remote SMTP server.

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "You@gmail.com"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "YourPassword"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465

'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

REFERENCES
http://www.paulsadowski.com/wsh/cdo.htm

Command Center

Friday, May 13, 2011

Windows VBScript To Send Email Using CDO

This is some sample message html.

Remarks

The code above will produce an email that looks something like this:

Labels

Blog Archive

My Blog List