Send 1000s of Emails Without Timeout

Download source - 33.99 KB

Introduction

I faced problems when sending 1000s of letters to members via a simple webpage, so I came up with a small application that can do this process without making the page timeout or fail sending. My application has the option to send emails via SQL Server or a simple SMTP Server.

Background

We had an application that is used to sending daily newsletters to members. Daily newsletters can be articles, ads, services... But suddenly, the members' number started to increase and at the number research above 10000 email. So the webform that was used to send emails to 1000 members started to timeout and give errors since it was looping for every email, adding options to each member and then sending the letters. So we needed another process that can run along with the same page, without making it timeout or block sending letters. The idea was as follows:

We start by creating the body of the letter that will be sent to members, select the option how to send (SMTP or SQL), then start the process of sending. At the end of the process, we can see the log file that resulted through the sending.

Using the Code

First, I started with configuring the SQL Server SMTP provider.

-- configuring sql smpt email sender
USE msdb
GO

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyAccount', @email_address = 'sender@provider.com', _
	@display_name = 'sender name', @mailserver_name = 'localhost'

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MyDBMail' 

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyDBMail', @account_name = 'MyAccount', @sequence_number = 1

The script is used to set up the SQL SMTP email sending. The following procedure is used to send the letters using the configured provider in SQL.

CREATE PROCEDURE [SendNewsletterMails]
(
    @Email varchar(max),
    @Body text,
    @Subject varchar(255)
)
as

EXEC msdb.dbo.sp_send_dbmail
@blind_copy_recipients=@Email,
@body= @Body, 
@subject = @Subject,
@body_format = 'HTML',
@profile_name = 'MyDBMail'

GO

Now my webpage has an option either to choose sending the emails via SMTP or SQL.

After setting the options of the email, parameters are sent to the process that will start running and sending the emails to the clients stored in the database.

At the end of the process, the pop up window will close showing that the process has ended successfully.

The log file will be generated at the end of the process showing:

If we are using the SQL Server option, we can check the history of sending emails from there. To check the history in SQL Server for sending emails, we can select the emails saved in Queue in SQL server:

use msdb
go

--to get successful sending emails
SELECT * FROM sysmail_allitems where sent_status = 'sent'

--to get successful failed 
SELECT * FROM sysmail_allitems where sent_status = 'failed'