sqlserver 发送邮件脚本

 

USE [MaganerDB]

GO
/****** 对象:  StoredProcedure [dbo].[spSendMail]    脚本日期: 07/16/2014 18:27:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[spSendMail]

    @from varchar(100),                --send by
    @to varchar(100),                --send to
    @bcc varchar(500),                --bcc(blind carbon copy)/cc(carbon copy)
    @subject varchar(400) = ' ',                --mail subject
    @htmlBody varchar(8000) = ' ',            --mail body content
    @addAttachment varchar(100)            --p_w_upload,such as 'd:/fileName.xls',if there is no p_w_upload,just input ''
as
    declare @object int
    declare @hr int
    declare @source varchar(255)
    declare @description varchar(500)
    declare @output varchar(1000)
     
    declare @smtpServer varchar(50)
    declare @smtpUsername varchar(50)
    declare @smtpPassword varchar(50)
    set @smtpServer = 'smtp.163.com'
    set @smtpUsername = 'Username'
    set @smtpPassword = 'UserPass'
 
   
    --http://schemas.microsoft.com/cdo/configuration
    exec @hr = sp_OACreate 'CDO.Message',@object out
    set @htmlBody = '<body><h3><font col=Red>' + @htmlBody + '</font></h3></body>'
    --change line
    --set @htmlBody = replace(@htmlBody,char(10),'<br/>')
    --exec @hr = sp_OASetProperty @object,'HTMLBodyPart.Charset','GBK'
 
    exec @hr = sp_OASetProperty @object,'Configuration.fields("
    exec @hr = sp_OASetProperty @object,'Configuration.fields("
    exec @hr = sp_OASetProperty @object,'Configuration.fields("
    exec @hr = sp_OASetProperty @object,'Configuration.fields("
    exec @hr = sp_OASetProperty @object,'Configuration.fields("
    exec @hr = sp_OASetProperty @object,'Configuration.fields("
 
    exec @hr = sp_OAMethod @object,'Configuration.Fields.Update',null
    exec @hr = sp_OASetProperty @object,'To',@to
    exec @hr = sp_OASetProperty @object,'Bcc',@bcc
    exec @hr = sp_OASetProperty @object,'From',@from
    exec @hr = sp_OASetProperty @object,'Subject',@subject
    exec @hr = sp_OASetProperty @object,'HtmlBody',@htmlBody
    --exec @hr = sp_OASetProperty @object,'TextBody',@htmlBody(String content)
     
    if @addAttachment <> '' begin
     declare @par int ;
     exec @hr = sp_OAMethod @object,'AddAttachment',@par output ,@addAttachment

    print   @par ;  

    end  ;

    if @hr <> 0
      --  select @hr
        begin
            exec @hr = sp_OAGetErrorInfo null,@source out,@description out
            if @hr = 0
                begin
                    select @output = ' Source:
                    print @output
                    select @output = ' Description:
                    print @output
                end
            else
                begin
                    print ' sp_OAGetErrorInfo failure!'
                    return
                end
        end

   exec @hr = sp_OAMethod @object,'Send',null
 

    --check error

    if @hr <>0
        select @hr
        begin
            exec @hr = sp_OAGetErrorInfo null,@source out,@description out
            if @hr = 0
                begin
                    select @output = ' Source:
                    print @output
                    select @output = ' Description:
                    print @output
                end
            else
                begin
                    print ' sp_OAGetErrorInfo failure!'
                    return
                end
        end
    print 'Send Mail Success!'
  exec @hr = sp_OADestroy @object