A friend of mine took the Microsoft example proc and produced a more robust version. It defaults to using the servername from @@servername if one is not provided, and it can send file attachments:

use master

go

CREATE PROCEDURE [dbo].[sp_sendmail_CDOSYS]

@To varchar(100) ,

@Subject varchar(100)=" ",

@Body varchar(4000) =" ",

@Importance int = 1, -- 0=low, 1=normal, 2=high

@CC varchar(100)= " " ,

@Bcc varchar(100)= " " ,

@Attachments varchar(8000)=NULL, /* seperated by ; */

@HTMLFormat int = 0,

@From varchar(100)= null ,

@Server varchar(255) = null,

@UserName varchar(255) = null,

@password varchar(255) = null

/*********************************************************************

Created by : Corey Embry

Description: Use CDOSYS to Send Mail using smtp

Base Code was obtained from MS Article Q312839

 

This stored procedure takes the parameters and sends an e-mail.

All the mail configurations are hard-coded in the stored procedure.

Comments are added to the stored procedure where necessary.

References to the CDOSYS objects are at the following MSDN Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

exec sp_sendmail_CDOSYS

@To = 'Coreye@avalamarketing.com'

, @Subject = 'CDOSYStest' -- Optional

, @Body ='this is a test' -- Optional

, @Importance int = 1 -- Optional

, @CC = 'mickey@disney.com' -- Optional

, @Bcc = 'Minnie@disney.com' -- Optional

, @Attachments = 'c:\test.txt' -- Optional ; Delimited

, @HTMLFormat = 0 -- Optional Default Text

-- , @From = 'Coreye@avalamarketing.com' -- Optional

, @Server='avalafs1' -- Optional

-- , @UserName= -- Optional

-- , @Password= -- Optional

***********************************************************************/

AS

SELECT @Server = isnull(@Server, @@SERVERNAME)

Select @From = isnull(@From, @@SERVERNAME)

Declare @iMsg int

Declare @hr int

Declare @int int

Declare @tmp varchar(8000)

Declare @source varchar(255)

Declare @description varchar(500)

Declare @output varchar(1000)

--************* Create the CDO.Message Object ************************

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************

-- This is to configure a remote SMTP server.

-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @Server

if @UserName is not null and @password is not null

begin

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/SendUserName").Value', @UserName

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value', @Password

end

-- Save the configurations to the message object.

-- EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.

EXEC @hr = sp_OASetProperty @iMsg, 'To', @To

EXEC @hr = sp_OASetProperty @iMsg, 'From', @From

EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @iMsg, 'CC', @CC

EXEC @hr = sp_OASetProperty @iMsg, 'BCC', @BCC

/* if there are any attachments */

if len(@Attachments)>0

begin

while len(@Attachments)>0

begin

set @int = charindex(';',@Attachments)

if @int > 0

begin

set @tmp = left(@Attachments,@int-1)

set @Attachments = right(@Attachments,(len(@Attachments)-@int))

end else

begin

set @tmp = @Attachments

set @Attachments = ''

end /* Add the Attachment */

Print @tmp

EXEC @hr= sp_OAMethod @iMsg, 'AddAttachment', NULL, @tmp

end

end

 

 

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

if @HTMLFormat <> 1

begin

EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body

end

else

begin

EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

end

-- Save the configurations to the message object.

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0

select @hr

BEGIN

EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

IF @hr = 0

BEGIN

SELECT @output = ' Source: ' + @source

PRINT @output

SELECT @output = ' Description: ' + @description

PRINT @output

END

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

RETURN

END

END

-- Do some error handling after each step if you have to.

-- Clean up the objects created.

EXEC @hr = sp_OADestroy @iMsg

GO

This technique is an oldie but a goodie.

One of Clinton Herring's contributions to "Script of the Day" a while back (I can't remember the date, but the script is dated 08/22/2001) does this too, and, might I add, is a bit more thorough than both examples posted above (Perhaps it gets overlooked because the script title is potentially misleading). 

In any event, Clinton's version of this idea uses an OLE automation implementation of the CDOSYS dll, includes error handling, a help/usage message, a clever technique to verify attachments, and, IMHO, is very well-factored.

In addition to any attachments you specify, Clinton's script can also perform a user-defined SQL Query (passed as a parameter to the SPROC), pipe the query results out to a text file (to the drive wih the most free space!), and attach the report to the email!  A great idea for automating server reports and emailing them to the DBA, or, generating application-specific reports and emailing them to your business stakeholders.

See what you think!

SQL 2K SMTP mail on Windows 2K
http://www.sqlservercentral.com/scripts/contributions/510.asp

Script Rating   Total number of votes [86]
By: Clinton Herring

SP_SQLSMTPMail is an OLE automation implementation of the CDOSYS dll for Windows 2000 which utilizes a network SMTP server rather than an Exchange server/Outlook client. The stored procedure functions similar to xp_sendmail including the ability to run a query and attach the results. No MAPI profile is required. It is also a working, detailed example of an OLE automation implementation. This update corrects a problem when the proc is called twice in the same batch without an intervening 'Go'. The cause is the sp_OAStop. It needs to be removed or commented out. The stated method of operation in the BOL is incorrect. 11/5/2002 Some people have reported errors when running this stored procedure. They have not been failures of the stored procedure. They are errors related to improper configuration/permissions for the SQL server to use the local network SMTP relay server for either internal or out going mail. 11/20/2002 Fixes a problem related to the OSQL call to send an attached query. OSQL was not releasing its lock on the first output file it created until the session ended, hence, calling the proc in a cursor or loop prevented subsequent query attachments. 04/09/2003 Comment correction.

Best,
Ken

I recently saw your article at http://www.orcsweb.com/articles/sendmailsql.aspx and it solved my problem perfectly.

Just wanted to say thanks.

I am using very simple VBscript to achieve the same thing, and attachements are allowed as well after small modification

--------------------------------------

Set oMsg = CreateObject("CDONTS.NewMail")
 oMsg.From = "abc"
 oMsg.To = "abc"
 oMsg.Subject = "you message"
 oMsg.Body = "you message"
 oMsg.Send
 Set oMsg = Nothing

-------------------------------------

Julia

I like the suggestions, and the ability to attach a SQL query output to an email.

I know the following works - EXEC ('master..xp_cmdshell ''isql /o' + @filename + ' /d' + @database + ' /Q"' + @SQLquery + '" /E''')

But the only problem with the above is that you need SYSADMIN access to run XP_CMDSHELL

I also do NOT want to use XP_SENDMAIL as it uses MAPI .

Is there any other way to attach SQL queries to an email ??

I was using the sqlsmtpmail code and attaching a step to all of my jobs to exec the proc on failures.  Problem is, how can I put the Job Name and/or step name that failed in the subject and/or body of the emails?  I have everything else working.

Thanks all!