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?

Improve company productivity with a Business Account.Sign Up

x
 
RiteshShahConnect With a Mentor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT @EmailRecipient =  EmailAddress from dbo.table
0
 
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
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.

All Courses

From novice to tech pro — start learning today.