Mail merge and bulk e-mail (Advanced)

Visual Basic Code | ASP Code

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

Two main differences from Simple version of the sample: The code assumes the database is located in "C:\Docs\advanced_merge.mdb" file, and "emails" table looks like the following:
id email first_name last_name filename
1 account@domain.com John Doe 345.doc
2 bill.smith@company.com Bill Smith  
3 Kate@site.com Kate Jones 377.doc
Note: Some SMTP servers limit maximum number of sends per second (not per connection). E.g, you can only send 5 e-mails per second from the same IP address. In this case, you can use queuing and background delivery with MailBee Message Queue or MailBee.NET Queue system. See Queuing sample for details.

[Visual Basic]:

' 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

' Counter to limit number of sends per connection
Dim I

' 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  = 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:\Temp\smtp_log.txt"
objSMTP.ClearLog

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

' Specify SMTP server name
objSMTP.ServerName = "localhost"

' 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"

' 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\advanced_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

' Initialize counter to connect to SMTP
' server before sending any e-mails
I = 1

' Loop through the table
Do While Not rsEmails.EOF

  ' Connect to the server. We connect only one time
  ' per 10 sends and reuse existing connection for
  ' subsequent 9 e-mails.
  If I = 1 Then
    If Not objSMTP.Connect Then
      ' Notify user on connection error and exit
      MsgBox "Error #" & objSMTP.ErrCode & ", " & objSMTP.ErrDesc
      Exit Do
    End If
  End If

  ' Set all properties for each e-mail.
  ' This is required because we will reset
  ' the message later to free Attachments
  ' collection.

  ' 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 <sales@site.com>"
  objSMTP.Message.Subject = "Invitation"
  
  ' 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 body 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"))
    
  ' Add attachment if required
  If rsEmails("filename") <> "" Then
    objSMTP.Message.AddAttachment "C:\Data\" & rsEmails("filename")
  End If

  ' Try to send e-mail
  If Not objSMTP.Send Then
    ' Notify user on sending error
    MsgBox "Error #" & objSMTP.ErrCode & ", " & objSMTP.ErrDesc
  End If
    
  ' Reset all message properties to defaults and
  ' clear Attachments collection. If this method
  ' was not called, subsequent messages would also
  ' hold attachments from previously sent messages.
  objSMTP.ResetMessage
    
  ' Proceed with the next record in the table
  rsEmails.MoveNext

  ' Disconnect once 10-th e-mail is sent or passed
  ' the end of the database.
  '
  ' You may adjust number of 10 to the maximum
  ' "sends per connection" value allowed by your
  ' SMTP server.
  If I = 10 Or rsEmails.EOF Then

    ' Disconnect from SMTP server
    objSMTP.Disconnect

    ' Reinitialize counter to force Connect
    ' before next send
    I = 0
  End If

  ' Proceed with the next e-mail
  I = I + 1
Loop
  
' Close the table and the whole database
rsEmails.Close
objConn.Close

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

[ASP]:

<%
' 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

' Counter to limit number of sends per connection
Dim I

' 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:\Temp\smtp_log.txt"
objSMTP.ClearLog

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

' Specify SMTP server name
objSMTP.ServerName = "localhost"

' 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"

' 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\advanced_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

' Initialize counter to connect to SMTP
' server before sending any e-mails
I = 1

' Loop through the table
Do While Not rsEmails.EOF

  ' Connect to the server. We connect only one time
  ' per 10 sends and reuse existing connection for
  ' subsequent 9 e-mails.
  If I = 1 Then
    If Not objSMTP.Connect Then
      ' Notify user on connection error and exit
      Response.Write "Error #" & objSMTP.ErrCode & ", " & objSMTP.ErrDesc
      Exit Do
    End If
  End If

  ' Set all properties for each e-mail.
  ' This is required because we will reset
  ' the message later to free Attachments
  ' collection.

  ' 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 <sales@site.com>"
  objSMTP.Message.Subject = "Invitation"
  
  ' 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 body 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"))
    
  ' Add attachment if required
  If rsEmails("filename") <> "" Then
    objSMTP.Message.AddAttachment "C:\Data\" & rsEmails("filename")
  End If

  ' 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
    
  ' Reset all message properties to defaults and
  ' clear Attachments collection. If this method
  ' was not called, subsequent messages would also
  ' hold attachments from previously sent messages.
  objSMTP.ResetMessage
    
  ' Proceed with the next record in the table
  rsEmails.MoveNext

  ' Disconnect once 10-th e-mail is sent or passed
  ' the end of the database.
  '
  ' You may adjust number of 10 to the maximum
  ' "sends per connection" value allowed by your
  ' SMTP server.
  If I = 10 Or rsEmails.EOF Then

    ' Disconnect from SMTP server
    objSMTP.Disconnect

    ' Reinitialize counter to force Connect
    ' before next send
    I = 0
  End If

  ' Proceed with the next e-mail
  I = I + 1
Loop
  
' Close the table and the whole database
rsEmails.Close
objConn.Close

' Free database-related objects
Set rsEmails = Nothing
Set objConn = Nothing
%>
See Also:
Mail merge and bulk e-mail (Queuing)

 

Send feedback to AfterLogic
Copyright © 2002-2024, AfterLogic Corporation. All rights reserved.