Using sp_send_dbmail on queried addresses. 2

[XopherS]

I'm trying to set up a query, where it returns a list of email addresses, and SQL sends a form letter to each address. I don't know how to do that, though, or whether it's even possible.

Help!

 

[ptheriault]

Yes you can do that. You could attach your form or include it in the body of the email.Check sp_send_dbmail in BOL.
HEre is the syntax.

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
    [ , [ @recipients = ] 'recipients [ ; ...n ]' ]
    [ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
    [ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
    [ , [ @subject = ] 'subject' ] 
    [ , [ @body = ] 'body' ] 
    [ , [ @body_format = ] 'body_format' ]
    [ , [ @importance = ] 'importance' ]
    [ , [ @sensitivity = ] 'sensitivity' ]
    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
    [ , [ @query = ] 'query' ]
    [ , [ @execute_query_database = ] 'execute_query_database' ]
    [ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
    [ , [ @query_attachment_filename = ] query_attachment_filename ]
    [ , [ @query_result_header = ] query_result_header ]
    [ , [ @query_result_width = ] query_result_width ]
    [ , [ @query_result_separator = ] 'query_result_separator' ]
    [ , [ @exclude_query_output = ] exclude_query_output ]
    [ , [ @append_query_error = ] append_query_error ]
    [ , [ @query_no_truncate = ] query_no_truncate ]
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[XopherS]

Well, right, but I want this email to go out to a list of recipients, that list being found in the results of a query. So I run this query (sample):

SELECT EmailAddress
FROM Contacts
WHERE HasBeenEmailed = 0

I get 5 emails in a query grid as a result. I want to send on email to each of these people. I've already gotten it where I can send a basic email to myself; I want to send this to multiple people in that list.

 

[ptheriault]

you will have to do something like this.

DECLARE @email varchar(1000)

WHILE @@ROWCOUNT > 0
SET ROWCOUNT 1

SELECT @email = EmailAddress
FROM Contacts
WHERE HasBeenEmailed = 0

EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'Default',
    @recipients = @email,     @subject = 'Whatever',
    @importance = 'High'


update Contacts
SET HasBeenEmailed = 1
WHERE EmailAddress = @email

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bikerboy718]

Another way you may want to do it is like this:

DECLARE @TBL_EmailAddress AS TABLE 
	(RowNum INT IDENTITY(1,1)
	, EmailAddress VARCHAR(500)
	)
DECLARE @MaxRow INT, @RowCnt INT, @Email VARCHAR(500)  

INSERT	@TBL_EmailAddress (EmailAddress)
SELECT	EmailAddress
FROM	Contacts
WHERE	HasBeenEmailed = 0

SELECT @MaxRow = COUNT(RowNum), @RowCnt = 1 
FROM @TBL_EmailAddress

WHILE (@RowCnt <= @MaxRow)
BEGIN

SELECT @Email = EmailAddress
FROM @TBL_EmailAddress
WHERE RowNum = @RowCnt

EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'Default',
    @recipients = @email,     @subject = 'Whatever',
    @importance = 'High'

UPDATE	Contacts
SET		HasBeenEmailed = 1
WHERE	EmailAddress = @email

SET @RowCnt = @RowCnt + 1 
END

With Great Power Comes Great Responsibility!!!

Michael

 

[XopherS]

Holy cats, that last one worked!

Thanks so much!