• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

How to queue emails sent using CDO.Message object

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.

Cheers
Sam
0
Samm1502
Asked:
Samm1502
  • 3
  • 2
1 Solution
 
jorge_torizResearch & Development ManagerCommented:
Use DatabaseMail that come with sql server... this enqueue the email requests that you need through service broker.
0
 
Mark WillsTopic AdvisorCommented:
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
    @recipients=@recipientaddress,
    @profile_name=N'MRW_SQL_Mail',
    @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
     @recipients=@recipientAddress,
     @body=@body,
     @subject=@subject,
     @profile_name='MRW_SQL_mail',
     @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

0
 
Mark WillsTopic AdvisorCommented:
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...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Samm1502Author Commented:
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.

Cheers
Sam
0
 
Mark WillsTopic AdvisorCommented:
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).
0
 
Samm1502Author Commented:
Sorry thought I had closed this one off already!  Many thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now