Mail merge and bulk e-mail: Simple

The sample sends multiple e-mails using single template and database.

For each message, actual values taken from the database substitute placeholders in the template. Thus, all e-mails will have the same look but will be still personalized.

The sample code connects to the MS Access database using ADO's Connection object. Then ADO's Recordset object is filled with the values from "emails" table. Each record of this table is then used to produce a single e-mail.

The code assumes the database is located in C:\Docs\simple_merge.mdb file, and "emails" table looks like the following:

Note 1: The sample sends all e-mails over single SMTP connection (this provides maximum performance). However, some SMTP servers allow sending only a few e-mails over the same connection. See Advanced sample for details.

Note 2: To reduce response time of your mass-sending application, you can tell MailBee to send e-mail in the background. See Queuing sample for details.

' Mailer object
Dim objSMTP

' ADO Connection object
Dim objConn

' ADO Recordset object
Dim rsEmails

' Merge patterns (Body and "To:", all other fields remain
' unchanged for all e-mails in the mailing list)
Dim strBodyPattern, strToPattern

' Define body pattern
strBodyPattern = "Hello %%FIRST_NAME%%, " & vbCrLf & _
  "You are welcome to visit our site at:" & vbCrLf & _
  "http://www.site.com" & vbCrLf & vbCrLf & _
  "Sincerely, Site Team"

' Define "To:" pattern (will look like
' "John Doe " in e-mail)
strToPattern = "%%FIRST_NAME%% %%LAST_NAME%% <%%EMAIL%%>"

' Create SMTP mailer component
Set objSMTP  = CreateObject("MailBee.SMTP")

' Enable logging SMTP session into a file. If any
' errors occur, the log can be used to investigate
' the problem.
objSMTP.EnableLogging = True
objSMTP.LogFilePath = "C:\smtp_log.txt"
objSMTP.ClearLog

' Unlock mailer component
objSMTP.LicenseKey = "put your license key here"

' Specify SMTP server name
objSMTP.ServerName = "smtp.site.com"

' Comment next 3 lines if your SMTP server does not
' require SMTP authentication
objSMTP.AuthMethod = 2
objSMTP.UserName = "your mail account name"
objSMTP.Password = "your mail account password"

' Connect to the server. We use single connection
' for sending all e-mails in the database.
If objSMTP.Connect Then
  ' Plain-text e-mail. Change to 1 to send HTML e-mail
  objSMTP.BodyFormat = 0
  
  ' Specify "From:" and "Subject:". They remain unchanged
  ' for all the e-mails.
  objSMTP.Message.FromAddr = "Site Team "
  objSMTP.Message.Subject = "Invitation"

  ' Create ADO Connection object
  Set objConn  = CreateObject("ADODB.Connection")

  ' Connect to Access database
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\Docs\simple_merge.mdb;"

  ' Create ADO Recordset object
  Set rsEmails = CreateObject("ADODB.Recordset")
  
  ' Open "emails" table. This table is
  ' actual data source for the merge
  rsEmails.Open "emails", objConn
  
  ' Loop through the table
  While Not rsEmails.EOF

    ' Merge body pattern and actual data
    ' from the database
    objSMTP.Message.BodyText = strBodyPattern
    objSMTP.Message.BodyText = Replace(objSMTP.Message.BodyText, "%%FIRST_NAME%%", rsEmails("first_name"))

    ' Merge "To:" pattern and actual data
    ' from the database
    objSMTP.Message.ToAddr = strToPattern
    objSMTP.Message.ToAddr = Replace(objSMTP.Message.ToAddr, "%%FIRST_NAME%%", rsEmails("first_name"))
    objSMTP.Message.ToAddr = Replace(objSMTP.Message.ToAddr, "%%LAST_NAME%%", rsEmails("last_name"))
    objSMTP.Message.ToAddr = Replace(objSMTP.Message.ToAddr, "%%EMAIL%%", rsEmails("email"))

    ' Try to send e-mail
    If Not objSMTP.Send Then
      ' Notify user on sending error
      MsgBox "Error #" & objSMTP.ErrCode & ", " & objSMTP.ErrDesc
    End If
    
    ' Proceed with the next record in the table
    rsEmails.MoveNext
  Wend
  
  ' Close the table and the whole database
  rsEmails.Close
  objConn.Close

  ' Free database-related objects
  Set rsEmails = Nothing
  Set objConn = Nothing

  ' Disconnect from SMTP server
  objSMTP.Disconnect
Else
  ' Notify user on connection error
  MsgBox "Error #" & objSMTP.ErrCode & ", " & objSMTP.ErrDesc
End If

<%
' Mailer object
Dim objSMTP

' ADO Connection object
Dim objConn

' ADO Recordset object
Dim rsEmails

' Merge patterns (Body and "To:", all other fields remain
' unchanged for all e-mails in the mailing list)
Dim strBodyPattern, strToPattern

' Define body pattern
strBodyPattern = "Hello %%FIRST_NAME%%, " & vbCrLf & _
  "You are welcome to visit our site at:" & vbCrLf & _
  "http://www.site.com" & vbCrLf & vbCrLf & _
  "Sincerely, Site Team"

' Define "To:" pattern (will look like
' "John Doe <j.doe@domain.com>" in e-mail)
strToPattern = "%%FIRST_NAME%% %%LAST_NAME%% <%%EMAIL%%>"

' Create SMTP mailer component
Set objSMTP  = Server.CreateObject("MailBee.SMTP")

' Enable logging SMTP session into a file. If any
' errors occur, the log can be used to investigate
' the problem.
objSMTP.EnableLogging = True
objSMTP.LogFilePath = "C:\smtp_log.txt"
objSMTP.ClearLog

' Unlock mailer component
objSMTP.LicenseKey = "put your license key here"

' Specify SMTP server name
objSMTP.ServerName = "smtp.site.com"

' Comment next 3 lines if your SMTP server does not
' require SMTP authentication
objSMTP.AuthMethod = 2
objSMTP.UserName = "your mail account name"
objSMTP.Password = "your mail account password"

' Connect to the server. We use single connection
' for sending all e-mails in the database.
If objSMTP.Connect Then
  ' Plain-text e-mail. Change to 1 to send HTML e-mail
  objSMTP.BodyFormat = 0
  
  ' Specify "From:" and "Subject:". They remain unchanged
  ' for all the e-mails.
  objSMTP.Message.FromAddr = "Site Team "
  objSMTP.Message.Subject = "Invitation"

  ' Create ADO Connection object
  Set objConn  = Server.CreateObject("ADODB.Connection")

  ' Connect to Access database
  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=C:\Docs\simple_merge.mdb;"

  ' Create ADO Recordset object
  Set rsEmails = Server.CreateObject("ADODB.Recordset")
  
  ' Open "emails" table. This table is
  ' actual data source for the merge
  rsEmails.Open "emails", objConn
  
  ' Loop through the table
  While Not rsEmails.EOF

    ' Merge body pattern and actual data
    ' from the database
    objSMTP.Message.BodyText = strBodyPattern
    objSMTP.Message.BodyText = Replace(objSMTP.Message.BodyText, "%%FIRST_NAME%%", rsEmails("first_name"))

    ' Merge "To:" pattern and actual data
    ' from the database
    objSMTP.Message.ToAddr = strToPattern
    objSMTP.Message.ToAddr = Replace(objSMTP.Message.ToAddr, "%%FIRST_NAME%%", rsEmails("first_name"))
    objSMTP.Message.ToAddr = Replace(objSMTP.Message.ToAddr, "%%LAST_NAME%%", rsEmails("last_name"))
    objSMTP.Message.ToAddr = Replace(objSMTP.Message.ToAddr, "%%EMAIL%%", rsEmails("email"))

    ' Try to send e-mail
    If Not objSMTP.Send Then
      ' Notify user on sending error
      Response.Write "Error #" & objSMTP.ErrCode & ", " & objSMTP.ErrDesc & "<br>"
    End If
    
    ' Proceed with the next record in the table
    rsEmails.MoveNext
  Wend
  
  ' Close the table and the whole database
  rsEmails.Close
  objConn.Close

  ' Free database-related objects
  Set rsEmails = Nothing
  Set objConn = Nothing

  ' Disconnect from SMTP server
  objSMTP.Disconnect
Else
  ' Notify user on connection error
  Response.Write "Error #" & objSMTP.ErrCode & ", " & objSMTP.ErrDesc & "<br>"
End If
%>

See also: