该存储过程使用办公自动化脚本调用Dimac w3 JMail AxtiveX组件来代替Sql Mail发送邮件

CREATE Procedure dbo.sp_jmail_send
@sender varchar(100),
@sendername varchar(100)='',
@serveraddress varchar(255)='SMTP服务器地址',
@MailServerUserName varchar(255)=null,
@MailServerPassword varchar(255)=null,
@recipient varchar(255),
@recipientBCC varchar(200)=null,
@recipientBCCName varchar(200)=null,
@recipientCC varchar(200)=null,
@recipientCCName varchar(100)=null,
@attachment varchar(100) =null,
@subject varchar(255),
@mailbody text
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)

--创建JMail.Message对象

Exec @hr = sp_OACreate 'jmail.message', @object OUTPUT

--设置邮件编码
Exec @hr = sp_OASetProperty @object, 'Charset', 'gb2312'

--身份验证
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_OAMethod @object, 'AddRecipient', NULL , @recipient
Exec @hr = sp_OASetProperty @object, 'Subject', @subject
Exec @hr = sp_OASetProperty @object, 'Body', @mailbody

--设置其它参数
if not @attachment is null
exec @hr = sp_OAMethod @object, 'Addattachment', NULL , @attachment,'false'
print @attachment
If (Not @recipientBCC is null) And (Not @recipientBCCName is null)
Exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC,@recipientBCCName
Else If Not @recipientBCC is null
Exec @hr = sp_OAMethod @object, 'AddRecipientBCC', NULL , @recipientBCC

If (Not @recipientCC is null) And (Not @recipientCCName is null)
Exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC,@recipientCCName
Else If Not @recipientCC is null
Exec @hr = sp_OAMethod @object, 'AddRecipientCC', NULL , @recipientCC

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 @output='错误源: '+@source
  Print @output
  Select @output = '错误描述: ' + @description
  Print @output
 End
Else
 Begin
  Print '获取错误信息失败！'
  Return
 End

--释放JMail.Message对象
Exec @hr = sp_OADestroy @object

if (@hr=1)
return
  
GO