populating @recipients value using msdb.dbo.sp_send_dbmail from a database table

are you sending one email for each record in the emailadd table, or the same email to all of them?

at the moment this would be the same email to all email addresses in the emailadd table

increased to 500, can any one assist?

G'Day,

Enjoy these types of problems, not sure why I haven't seen it before actually...

Can use the e-mail addresses from the table, and can either string them together, or, send individual e-mails. But, identifying which email goes to what email address might be your challenge. Or, are you saying that all emails via this procedure should always go to the recipients found in the email address table...

If you could give some thought as to how to identify an "appropriate" recipient, and / or, how you envisage seeing that happen - I did see your comment that every email would go to everyone in the table, but that is also a small deviation from your original question...

basically the approach will be to either string together the recipients as a single receipient, or do a "loop" to send individually. Kind of depends on how many recipients there could be... @recipients is varchar(max) so that is about 2 gig of addresses :)

Sorry aronabend - did not see your post - again forgot a quick refresh... The cursor is a good idea - should declare with READ_ONLY and FAST_FORWARD...

And using that as an example, aronabend is building up the address so it is one email to lots of recipients - could also do the individual sp_send_dbmail for each recipient (rather than build a string, do the actual send_dmail) if you want to personalise a bit more...

There is a really cool way of stringing together columns without looping through - if you do not need to personalise each e-mail.

Below, I create a little test table, populate it and then use the "trick" - check it out...

-- step 1 create a test table
 
create table test_email_recipients (id int identity, email_address varchar(100))
 
-- step 2 populate
 
insert test_email_recipients (email_address) values ('email_1')
insert test_email_recipients (email_address) values ('email_2')
insert test_email_recipients (email_address) values ('email_3')
 
-- step 3 - declare a variable - thout not really needed, could populate @recipient directly
 
declare @recipient varchar(max)
 
-- step 4 string together all the email addresses in the table
 
set @recipient = (select email_address + ';' from test_email_recipients for xml path(''))
 
-- step 5 print it out just to make sure
 
print @recipient
 
-- in sp_send_dbmail you can use directly...
 
exec sp_send_dbmail @recipients = (select email_address + ';' from test_email_recipients for xml path('')),
                    @subject = 'Test bulk e-mail',
 
etc...

Select allOpen in new window

Kudos/points to mark_wills - avoids the cursor. It really was past midnight - I must have been really tired!

Question is not over yet, and I do prefer "personalising" e-mails - cannot do that with a string, so have to loop around, and may as well be a cursor...  

Yep, that is indeed pretty cool... But, we can probably agree to debate if it is still the coolest (classic yes).

and I have also stated something incorrectly : you cannot use mine directly in the "exec msdb.dbo.sp_send_dbmail"  without using some dynamic sql, so just as easy to set up the variable first.

and the last semicolon does not matter.

Zberteoc  - I did not know you could do that with mssql.  I will take that knowledge as my reward. Thanks.
 

OK, OK, then this is why I think the for XML is probably the coolest - it is more adaptable/flexible because it implicitly becomes a string... and do not know why people are so put off by the "xml" keyword...

Try this using the "classic" approach:

create table #test_email_recipients (id int identity, email_address varchar(100), company varchar(100))
 
insert #test_email_recipients (email_address,company) values ('email_1','EE')
insert #test_email_recipients (email_address,company) values ('email_2','EE')
insert #test_email_recipients (email_address,company) values ('email_3','BB')
 
select distinct company,(select email_address + ',' from #test_email_recipients e where c.company = e.company for xml path('')) as co_emails
from #test_email_recipients c
 
select 'The following emails can be dynamically strung together : '+(select email_address + ',' from #test_email_recipients for xml path(''))
 
drop table #test_email_recipients

Select allOpen in new window

thanks to all for providing the solutions to this

I always appreciate points - and I think I gave a good example of how to use a cursor (which might help someone in a situation that is more complex than the stated problem) but nobody looking at this thread and conclude that my solution was a good solution to this problem. Cursors are quite slow in TSQL and not a good idea when other approaches are possible.  

@aaronabend: apart from not getting a "gosh mark, I have learned something new" or suitably gooey accolade as was afforded zberteoc (see ID:22753412), I am pleased to be sharing the points with you in this solution - it is a legitimate approach, it works, and ever so humble, you have joined in the conversation and definitely assisted me (at least) to arrive at the "accepted" solution. In fact depending on the response to personalised / individualised e-mails, then cursors might be the easiest way to solve. So, what are you saying ?

Hi Mark and Aaron,
Wondered if you could help with a similar question.

I'm have been able to send a View that display's multiple records in a nice table based on certain criteria. I use sp_send_dbmail and MSSQL 05

What I can't figure out, is how to send to the email address associated with those records, not a static one, then loop through and send the next result set to the next recipient.

In the code below, i've declared a static @recipients...but i want it to be pulled from the joined table, and i want it to loop to the next record set. So in the joined tables, 1 email will be associated with, let's say, 10 records...etc.

Any ideas? Thanks!

I think this is a cursor thing...not sure. Any ideas?

DECLARE @tableHTML  NVARCHAR(MAX) ;
 
SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'</tr>' +
    CAST ( ( SELECT td = m.ln,       '',
                    td = m.Lot, '',
                    td = m.Br, '',
                    td = m.Baf, '',
                    td = m.Lp, ''
              FROM mydb.dbo.table as m
              WHERE subd = 'village grove'
              ORDER BY lp ASC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
 
 
EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'local',
	@recipients='myemail@gmail.com',
    @subject = 'Current Homesites',
    @body = @tableHTML,
    @body_format = 'HTML' ;

Select allOpen in new window

by the way...i posted the question earlier here:
https://www.experts-exchange.com/questions/24487212/SQL-Cursor-and-sp-send-dbmail.html