Send Mass Emails using Database Queue in ASP

In previous section, I introduced how to send email EASendMail Service Queue. In this section, I will introduce how to send mass emails with advanced database queue in ASP.

Introduction

Although EASendMail service provides a faster way to send email in background, but there are thousands of emails in a task, the SendMailToQueue will be invoked for thousands times, obviously it is not effect way.

Therefore, EASendMail service provides a more effective way to send mass emails. In short, you just need to submit your database connection and record set once, EASendMail service will pick up the record set in background and send email to each record one by one. It is very useful to send mass emails in ASP web application.

send email using database queue in ASP/VBScript

To better understand the database queue, we need to create three tables in your SQL database like this:

CREATE TABLE [dbo].[rcpts](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [email] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_rcpts] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[errorlog](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [email] [nvarchar](128) NULL,
    [server] [nvarchar](50) NULL,
    [errorcode] [nvarchar](50) NULL,
    [errordescription] [nvarchar](255) NULL,
 CONSTRAINT [PK_errorlog] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sentlog](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [server] [nvarchar](50) NULL,
    [email] [nvarchar](128) NULL,
 CONSTRAINT [PK_sentlog] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Then insert two records in table ‘rcpts’ like this:

SQL table sample

Note

Remarks: All of samples in this section are based on first section: Send email in a simple VB 6.0 project. To compile and run the following example codes successfully, please click here to learn how to create the test project and add reference of EASendMail to your project.

[ASP Example - Send mass emails with EASendMail Service Database Queue]

The following example codes demonstrate how to send mass emails using email queue + database in VBScript/ASP.

Note

To get the full sample projects, please refer to Samples section.

<%
' The following example codes demonstrate sending email message using email queue + database
' To get full sample projects, please download and install EASendMail on your machine.
' To run it correctly, please change SMTP server, user, password, sender, recipient value to yours

Const ConnectNormal = 0
Const ConnectSSLAuto = 1
Const ConnectSTARTTLS = 2
Const ConnectDirectSSL = 3
Const ConnectTryTLS = 4

Dim oSmtp
Set oSmtp = Server.CreateObject("EASendMailObj.Mail")
oSmtp.LicenseCode = "TryIt"

' Set your sender email address
oSmtp.FromAddr = "test@emailarchitect.net"
' Set email subject
oSmtp.subject = "simple email from ASP queue"

' If you want to EASendMail service send the email after 10 minutes, please use the following code.
' oSmtp.Date = DateAdd("n", 10, Now())

' change it to your sql server address, database, user and password
' The server/instance name syntax used in the server option is the same for all SQL Server connection strings.
' e.g.: Server=serveraddress\instancename;
' EASendMail will use the following connection to connect to the database,
' the syntax is same as ADO connection object.
oSmtp.AddHeader "X-Data-Connection", _
 "Driver={SQL Native Client};" & _
 "Server=serveraddress;Database=database;Uid=user;Pwd=password;"

' For more connection string
' MS SQL Server 2000
'"Driver={SQL Server};Server=localhost;Database=pubs;Uid=sa;Pwd=asdasd;"

' MS SQL Server 2005
'"Driver={SQL Server Native Client};Server=localhost;Database=pubs;Uid=sa;Pwd=asdasd;"
' MS SQL Server 2005 Native Provider
'"Provider=SQLNCLI;Server=serveraddress;Database=database;Uid=user;Pwd=password;"

' MS SQL Server 2008
'"Driver={SQL Server Native Client 10.0};Server=localhost;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
' MS SQL Server 2008 Native Provider
'"Provider=SQLNCLI10;Server=serveraddress;Database=database;Uid=user;Pwd=password;"

' MS SQL Server 2012
'"Driver={SQL Server Native Client 11.0};Server=localhost;Database=myDataBase;Uid=myUsername;Pwd=myPassword;"
' MS SQL Server 2012 Native Provider
'"Provider=SQLNCLI11;Server=serveraddress;Database=database;Uid=user;Pwd=password;"

' EASendMail will select the fields by the following sql statement
' before sending email,
' then pick the recipient address from specified field.
oSmtp.AddHeader "X-Sql-Select", "SELECT uid, name, email FROM Recipients"

' Pick "name" field as the recipient name and "email" field as the recipient address.
' You can also use {$var_srecord:fieldname} to pick any field in X-Sql-Select statement
' and put it to subject, bodytext, then EASendMail will replace it automatially.
oSmtp.DisplayTo = """{$var_srecord:name}"" <{$var_srecord:email}>"
oSmtp.AddHeader "X-Rcpt-To", "{$var_srecord:email}"

' EASendMail service will execute the following sql statement on
' every email was sent successfully.
oSmtp.AddHeader "X-Sql-OnSentSuccess", _
"INSERT INTO sentlog (server, email) VALUES('{$var_server}', '{$var_rcptaddr}')"

' EASendMail service will execute the following sql statement on
' every email could not be sent.
oSmtp.AddHeader "X-Sql-OnSentError", _
"INSERT INTO errorlog(email, server, errorcode, errordescription ) " & _
"VALUES('{$var_rcptaddr}', '{$var_server}', '{$var_errcode}', '{$var_errdesc}')"

Dim bodytext As String
bodytext = "Hi {$var_srecord:name}, " & Chr(13) & Chr(10)
bodytext = bodytext & "Send email with queue." & Chr(13) & Chr(10) & Chr(13) & Chr(10)
bodytext = bodytext & "From:Tester" & Chr(13) & Chr(10)
bodytext = bodytext & "To:{$var_srecord:email}" & Chr(13) & Chr(10) & Chr(13) & Chr(10)
bodytext = bodytext & "Your id in database is {$var_srecord:uid}." & Chr(13) & Chr(10)
oSmtp.bodytext = bodytext

' {$var_srecord:uid} {$var_srecord:name} {$var_srecord:email} in
' body text will be replaced by EASendMail automatically.

' Your SMTP server address
oSmtp.ServerAddr = "smtp.emailarchitect.net"

' User and password for ESMTP authentication, if your server doesn't require
' User authentication, please remove the following codes.
oSmtp.UserName = "test@emailarchitect.net"
oSmtp.Password = "testpassword"

' ConnectTryTLS means if server supports SSL/TLS connection, SSL/TLS is used automatically
oSmtp.ConnectType = ConnectTryTLS

' If your server uses 587 port
' oSmtp.ServerPort = 587

' If your server uses 25/587/465 port with SSL/TLS
' oSmtp.ConnectType = ConnectSSLAuto
' oSmtp.ServerPort = 25 ' 25 or 587 or 465

Response.Write "start to send email ..."
If oSmtp.SendMailToQueue() = 0 Then
    Response.Write "email was sent to queue successfully!"
Else
    Response.Write "failed to send email with the following error:" & oSmtp.GetLastErrDescription()
End If
%>

With above codes, no matter how many records in your table, SendMailToQueue is only invoked once and EASendMail service will pick up records in background and send the email based on each record to recipient one by one. It also inserts the results back to “errorlog” and “sentlog” tables automatically. You can open EASendMail Service Manager to monitor the queue and view Journal -> System Error to check if there is error with your database connection and SQL statement.

Database Server Driver

In X-Data-Connection header, you should specify a database driver to connect database. You can open “Control Panel” -> “Administrative Tools” - > “ODBC Data Sources” - “Drivers” to check current installed database drivers.

odbc drivers

Common SQL Driver Download

If SQL Server is installed on a remote server, and you don’t have SQL driver installed on local machine, then you need to download and install corresponding driver on local machine.

MS Access Database x64 Driver Download

Microsoft Access Database Engine 2010 Redistributable

See Also

Send Email in SQL Server Stored Procedure - Tutorial

Next Section

At next section I will introduce how to send email using Exchange Web Service (EWS) in VB6.

Appendix

Comments

If you have any comments or questions about above example codes, please click here to add your comments.