Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2707
  • Last Modified:

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

Hello

I'm using the stored procedure msdb.dbo.sp_send_dbmail in sql server 2005 to send out emails.

I need to be able to send emails to email addresses stored in a table called emailadd. The msdb.dbo.sp_send_dbmail stored procedure uses the @recipients parameter to guage who the email is sent to, for example:

@recipients = 'me@me.com; myfriend@friend.com,

rather than hard coding these values i want the values to be derived from the emailadd table so that if the email addresses change in the table, the email will go to the right people.

Any ideas?

Thanks
0
pedley123
Asked:
pedley123
  • 8
  • 4
  • 3
  • +3
3 Solutions
 
chapmandewCommented:
are you sending one email for each record in the emailadd table, or the same email to all of them?
0
 
pedley123Author Commented:
at the moment this would be the same email to all email addresses in the emailadd table
0
 
pedley123Author Commented:
increased to 500, can any one assist?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
AaronAbendCommented:
create procedure sendmails as
begin
declare getemails cursor for
select emailaddress from mytable
declare @onerecipient varchar(40) -- make variables large enough for your needs
declare @allrecipients varchar(2000)

open getemails;
fetch next from getemails into @onerecipient;

while (@@fetch_status=0)
begin
        set @allrecipients = @allrecipients+@onerecipient+';' --- i do not think a trailing semicolon will be a problem - if it is you need to eliminate with logic
        fetch next from getemails into @onerecipient;
end
close getemails;
deallocate getemails;

-- execute the send email command here with @allrecipients...
end
   
-- this is one approach (sorry - it is past midnight in my timezone and i do not have time to test right now)
-- another would be to execute the emailing process within the cursor loop.
-- if you create  an exception block make certain that the cursor is closed and deallocated regardless of the logic path
0
 
Mark WillsTopic AdvisorCommented:
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 :)

0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
Mark WillsTopic AdvisorCommented:
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...

Open in new window

0
 
AaronAbendCommented:
Kudos/points to mark_wills - avoids the cursor. It really was past midnight - I must have been really tired!
0
 
Mark WillsTopic AdvisorCommented:
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...  
0
 
ZberteocCommented:
And here is THE COOLEST way to concatenate column values whithout looping through and without using any fancy tricks like XML and stuff. This should be a classic by now.

Thanks Mark for your testing code:
-- 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
select @recipient=isnull(@recipient,'')+email_address+';' from test_email_recipients
 
-- get rid of the last ; might not be necessary
select @recipient=left(@recipient, len(@recipient)-1)
 
-- step 5 print it out just to make sure
select @recipient as Recipients

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
AaronAbendCommented:
Zberteoc  - I did not know you could do that with mssql.  I will take that knowledge as my reward. Thanks.
 
0
 
Mark WillsTopic AdvisorCommented:
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

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
And just so I might also get a nice little "thats cool" instead of "it was late" from AaronAbend, or even "I learnt something new", what is really cool is being able to loop through each distinct company and provide a single email to all the recipients that belong to that company - all in ONE "select" statement and ONE "exec", no cursors, no loops... while it does use some elements of "classic", don't think you can do that without using the XML version - is it cool yet ?
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')
 
-- if you want to acually run it, need to change your profile_name accordingly... ie change MY_MAIL_PROFILE to whatever is your real profile, and change the above e-mail addresses to something real, and remove the comments from below...
 
declare @sql varchar(max)
select @sql = isnull(@sql,'') + 'exec msdb.dbo.sp_send_dbmail @profile_name=''MY_MAIL_PROFILE'',@recipients='''+
              (select email_address + ';' from #test_email_recipients e where c.company = e.company for xml path('')) + 
              ''',@subject=''Email Listings for '+company+''',@body = ''Dear Staff member of '+company+
              ','+replicate(char(13)+char(10),2)+'You Belong to Company '+company+replicate(char(13)+char(10),2)+
              'Cheers,'+char(13)+char(10)+'Mark Wills'';'+char(13)+char(10)
              from #test_email_recipients c group by company
print @sql
 
--exec (@sql)  -- currerntly less than 500 characters
 
drop table #test_email_recipients

Open in new window

0
 
pedley123Author Commented:
thanks to all for providing the solutions to this
0
 
AaronAbendCommented:
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.  
0
 
Mark WillsTopic AdvisorCommented:
@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 ?
0
 
hessfirm01Commented:
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' ;

Open in new window

0
 
hessfirm01Commented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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