發郵件的SMTP配置(smtpserver = ''192.168.1.100')

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:

***********************************************************************/
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.
--
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(").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(").Value', @smtpserver
--
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(").Value','1'

EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(").Value', @sendusername
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields(").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 ' 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
BEGIN
SELECT @output = ' Error Source: ' + @source
PRINT @output
SELECT @output = ' Error Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END

-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
-------

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[@more@]