How to queue emails sent using CDO.Message object

Posted on 2008-10-16
Last Modified: 2013-11-05
Hi there

I need to send email notifications from my stored procedure and am using the cdosys object CDO.Message to do this.

My problem is that I will need to cycle round and send anywhere up to 100 emails within my procedure as the procedure itself deletes/updates records and then needs to tell the relevant user these changes affect.  So is it reasonable to just build and send a new email every time or is there some facility for queuing mails as they are created and then just sending them once at the end?  I am obviously concerned that emailing each user in turn will be too expensive timewise.

Question by:Samm1502
  • 3
  • 2
LVL 15

Expert Comment

ID: 22732022
Use DatabaseMail that come with sql server... this enqueue the email requests that you need through service broker.
LVL 51

Accepted Solution

Mark Wills earned 500 total points
ID: 22733045
If you are using 2005 and/or 2008 then sp_send_dbmail is much more friently than the "old" way...

declare @CC varchar(100)

declare @BCC varchar(100)

Declare @MailID int

Declare @hr int

Declare @SenderName varchar(100)

Declare @SenderAddress varchar(100)

Declare @RecipientName varchar(100)

Declare @RecipientAddress varchar(100)

Declare @Subject varchar(200)

Declare @Body varchar(8000)

Declare @MailServer varchar(100)

set @MailServer = 'localhost'

set @SenderName='Mark Wills'

set @SenderAddress='mark.wills@email.address' 

set @RecipientName = 'Mark Wills' 

set @RecipientAddress = 'mark.wills@email.address'

set @Subject='SQL Mail Test'

set @body='Hello, this is a test email from SQL Server'

set @CC  = null

set @BCC  = null

-- new way:

EXEC msdb.dbo.sp_send_dbmail



    @body=@body ;

-- cdo / old way:

EXEC @hr = sp_OACreate 'CDO.Message', @MailID OUT

EXEC @hr = sp_OASetProperty @MailID, 'From',@senderaddress

EXEC @hr = sp_OASetProperty @MailID, 'TextBody', @Body

EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC

EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC

EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @MailID, 'To', @recipientAddress

EXEC @hr = sp_OAMethod @MailID, 'Send', NULL

EXEC @hr = sp_OADestroy @MailID

-- more advanced new way with query

EXEC msdb.dbo.sp_send_dbmail





     @query = 'select name, address, phone from mrwtemp..tbl_customer_email_list',

     @execute_query_database = 'mrwtemp',

     @attach_query_result_as_file = 1,                    -- change to zero to include in body

     @query_attachment_filename = 'your_customer_list.txt'

Open in new window

LVL 51

Expert Comment

by:Mark Wills
ID: 22733075
Oh, forgot the important bit... Using the new way, you can run a query very easily - so long as all your security is OK (need to add mail user to SQL logins), so, could easily "stockpile" the list of changes and then run a query...
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


Author Comment

ID: 22739697
Thanks Mark. Does the new way have a means of queueing the mails sent out becaue I am not sending a list of updates to one user but a single update to many users so want to make sure I don't have to wait for each mail to be sent before the next one can be processed.

LVL 51

Expert Comment

by:Mark Wills
ID: 22739735
Not exactly...  

Still need to formulate a query for the list of updates, but, you will need to loop through each recipient if they are to get their "own" updates. They are seperate parameters so need to match the changes (ie query) for that recipient.

But if the changes (ie query) are "global" to all users, then can simply put all the users into the recipient list (seperate by semicolons).

Author Closing Comment

ID: 31506664
Sorry thought I had closed this one off already!  Many thanks.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now