if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_SEND_MAIL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_SEND_MAIL]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE SP_SEND_MAIL(@sender VARCHAR(100),@sendername VARCHAR(100)='',@serveraddress VARCHAR(255)='121.13.*.*',
@MailServerUserName VARCHAR(255)=NULL,@MailServerPassword VARCHAR(255)=NULL,@recipient VARCHAR(1000),
@recipientBCC VARCHAR(1000)=NULL,@recipientBCCName VARCHAR(1000)=NULL,@recipientCC VARCHAR(1000)=NULL,
@recipientCCName VARCHAR(1000)=NULL,@attachment VARCHAR(100) =NULL,@subject VARCHAR(255),@mailbody TEXT,
@errInfo VARCHAR(400) OUTPUT)
AS
/*
该存储过程使用办公自动化脚本调用Dimac w3 JMail AxtiveX组件来代替Sql Mail发送邮件
该方法支持“服务器端身份验证”
*/
--声明w3 JMail使用的常规变量及错误信息变量
DECLARE @object INT,@hr INT,@rc INT,@output VARCHAR(400),@description VARCHAR (400),@source VARCHAR(400)
DECLARE @tmpMail VARCHAR(1000)
DECLARE @tempTable table(tmpKey varchar(8000))
-------创建JMail.Message对象
EXEC @hr = SP_OACreate 'jmail.message', @object OUTPUT
--设置邮件编码
EXEC @hr = SP_OASetProperty @object, 'Charset', 'BIG5'
--身份验证
IF NOT @MailServerUserName IS NULL
EXEC @hr = SP_OASetProperty @object, 'MailServerUserName',@MailServerUserName
IF NOT @MailServerPassword IS NULL
EXEC @hr = SP_OASetProperty @object, 'MailServerPassword',@MailServerPassword
--设置邮件基本参数
EXEC @hr = SP_OASetProperty @object, 'From', @sender
EXEC @hr = SP_OASetProperty @object, 'Subject', @subject
--EXEC @hr = SP_OASetProperty @object, 'Body', @mailbody --非HTML格式
EXEC @hr = SP_OASetProperty @object, 'HTMLBody', @mailbody --HTML格式
--設置接收者
IF NOT @recipient IS NULL
BEGIN
INSERT INTO @tempTable SELECT * FROM SplitToStr(@recipient,',')
DECLARE tmp_Cur SCROLL CURSOR FOR SELECT tmpKey from @tempTable
OPEN tmp_Cur
FETCH NEXT FROM tmp_Cur
INTO @tmpMail
WHILE @@FETCH_STATUS =0
BEGIN
EXEC @hr = SP_OAMethod @object, 'AddRecipient', NULL , @tmpMail
FETCH NEXT FROM tmp_Cur
INTO @tmpMail
END
CLOSE tmp_Cur
DEALLOCATE tmp_Cur
DELETE FROM @tempTable
END
--设置其它参数
IF NOT @attachment IS NULL
EXEC @hr = SP_OAMethod @object, 'Addattachment', NULL , @attachment,'false'
PRINT @attachment
--設置暗送
IF NOT @recipientBCC IS NULL
BEGIN
INSERT INTO @tempTable SELECT * FROM SplitToStr(@recipientBCC,',')
DECLARE tmp_Cur SCROLL CURSOR FOR SELECT tmpKey from @tempTable
OPEN tmp_Cur
FETCH NEXT FROM tmp_Cur
INTO @tmpMail
WHILE @@FETCH_STATUS =0
BEGIN
EXEC @hr = SP_OAMethod @object, 'AddRecipientBCC', NULL , @tmpMail
FETCH NEXT FROM tmp_Cur
INTO @tmpMail
END
CLOSE tmp_Cur
DEALLOCATE tmp_Cur
DELETE FROM @tempTable
END
--設置抄送
IF NOT @recipientCC IS NULL
BEGIN
INSERT INTO @tempTable SELECT * FROM SplitToStr(@recipientCC,',')
DECLARE tmp_Cur SCROLL CURSOR FOR SELECT tmpKey from @tempTable
OPEN tmp_Cur
FETCH NEXT FROM tmp_Cur
INTO @tmpMail
WHILE @@FETCH_STATUS =0
BEGIN
EXEC @hr = SP_OAMethod @object, 'AddRecipientCC', NULL , @tmpMail
FETCH NEXT FROM tmp_Cur
INTO @tmpMail
END
CLOSE tmp_Cur
DEALLOCATE tmp_Cur
DELETE FROM @tempTable
END
IF NOT @sendername IS NULL
EXEC @hr = SP_OASetProperty @object, 'FromName', @sendername
--调用Send方法发送邮件
EXEC @hr = SP_OAMethod @object, 'Send', NULL,@serveraddress
--捕获JMail.Message异常
EXEC @hr = SP_OAGetErrorInfo @object, @source OUTPUT, @description OUTPUT
IF (@hr = 0)
BEGIN
SET @errInfo=@description
SET @output='错误源: '+@source
PRINT @output
SELECT @output = '错误描述: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT '获取错误信息失败!'
Return
END
----释放JMail.Message对象
EXEC @hr = SP_OADestroy @object
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SQL2000調用JMail發送郵件
最新推荐文章于 2024-08-25 13:51:40 发布