Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Using Xp_sendmail With the Recipients Generated From a Query

By Kunal Das, (first published: 2001/07/16)

Xp_sendmail is a great utility that SQL Server provides in order to e-mail messages directly from SQL Server, by using a valid MAPI profile. However, its syntax is rather stringent and if you type in the email address yourself it works fine. Consider

Xp_sendmail ‘’, ‘Hello’

But Say you wanted to extract email addresses from a table I will call “TEST_TABLE”, in a database called Test. and use the results of this query as the address list for xp_sendmail. How would you do it?

Here is a simple solution using T-SQL which you can use as a stored procedure:


Use Test 

Declare @MyRecipients nvarchar (255)

Declare @MyMessage nvarchar (255)

Set @MyMessage = ‘Your timesheet is overdue, please send it ASAP.'

Declare MyCursor Cursor For 

Select email_address From Test_table  Where Status = 0

Open MyCursor

Fetch Next From MyCursor Into @MyRecipients 

While @@Fetch_Status = 0


Print @MyRecipients 

Fetch Next From MyCursor Into @MyRecipients

Exec Master.dbo.xp_sendmail @MyRecipients, @MyMessage


Close MyCursor

Deallocate MyCursor

We simply get each result of the query one at a time, call xp_sendmail and send the result out. It is very convenient when you want to send reminders to people whose addresses you have in a database, maybe a sales database or some such table. This little piece of code is very useful. I wrote this code and modified it so that I could send out reminders every Friday to salespersons who had not returned their timesheets. Their addresses were queried from a table, based upon their timesheet status being ‘0’, i.e. their timesheet had not been returned. I added this as a job to be run every Friday at 8am, which saved me from building a VB Application that would do the same thing, however not so elegantly. Fetch gets each record one a time, and then we email the message to the current recipient using xp_sendmail, this whole process is enclosed in a while loop, so each recipient will be cycled through.

You could modify this code so that each address is concatenated and only one email is sent, however you would have to change your select statement to something like this

Select email_Address+’;’ From Test_Table Where Status = 0

This is because Outlook requires multiple addresses to be separated by semicolons, as does the syntax of xp_sendmail. Also, do not worry about the last address having a semicolon behind it as Outlook will ignore it.


Total article views: 23416 | Views in the last 30 days: 5
Related Articles

query results by email using xp_sendmail

query results by email using xp_sendmail


TSQL Challenge 41 - Extract email addresses from text data

This challenge involves extracting email addresses from a table that contains text data downloaded f...


A Pattern for Email Address storage and retrieval

Email addresses are very prevalent in IT systems and often used as a natural primary key. The repeti...


xp_sendmail to multiple recipients

I'm trying to send email to a group of people using the logic provided by following article:  http:/...


Subscription email rights

Change the email address

advanced querying    
sql server 7    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones