sql server發送郵件

CREATE PROCEDURE usp_sendmail(@server varchar(20), --服務器@From varchar(1000),@To varchar(1000),@Bcc varchar(500)='',@Subject nvarchar(400)='',@Body nvarchar(4000),@errnum int output)AS

Declare @object int Declare @hr int

set @errnum=0 --正確的

EXEC @hr = sp_OACreate 'cdo.Message', @object OUT

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

EXEC @hr = sp_OASetProperty @object, 'Configuration.fields('http://schemas.microsoft.com/cdo/configuration/usemessageresponsetext').Value','1'--EXEC @hr = sp_OASetProperty @object, 'Configuration.fields('http://schemas.microsoft.com/cdo/configuration/LanguageCode').Value',' 2052'

--下面三條語句是smtp驗證，如果服務器需要驗證，則必須要這三句，你需要修改用戶名和密碼EXEC @hr = sp_OASetProperty @object, 'Configuration.fields('http://schemas.microsoft.com/cdo/configuration/smtpauthenticate').Value','0' EXEC @hr = sp_OASetProperty @object, 'Configuration.fields('http://schemas.microsoft.com/cdo/configuration/sendusername').Value','' EXEC @hr = sp_OASetProperty @object, 'Configuration.fields('http://schemas.microsoft.com/cdo/configuration/sendpassword').Value',''

--EXEC @hr = sp_OASetProperty @object, 'Configuration.fields('http://schemas.microsoft.com/cdo/configuration/sendusername').Value','lihonggen0' --EXEC @hr = sp_OASetProperty @object, 'Configuration.fields('http://schemas.microsoft.com/cdo/configuration/sendpassword').Value','xxx'

EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', nullEXEC @hr = sp_OASetProperty @object, 'To', @ToEXEC @hr = sp_OASetProperty @object, 'BodyPart.Charset','GB2312'EXEC @hr = sp_OASetProperty @object, 'Bcc', @BccEXEC @hr = sp_OASetProperty @object, 'From', @FromEXEC @hr = sp_OASetProperty @object, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @object, 'TextBody',@BodyEXEC @hr = sp_OAMethod @object, 'Send', NULL

--判斷出錯IF @hr <> 0BEGIN EXEC sp_OAGetErrorInfo @object; set @errnum=@hr RETURN @objectEND--PRINT 'success'EXEC @hr = sp_OADestroy @object

GO