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:
id | email | first_name | last_name |
1 | account@domain.com | John | Doe |
2 | bill.smith@company.com | Bill | Smith |
3 | Kate@site.com | Kate | Jones |
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:
Mail merge and bulk e-mail: Advanced Mail merge and bulk e-mail: Queuing