Mail merge and bulk e-mail: Advanced
The sample sends multiple e-mails with attachments using single template and database.
Two main differences from Simple version of the sample:
- Supports e-mails which have optional parts such as attachments. E.g., some users will receive attachments while others will not
- Limits number of e-mails sent over the single connection session. Useful when your SMTP server imposes such limitation
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 system. 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
' 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 " 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 = "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 "
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
<%
' 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 " 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 = "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 "
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