Send Mass Emails using Database Queue in Delphi

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 Delphi.

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 Delphi 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.

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

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

Note

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

unit Unit1;
interface
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, EASendMailObjLib_TLB; // add EASendMail unit
type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

const
  ConnectNormal = 0;
  ConnectSSLAuto = 1;
  ConnectSTARTTLS = 2;
  ConnectDirectSSL = 3;
  ConnectTryTLS = 4;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  oSmtp : TMail;
begin
  oSmtp := TMail.Create(Application);
  oSmtp.LicenseCode := 'TryIt';

  // Set your sender email address
  oSmtp.FromAddr := 'test@emailarchitect.net';
  // Set email subject
  oSmtp.Subject := 'simple email from Delphi project';

  // 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}'')');

  oSmtp.BodyText := 'Hi {$var_srecord:name}, ' + #13#10
    + 'Send email with queue.' + #13#10 + #13#10
    + 'From:Tester' + #13#10
    + 'To:{$var_srecord:email}' + #13#10 + #13#10
    + 'Your id in database is {$var_srecord:uid}.' + #13#10;

  // {$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 := 587; // 25 or 587 or 465

  ShowMessage('start to send email ...');

  // You just need to change SendMail method to SendMailToQueue method in
  // your Delphi application, then EASendMail uses queue to send email.
  // SendMailToQueue can be used in windows application as well.
    if oSmtp.SendMailToQueue() = 0 then
      ShowMessage('email was sent to queue successfully!')
    else
      ShowMessage('failed to send email with the following error: '
      + oSmtp.GetLastErrDescription());

end;

end.

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 Delphi.

Appendix

Comments

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