--身份验证
If Not @MailServerUserName is null
Exec @hr = sp_OASetProperty @object, 'MailServerUserName',@MailServerUserName
If Not @MailServerPassword is null
Exec @hr = sp_OASetProperty @object, 'MailServerPassword',@MailServerPassword
--设置其它参数
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
if (@hr = 0)
Begin
Set @output='错误源: '+@source
Print @output
Select @output = '错误描述: ' + @description
Print @output
End
Else
Begin
Print '获取错误信息失败!'
Return
End
--初始化缺省变量
Set @DefaultSender='缺省发送地址'
Set @MailServer='邮件服务器地址'
Set @User='SMTP服务器验证用户地址'
Set @Pass='SMTP服务器验证地址'
Set @SenderName='缺省发件人名称'
Set @AttachDir='E:\LOG\WebData\Jmail\'+Replace(Replace(Replace(Convert(varChar(19),GetDate(),120),'-',''),' ',''),':','')+'.txt'
--将Email地址分割符统一为分号
set @SplitStr=';'
Set @strTemp=@strRecipients+@SplitStr+'end'
Set @strTemp=Replace(@strTemp,',',';')
--判断是否有sql语句
If (@Sql is Null) Or (len(@Sql)=0)
Set @AttachDir=Null
Else
Begin
Declare @CmdStr varChar(200)
Set @CmdStr='bcp "'+@Sql+'" queryout '+@AttachDir+' -c'
EXEC master..xp_cmdshell @CmdStr
End
while CharIndex(@SplitStr,@strTemp,1)<>0
Begin
Set @email=left(@strTemp,CharIndex(@SplitStr,@strTemp,1)-1)
Set @strTemp=right(@strTemp,len(@strTemp)-len(@email)-1)
If (@Sender Is Null) Or (Len(@Sender)=0)
Set @SenderAddress=@DefaultSender
Else
Set @SenderAddress=@Sender
Print @email
--调用sp_jmail_send发送邮件
EXEC sp_jmail_send @sender=@SenderAddress,@sendername=@SenderName,
@serveraddress=@MailServer,@MailServerUserName=@User,@MailServerPassword=@Pass,
@recipient=@email,@subject=@strSubject,@mailbody=@strMessage,@attachment=@AttachDir
End
此存储过程只扩展了Sql查询结果附件发送,如果你要发送标准附件,请直接使用sp_jmail_send存储过程或者自行扩展功能。