create Procedure dbo.sp_send_mail
 @sender varchar(100),
 @sendername varchar(100)='',
 @serveraddress varchar(255)='mail.yourmailserver.net',
 @recipient varchar(255),
 @recipientBCC varchar(200)='',
 @recipientCC varchar(200)='',
 @attachment varchar(100) ='',
 @subject varchar(255),
 @mailbody text
/*
Stored procedure using Dimac w3 JMail by Mats Cederholm, Global Communications WWW AB

Sending email by instantiating w3 JMail instead of SQL mail.
*/
As

/*
Declares variables for input/output from w3 JMail and errormessages
*/
declare @object int,
 @hr int,
 @rc int,
 @output varchar(400),
 @description varchar (400),
 @source varchar(400)

/*
Set all values to w3 JMail needed to send the email
*/

exec @hr = sp_OACreate 'jmail.smtpmail', @object OUT
exec @hr = sp_OASetProperty @object, 'Sender', @sender
exec @hr = sp_OASetProperty @object, 'ServerAddress', @serveraddress
exec @hr = sp_OAMethod @object, 'AddRecipient', NULL , @recipient
exec @hr = sp_OASetProperty @object, 'Subject', @subject
exec @hr = sp_OASetProperty @object, 'Body', @mailbody

/*
Set some more values, depending on the value of the variables
*/

if not(@attachment='')
 exec @hr = sp_OAMethod @object, 'Addattachment', NULL , @attachment
 print @attachment
if not(@recipientBCC='')
 exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC
if not(@recipientCC='')
 exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC
if not(@sendername='')
 exec @hr = sp_OASetProperty @object, 'SenderName', @sendername

/*
Call execute to send the email
*/
exec @hr = sp_OAMethod @object, 'execute', NULL

/*
Catch possible errors
*/
exec @hr = sp_OAGetErrorInfo @object, @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

/*
Kill the object
*/
exec @hr = sp_OADestroy @object