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]GOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE 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 NULLEXEC @hr = SP_OASetProperty @object, 'MailServerUserName',@MailServerUserNameIF NOT @MailServerPassword IS NULLEXEC @hr = SP_OASetProperty @object, 'MailServerPassword',@MailServerPassword--设置邮件基本参数EXEC @hr = SP_OASetProperty @object, 'From', @senderEXEC @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 NULLBEGIN 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 @tempTableEND--设置其它参数IF NOT @attachment IS NULLEXEC @hr = SP_OAMethod @object, 'Addattachment', NULL , @attachment,'false'PRINT @attachment--設置暗送IF NOT @recipientBCC IS NULLBEGIN 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 @tempTableEND--設置抄送IF NOT @recipientCC IS NULLBEGIN 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 @tempTableENDIF NOT @sendername IS NULLEXEC @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 OUTPUTIF (@hr = 0)BEGIN SET @errInfo=@description SET @output='错误源: '+@source PRINT @output SELECT @output = '错误描述: ' + @description PRINT @outputENDELSEBEGIN PRINT '获取错误信息失败！' ReturnEND----释放JMail.Message对象EXEC @hr = SP_OADestroy @objectGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
