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 ‘kdas@it-enterprises.com’, ‘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
BEGIN
Print @MyRecipients
Fetch Next From MyCursor Into @MyRecipients
Exec Master.dbo.xp_sendmail @MyRecipients, @MyMessage
End
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.
Enjoy.
