SQL2000調用JMail發送郵件

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