Sending Emails with Pervasive EZScript

We use emails in our Pervasive Data Integrator processes to alert ourselves when there are errors in a process or when a process has successfully completed. Often we're running a query and sending results in an email.

The out of the box way to accomplish this using EZScript, is by creating an "SendMail-SMTP" DJComponent for the SMTP object, a DJMessage for the email body and, optionally, a DJMessage for an attachment. This works well for simple applications, but fell short for us when needing some additional functionality like CC and BCC, multiple recipients, etc. 

A much more powerful way to send emails using EZScript is to use CDO (Collaboration Data Objects), a Microsoft technology that is meant to make sending messages (i.e. emails) relatively simple. There are many, many examples on the web on how to use the CDO.Message object with VBScript. This post is mainly to alert you to how you can use it with EZScript.

I wrote most of the script below for use with other projects over the years and it required very little, if any, changes to use as an EZScript. It supports sending emails to multiple recipients, CC and BCC recipients and adding multiple file attachments. There are five functions that make up the script, with the first being the AdvancedCreateCDOMessage function:

' Added Pervasive server's IP to Exchange Server 2003; 
' Must allow anonymous SMTP hosts to relay mail by adding their IP address(es) in 
' SMTP Virtual Server Properties | Access tab | Relay Restrictions

Option Explicit

Private mcdoMessage As Object

' constant
Private CrLf
CrLf = Chr(13) & Chr(10)

' Send Using
Private cdoSendUsingPickup, cdoSendUsingPort
cdoSendUsingPickup = 1  ' Send message using the local SMTP service pickup directory. 
cdoSendUsingPort = 2  ' Send the message using the network (SMTP over the network). Required for Delivery Notification. 

' Type of Authentication
Private cdoAnonymous, cdoBasic, cdoNTLM
cdoAnonymous = 0  ' Do not authenticate
cdoBasic = 1    ' Basic (clear-text) authentication
cdoNTLM = 2   ' NTLM

' Delivery Status Notifications
Private cdoDSNDefault, cdoDSNNever, cdoDSNFailure, cdoDSNSuccess, cdoDSNDelay, cdoDSNSuccessFailOrDelay
cdoDSNDefault = 0   ' None
cdoDSNNever = 1   ' None
cdoDSNFailure = 2   ' Failure
cdoDSNSuccess = 4   ' Success
cdoDSNDelay = 8   ' Delay
cdoDSNSuccessFailOrDelay = 14  ' Success, failure or delay

Public Function AdvancedCreateCDOMessage( _
 toName, toAddress, _
 ccName, ccAddress, _
 bccName, bccAddress, _
 fromName, fromAddress, _
 subject, body, attachedFileName, _
 userName, password, _
 smtpServer, smtpPort)

 Dim cdoConfig As Object
 Dim configSchema, attachments(), i

 If Len(smtpPort) == 0 Then
  smtpPort = "25"
 End If

 Set cdoConfig = CreateObject("CDO.Configuration")
 
 configSchema = "http://schemas.microsoft.com/cdo/configuration/"

 ' Type of authentication: NONE, Basic (Base64 encoded), NTLM
 cdoConfig.Fields.Item(configSchema & "smtpauthenticate") = cdoBasic
 'Your UserID and password on the SMTP server
 cdoConfig.Fields.Item(configSchema & "sendusername") = userName
 cdoConfig.Fields.Item(configSchema & "sendpassword") = password

 ' Remote SMTP server configuration 
 cdoConfig.Fields.Item(configSchema & "sendusing") = 2 'cdoSendUsingPort
 cdoConfig.Fields.Item(configSchema & "smtpserverport") = smtpPort
 cdoConfig.Fields.Item(configSchema & "smtpserver") = smtpServer 
 ' Use SSL for the connection (False or True)
 cdoConfig.Fields.Item(configSchema & "smtpusessl") = False
 ' Connection Timeout in seconds
 cdoConfig.Fields.Item(configSchema & "smtpconnectiontimeout") = 60

 cdoConfig.Fields.Update

 toAddress = FormatAddresses(toName, toAddress)
 fromAddress = FormatAddresses(fromName, fromAddress)
 ccAddress = FormatAddresses(ccName, ccAddress)
 bccAddress = FormatAddresses(bccName, bccAddress)

 Set mcdoMessage = CreateObject("CDO.Message")

 mcdoMessage.Configuration = cdoConfig

 mcdoMessage.To = toAddress
 mcdoMessage.From = fromAddress  ' Chr(34) & TheName & Chr(34) & "<" & TheAddress & ">"
 mcdoMessage.Cc = ccAddress
 mcdoMessage.Bcc = bccAddress
 mcdoMessage.Subject = subject 
 mcdoMessage.TextBody = body
 'mcdoMessage.HTMLBody = body
 'mcdoMessage.Fields("urn:schemas:mailheader:disposition-notification-to") = "me@my.com"
 'mcdoMessage.Fields("urn:schemas:mailheader:return-receipt-to") = "me@my.com" 
 'mcdoMessage.DSNOptions = cdoDSNSuccessFailOrDelay
 'mcdoMessage.Fields.Update

 If Len(attachedFileName) <> 0 Then
  ReDim attachments(UBound(Split(attachedFileName, ";")))
  attachments = Split(attachedFileName, ";")
  For i = 0 To UBound(attachments)
   If Len(attachments(i)) <> 0 Then 
    AddAttachment(Trim(attachments(i)))
   End If
  Next
 End If

 Set cdoConfig = Nothing
 
End Function

The next function, FormatAddresses, takes an array of names and address and formats them for use with the message object. This is called for each type of address (i.e. To, From, CC and BCC):

Function FormatAddresses(emailName, emailAddress)
 Dim addresses(), names(), i

 If Len(emailName) <> 0 Then
  ReDim names(UBound(Split(emailName, ";")))
  ReDim addresses(UBound(Split(emailAddress, ";")))

  If UBound(names) == UBound(addresses) Then
   names = Split(emailName, ";")
   addresses = Split(emailAddress, ";")

   For i = 0 To UBound(addresses)
    If Len(Trim(names(i))) <> 0 Then
     addresses(i) = Chr(34) & Trim(names(i)) & Chr(34) & " <" & Trim(addresses(i)) & ">"
    End If
   Next
  End If

  Return Join(addresses, ";")
 Else
  Return emailAddress
 End If

End Function

After the CDO.Message object is created, you can then call the AddAttachment function below, repeatedly, for as many attachments as you need to add:

Public Function AddAttachment(filePath)
 
 If FileExists(filePath) Then
  mcdoMessage.AddAttachment filePath
 Else
  mcdoMessage.TextBody = mcdoMessage.TextBody & CrLf & "Attached file did not exist: " & filePath
 End If

End Function

Finally, once the message is complete, we can send it using the AdvancedSendCDOMessage function:

Public Function AdvancedSendCDOMessage()
 Dim x

 mcdoMessage.Send

 x = LogMessage("INFO", "Sent email to: " & mcdoMessage.To)

 Set mcdoMessage = Nothing

End Function

Alternately, we can avoid directly using all of the above functions and send a quick email, using some defaults, with the SendCDOMessage function (note, rather than hard coding the defaults, you should use global macros that you have set up in Pervasive:

Public Function SendCDOMessage(toAddress, ccAddress, subject, body, attachedFileName)
 Dim fromName, fromAddress, userName, password, smtpServer, smtpPort

 ' TODO: point the following at global macros
 fromName = "Pervasive"
 fromAddress = "DoNotReply@yourdomainnamehere.com"
 userName = "PervasiveUser"
 password = "PervasiveUserPassword"
 smtpServer = "myserver.domain.local"
 smtpPort = ""  ' defaults to "25"

 AdvancedCreateCDOMessage( _
  "", toAddress, _
  "", ccAddress, _
  "", "", _
  fromName, fromAddress, _
  subject, body, attachedFileName, _
  userName, password, _
  smtpServer, smtpPort)
 
 AdvancedSendCDOMessage()

End Function

You can call the script using something like the following:

SendCDOMessage("me@mydomain.com", _
    "", _
    "Data Integration Job Status", _
    "This email was sent from Pervasive's EZ Script.", _
    "\\serverfileshare\myfolder\test.txt")

Hopefully the above gets you started with sending your own emails using CDO from Pervasive.