How to assign the value of a table field to sp_send_dbmail @recipients

i have a value in Database.dbo.table.EmailAddress that i'd like to use for sp_send_dbmail @recipients

i know my code is right except for where the email address assignment is done... because if i specify an email address in the assignment to @recipients, it works.  however, it doesn't work when i try to select a value from a table, and so i know there is something wrong with my code in that section.
use Database
GO
 
... some stuff is done
 
DECLARE @EmailRecipient VARCHAR(30);
SELECT @EmailRecipient = 'SELECT EmailAddress from dbo.table';
 
... more stuff is done
 
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailRecipient,
  @subject = 'Subject',
  @body = '@tableHTML',
  @body_format = 'HTML',
  @profile_name='Email';

Open in new window

LVL 44
zephyr_hex (Megan)DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
SELECT @EmailRecipient =  EmailAddress from dbo.table
0
RiteshShahCommented:
are you sure you will have only one value return from dbo.table? if yes, than below will work or/else will have unexpected out going email.
use Database
GO
 
... some stuff is done
 
DECLARE @EmailRecipient VARCHAR(30);
SELECT @EmailRecipient = EmailAddress from dbo.table;
 
... more stuff is done
 
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailRecipient,
  @subject = 'Subject',
  @body = '@tableHTML',
  @body_format = 'HTML',
  @profile_name='Email';

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zephyr_hex (Megan)DeveloperAuthor Commented:
to test, i changed line 7 to:
SELECT @EmailRecipient = 'SELECT distinct EmailAddress from dbo.table where EmailAddress="name@domain.com" ';

and SQL is still posting this error in the mail logs:
The mail could not be sent to the recipients because of the mail server failure.... The specified string is not on the form required for an email-address.

is there a way for me to see what is being returned from the SELECT statement?  when i use the SELECT statement as a query, i am getting 1 value returned.
0
zephyr_hex (Megan)DeveloperAuthor Commented:
ok, i figured this out.  i created a view that lists each email address and used a cursor to iterate through the list of email addresses.

here is the code i added...
USE [Database]
GO
 
DECLARE @EmailRecipient VARCHAR(50);
DECLARE db_cursor CURSOR FOR
SELECT EmailAddress from dbo.NewViewWithListOfAddresses;
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @EmailRecipient;
 
WHILE @@FETCH_STATUS=0
BEGIN
 
...stuff is done
 
EXEC msdb.dbo.sp_send_dbmail @recipients=@EmailAddress,
    @subject = 'Subject',
    @body = @tableHTML,
    @body_format = 'HTML',
@profile_name='Email';
FETCH NEXT FROM db_cursor INTO @EmailRecipient;
END
 
CLOSE db_cursor;
DEALLOCATE db_cursor;

Open in new window

0
zephyr_hex (Megan)DeveloperAuthor Commented:
this solution pointed out the problem i was having.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.