[p]Because of PCI compliance, both xp_cmdshell and db_sendmail are disabled on our database servers. I needed to get some of my tasks to send email to me so I used CDOSYS.

The script supports both text and HTML email formats.[/p]

[p]Here is the base code:[/p]

CREATE PROCEDURE usr_sp_send_cdosysmail

@From_Addr VARCHAR(500) ,

@To_Addr VARCHAR(500) ,

@Subject VARCHAR(500) ,

@Body VARCHAR(8000) ,

@SMTPserver VARCHAR(25) = 'localhost',

@BodyType VARCHAR(10) = 'textbody'

AS

DECLARE @imsg INT

DECLARE @hr INT

DECLARE @source VARCHAR(255)

DECLARE @description VARCHAR(500)

EXEC @hr = sp_oacreate 'cdo.message', @imsg out

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

EXEC @hr = sp_oasetproperty @imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @SMTPserver

EXEC @hr = sp_oamethod @imsg, 'configuration.fields.UPDATE', NULL

EXEC @hr = sp_oasetproperty @imsg, 'to', @To_Addr

EXEC @hr = sp_oasetproperty @imsg, 'from', @From_Addr

EXEC @hr = sp_oasetproperty @imsg, 'subject', @Subject

-- If you are using html e-mail, use 'htmlbody' instead of 'textbody'.

EXEC @hr = sp_oasetproperty @imsg, @BodyType, @Body

EXEC @hr = sp_oamethod @imsg, 'send', NULL

EXEC @hr = sp_oadestroy @imsg

[p]How To Call The Procedure:[/p]

EXEC usr_sp_send_cdosysmail

@From_Addr='myaddr@myurl.com',

@to_Addr ='youraddr@yoururl.com',

@subject ='Formatted email from SQL',

@body ='<B>Test Email From SQL</B>

Please Ignore this <FONT COLOR=red>email.</FONT>',

@smtpserver ='localhost',

--

@bodytype ='HTMLBody'

-- @bodytype ='textbody'