Here's my stab at it.

<
/* Created by free online sql formatter: http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm */

/*This sets up a quick test table*/
IF object_id('DJmailDetails_T') > 0
  DROP TABLE djmaildetails_t

CREATE TABLE djmaildetails_t (
  id           INT  NOT NULL  IDENTITY( 1  , 1  ),
  firstname    VARCHAR(30)  NOT NULL,
  lastname     VARCHAR(30)  NOT NULL,
  emailaddress VARCHAR(100)  NOT NULL,
  sendmail     BIT  NOT NULL  DEFAULT (0) )

INSERT INTO djmaildetails_t
           (firstname,
            lastname,
            emailaddress,
            sendmail)
VALUES     ('dave',
            'jackson',
            'me@nonworkingExample.co.uk',
            1)

INSERT INTO djmaildetails_t
           (firstname,
            lastname,
            emailaddress,
            sendmail)
VALUES     ('fred',
            'bloggs',
            'you@nonworkingExample.co.uk',
            1)

INSERT INTO djmaildetails_t
           (firstname,
            lastname,
            emailaddress,
            sendmail)
VALUES     ('joe',
            'smith',
            'him@nonworkingExample.co.uk',
            0)

INSERT INTO djmaildetails_t
           (firstname,
            lastname,
            emailaddress,
            sendmail)
VALUES     ('mike',
            'jones',
            'her@nonworkingExample.co.uk',
            1)
/*This selects from above table*/

DECLARE  @recipients VARCHAR(4000)

SELECT @recipients = Coalesce(@recipients + ';','') + emailaddress
FROM   djmaildetails_t
WHERE  sendmail = 1

PRINT @recipients -- show what we got

PRINT '' -- a blank line
/***************************************************************************************************/
/*The next two variables are normally set in theerror handling of each 'bit' of DML in your SP/Script*/

DECLARE  @Error_status INT

DECLARE  @Error_message VARCHAR(128)

SET @Error_status = -1

SET @Error_message = 'failure updating something!' --something apposite
                     /*This should be in the error handler of the script or SP*/

ERRORHANDLER:
DECLARE  @sql VARCHAR(8000)

DECLARE  @mailMessage VARCHAR(128)

DECLARE  @mailSubject VARCHAR(128)

DECLARE  @Procedure_Name SYSNAME -- Holds the name of the currently executing procedure
SET @Procedure_Name = Object_name(@@PROCID) -- Set the procedure name in the variable

/*the next two lines would not be called above normally, so needs to be commented out here.
declare @recipients varchar(4000)
Select @recipients = COALESCE(@recipients+';' , '') + emailAddress from DJmailDetails_T where sendMail = 1
*/

/*This sends the message*/

SET @mailMessage = 'Table update failure' --something apposite

SET @mailSubject = 'Error in ' + Isnull(@Procedure_Name,'Ad-hoc Query')

SET @sql = 'exec master.dbo.xp_sendmail ''' + @recipients + ''',@message = ''' + @mailMessage + 
''',@query = ''select ''''' + @error_message + ''''' as ErrorMessage,' + Isnull(CAST@Error_status AS VARCHAR),' ') + ' as ErrorNumber'',@subject = ''' + @mailSubject + 
''',@dbuse = ''master'',@attach_results = false,@no_output = true,@width = 8000'

PRINT (@sql)  --change this from print to an exec to 'really' do it.
--exec (@sql) --or uncomment this line

/*
Script to drop the table when finished testing

if object_id('DJmailDetails_T') > 0
drop table DJmailDetails_T

*/

I liked this so much I posted it on my site at http://glossopian.co.uk

Dave J

How would you add the subject to the message instead of getting the default of "SQL SERVER MESSAGE"?  I've tried adding the @subject = 'Test Subject' but get an error "Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'."  I then tried to add DECLARE @MYSUBJECT nvarchar (255) and then SET @MYSUBJECT = 'Test Subject' and then

Exec Master.dbo.xp_sendmail  @mysubject ,@MyRecipients, @MyMessage  END END .  That gives an ODBC error 170 (42000)

Any help is appreciated.

My code is below,  my issue is when this executes,  lets say the query brings back 2 email address,  instead of sending one to each name it send 2 to each name.  How/where can I fix this??

CREATE   PROCEDURE usp_email_ccb

AS

DECLARE @TITLE VARCHAR(500)
DECLARE @DESCRIPTION VARCHAR(500)
DECLARE @REQUIREMENTS VARCHAR(500)
Declare @MyRecipients nvarchar (255)
Declare @NEWMessage nvarchar (255)
Declare @NEWSubject nvarchar (255)
DECLARE @RECORD_NUM NCHAR(2)
Declare MyCursor Cursor For

Select LOGINID From USERS  Where TASK='CCB'

Open MyCursor

Fetch Next From MyCursor Into @MyRecipients

While @@Fetch_Status = 0

BEGIN

Print @MyRecipients

Fetch Next From MyCursor Into @MyRecipients

select  @record_num=[id],
 @title=[title],
 @description=[description],
 @REQUIREMENTS=[REQUIREMENTS]
 from display a,(select max(id) mxid from display) b where a.id=b.mxid

SET @NEWSUBJECT='YOU HAVE AN IT REQUEST TO APPROVE!!!  THIS IS A TEST EMAIL FOR THE WAP TOOL, JUST DISREGARD!!!'
SET @NEWMESSAGE='TITLE:  '+@TITLE + " " + +CHAR(13)+ +CHAR(13)+ 'DESCRIPTION:  '+@DESCRIPTION +  " " + +CHAR(13)++CHAR(13)+ 'REQUIREMENTS:  '+@REQUIREMENTS +  " "++CHAR(13)++CHAR(13)+ + 'Click on this link to view your request.  http://localhost/WAP/db/ba2btpeb2.asp?a=GETRECORD&ID='+@RECORD_NUM

Exec Master.dbo.xp_sendmail
 @MyRecipients,
 @SUBJECT =@NEWSUBJECT,
 @MESSAGE=@NEWMESSAGE

End

Close MyCursor

Deallocate MyCursor
GO