send mail SP

CREATE PROCEDURE [dbo].[SendMail_cdo_sp]
    @From varchar(100) ,
    @To varchar(8000) ,
    @Cc varchar(8000) = '',
    @Bcc varchar(8000) = '',
    @Subject varchar(200)=" ",
    @Body varchar(8000) =" " ,
    @Attachment varchar(8000) = '',
    @SMTPServer varchar(20) ='',  
    @SMTPServer2 varchar(20) =''  

 /*********************************************************************
 
 To Send Mail:
 exec master..SendMail_cdo_sp
  @From = 'fuck@sendmail.com',
  @To = 'fuck@sendmail.com;fuck@send.com',
  @Cc = '',
  @Bcc = '',
  @Subject = 'Subject',
  @Body = 'Mail Body -- My Message',
  @Attachment = ''
 
 ***********************************************************************/
    AS
    Declare @iMsg int
    Declare @hr int
    Declare @ErrorMessage varchar(200)

    IF @SMTPServer = '' Set @SMTPServer = '10X.17X.X48.12X'
    IF @SMTPServer2 = '' Set @SMTPServer2 = '10X.17X.X49.15X'
 
 --************* Create the CDO.Message Object ************************
    EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On CreateObject'
   GOTO ObjectError
  end
 
 --***************Configuring the Message Object ******************
 -- This is to configure a remote SMTP server.
 -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
        send_cdosysmail_config:
    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Configuration'
   GOTO ObjectError
  end

 -- This is to configure the Server Name or IP address.
 -- Replace MailServerName by the name or IP of your SMTP Server.
    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTPServer
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Configuration SMTP Server ' + @SMTPServer
   GOTO ObjectError
  end
 
 -- Save the configurations to the message object.
    EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Configuration Update'
   GOTO ObjectError
  end
 
 -- Set the e-mail parameters.
    EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Setting From Address'
   GOTO ObjectError
  end

    EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Setting To Address'
   GOTO ObjectError
  end

 if len(@Cc) > 0
  begin
    EXEC @hr = sp_OASetProperty @iMsg, 'Cc', @Cc
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Setting To Address'
   GOTO ObjectError
  end
  end

 if len(@Bcc) > 0
  begin
    EXEC @hr = sp_OASetProperty @iMsg, 'Bcc', @Bcc
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Setting To Address'
   GOTO ObjectError
  end
  end

    EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Setting Subject'
   GOTO ObjectError
  end
 
 -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
    EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Setting Mail Body'
   GOTO ObjectError
  end

 if len(@Attachment) > 0
  begin
    exec master..xp_fileexist @Attachment, @hr output
    if @hr = 1
       --EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment
  EXEC sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment
    else
  begin
   set @ErrorMessage = @Attachment + 'Does Not Exist !!'
   GOTO ObjectError
  end
  end

 print 'Using SMTP Server ' + @SMTPServer + ' On ' + convert(char(19),getdate(),120)
    EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

    IF @hr <>0 and @SMTPServer <> @SMTPServer2
      BEGIN
  -- Support the 2nd SMTP Server 07/07/2006
  set @SMTPServer = @SMTPServer2
  GOTO send_cdosysmail_config
      END
   
    IF @hr <> 0
  begin
   set @ErrorMessage = 'Error On Send Mail'
   GOTO ObjectError
  end

 -- Do some error handling after each step if you have to.
 -- Clean up the objects created.
        send_cdosysmail_cleanup:
 If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
 BEGIN
  EXEC @hr=sp_OADestroy @iMsg
 
  -- handle the failure of the destroy if needed
  IF @hr <> 0 GOTO ObjectError
 END

PRINT 'Message sent.'
RETURN 0

ObjectError:
BEGIN
Print @ErrorMessage
RETURN 1
END
GO

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/90986/viewspace-706110/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/90986/viewspace-706110/