Send mail using CDO - DOC

Document Sample
Send mail using CDO - DOC Powered By Docstoc
					          Send mail using CDO (Windows 2000, XP, 2003) part 2

Load the text of the e-mail from a file

In certain situations you may want to load the text of the e-mail from a file.

The sample code below shows you how to load the plain text file C:\Temp\MyEmail.txt
(the text can be either plain text or HTML as needed). This code loads the entire content
of that file into a variable, here named BodyText which you can then reference in your
CDO code.

' Constants to make the code more readable
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Dim strBody

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
strBody = f.ReadAll
' Close the file
Set f = Nothing
Set fso = Nothing

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Simple plain text CDO example"
objMessage.From = ""
objMessage.To = ""
objMessage.TextBody = strBody ' Use .HTMLBody to send from a HTML

Load Recipients from a Database

Please note: This is just an example and is therefore not intended to be used as-is.

The database could be any number of formats, MS Access, MySQL etc., although the
example below is for a MS Access database file. The table we have in our database that
we are going to use is called MailingList, with each record consists of 4 fields named "
ID", "Name", "E-mail", and "Customer", where ID is an auto incremented index,
Customer is their customer identification number, Name is the full name of our customer
and E-mail is the customer's e-mail address (although we are only interested in Name
and E-mail).

Database records:

   ID           Customer                    Name                         E-mail
   1            12345678                    Andrew Peters      
   2            23456789                    Peter Andrews      
Set objDBConnection = CreateObject("ADODB.Connection")
objDBConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};
SQLQuery = "SELECT Name, E-mail FROM MailingList"
Set Result = objDBConnection.Execute(SQLQuery)
if Not Result.EOF then
  Do While Not Result.EOF
    SendMail Result("Name"), Result("E-mail")
end if

As you can see the code is very simple. We create a database connection object, then
open the database and query it for the Name and E-mail fields of each record. The
results for each record are passed to a subroutine that sends the customer an e-mail.

Sub SendMail(TheName, TheAddress)
Dim strRcpt

strRcpt = Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">"
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "You are on a mailing list"
objMessage.From = """Mailing list"" <>"
objMessage.To = strRcpt
objMessage.TextBody = "Would you like to buy something else." '
Or use the load e-mail from file example above

End Sub

As you can see from the code above, it is quite a simple task to use a database with
VBScripts. The recipient is also created using the full name to give a more professional

Load data from an Excel Worksheet

Most company mail will contain data generated from a database or from a spreadsheet.
The example below shows how this could be done using Microsoft Excel.

In the example we will be using an Excel workbook with three columns starting at
column A row 1. Each row represents one product in our inventory and the three
columns contain the following data about each item: Part number, name of the part and
the stock number of the part.

Excel records:

   PartNumber             PartName                                       StockNumber
   125501                 An important part                              880
   136572                 A more important part                          881

The script works by walking down each cell of column 1 until it finds an empty cell which
it assumes is the end of the list of entries.

Please note: 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.

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

Set objExcel = Wscript.CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open("C:\ComapanyData\Parts.xls")
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Make Excel visible (helps to debug)
objExcel.Visible = True

' Our first cell is 1, although we could start on any row
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

' Pretend that the workbook has been saved to prevent Excel
' from prompting us to save the workbook
objExcel.ActiveWorkbook.Saved = True

' Close the workbook and exit the application

Set objWB = Nothing
set objExcel = Nothing

GetData = strTemp ' Return the string
End Function

' Main body of the script
Dim strBody

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Inventory report for " & Date
objMessage.From = ""
objMessage.To = ""
strBody = "PartNumber" & Space(6) & "PartName" & Space(17) &
"StockNumber" & vbCRLF

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

objMessage.TextBody = strBody

The code above will produce an e-mail that looks something like this:

Subject: Stock report for May 2006

PartNumber            PartName                            StockNumber
125501                An important part                   880
136572                A more important part               881

I hope this has been of some help to you.

Shared By: