SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
USE Master
GO
if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[send_mail]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
drop procedure [dbo].[send_mail]
GO
CREATE PROCEDURE [dbo].[send_mail]
   @From varchar(1000) ,  --发件人
   @To varchar(1000) ,   --收件人
   @Subject nvarchar(128)='''', --标题
   @Body nvarchar(4000) ='''' --正文
with encryption

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

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

***********************************************************************/
   AS
   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)
------
declare @smtpserver varchar(200),@sendusername varchar(200) ,@sendpassword varchar(200)      
--please set the values before excute the stored procedure--@@@@@@@@
set @smtpserver  = ''192.168.1.100''  --smtp服务器
set @sendusername=''master''       --发送认证:用户名
set @sendpassword=''password''       --发送认证:密码

if @sendusername='''' or  @sendpassword=''''
begin
 raiserror 50000 ''please set the @sendusername and  @sendpassword values before excute the stored procedure''
 return -1
end

--replace the quotation marks
set @Subject=replace(@Subject,'''''''','''''''''''')
set @Body=replace(@Body,'''''''','''''''''''')

--************* 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'', @smtpserver
--
 EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value'',''1'' 

   EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value'', @sendusername
   EXEC @hr = sp_OASetProperty @iMsg, ''Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value'', @sendpassword
 
-- 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

-- If you are using HTML e-mail, use ''HTMLBody'' instead of ''TextBody''.
   EXEC @hr = sp_OASetProperty @iMsg, ''TextBody'', @Body
   EXEC @hr = sp_OAMethod @iMsg, ''Send'', NULL
 if @@error<>0 or @hr<>0
 begin
  raiserror 55000 ''<send_mail> Error: send mail failed.''
 end
 else 
 begin
  print ''Success: send mail ok.''
 end

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