对微软提供的CDOSYS SendMail 函数进行了修改, 可发送带多个附件的 HTML 邮件

一.  Create Store Procedure : sp_send_cdosysmail


CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
    @From varchar(100) ,
    @To varchar(1000) ,
    @Subject varchar(1000)=" ",
    @Body varchar(8000) =" ",
    @attachments varchar(4000)=NULL
 /*********************************************************************
 
 This stored procedure takes the parameters and sends an e-mail.
 All the mail configurations are hard-coded in the stored procedure.
 Comments are added to the stored procedure where necessary.
 References to the CDOSYS objects are at the following MSDN Web site:
 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
 
 ***********************************************************************/
    AS
    Declare @iMsg int
    Declare @hr int
    Declare @source varchar(255)
    Declare @description varchar(500)
    Declare @output varchar(1000)
        /******************************************************************
        Supply attachments as either a single file or a comma delimitted list
        This stored procedure takes the above parameters and sends an e-mail.
        All of the mail configurations are hard-coded in the stored procedure.
        Comments are added to the stored procedure where necessary.
        Reference to the CDOSYS objects are at the following MSDN Web site:
        http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
        ******************************************************************/
           Declare @files table(fileid int identity(1,1),[file] varchar(255))
           Declare @file varchar(255)
           Declare @filecount int ; set @filecount=0
           Declare @counter int ; set @counter = 1
 
 --************* Create the CDO.Message Object ************************
    EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate')
        EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '  Source: ' + @source
            PRINT  @output
            SELECT @output = '  Description: ' + @description
            PRINT  @output
                   INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate')
                   RETURN
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            RETURN
          END
      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
    EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing')
        EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '  Source: ' + @source
            PRINT  @output
            SELECT @output = '  Description: ' + @description
            PRINT  @output
                   INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      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', '161.36.218.16'
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver')
        EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '  Source: ' + @source
            PRINT  @output
            SELECT @output = '  Description: ' + @description
            PRINT  @output
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END
 
 -- Save the configurations to the message object.
    EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update')
        EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '  Source: ' + @source
            PRINT  @output
            SELECT @output = '  Description: ' + @description
            PRINT  @output
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update')                
     GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END
 
 -- Set the e-mail parameters.
    EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To')
        EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '  Source: ' + @source
            PRINT  @output
            SELECT @output = '  Description: ' + @description
            PRINT  @output
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To')                
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

    EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From')
        EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '  Source: ' + @source
            PRINT  @output
            SELECT @output = '  Description: ' + @description
            PRINT  @output
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From')                
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

    EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject')
        EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '  Source: ' + @source
            PRINT  @output
            SELECT @output = '  Description: ' + @description
            PRINT  @output
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END
 
 -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
    EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody')
        EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '  Source: ' + @source
            PRINT  @output
            SELECT @output = '  Description: ' + @description
            PRINT  @output
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      END

   
 /***********************************************************************
         Attachment support script add.
        ***********************************************************************/
        IF @attachments IS NOT NULL
        BEGIN       
          INSERT @files SELECT value FROM dbo.fn_split(@attachments,',')       
          SELECT @filecount=@@ROWCOUNT       
          WHILE @counter<(@filecount+1)       
          BEGIN               
            SELECT @file = [file] FROM @files WHERE fileid=@counter
            EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment',NULL, @file
            SET @counter=@counter+1
          END
        END
        /***********************************************************************
         Attachment support script add end.
        ***********************************************************************/
          EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
    IF @hr <>0
      BEGIN
        SELECT @hr
        INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')
        EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
        IF @hr = 0
          BEGIN
            SELECT @output = '  Source: ' + @source
            PRINT  @output
            SELECT @output = '  Description: ' + @description
            PRINT  @output
         INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')
                   GOTO send_cdosysmail_cleanup
          END
        ELSE
          BEGIN
            PRINT '  sp_OAGetErrorInfo failed.'
            GOTO send_cdosysmail_cleanup
          END
      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
       BEGIN
   select @hr
                 INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')
          EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
 
   -- if sp_OAGetErrorInfo was successful, print errors
   IF @hr = 0
   BEGIN
    SELECT @output = '  Source: ' + @source
           PRINT  @output
           SELECT @output = '  Description: ' + @description
           PRINT  @output
    INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')
   END
   
   -- else sp_OAGetErrorInfo failed
   ELSE
   BEGIN
    PRINT '  sp_OAGetErrorInfo failed.'
           RETURN
   END
  END
 END
 ELSE
 BEGIN
  PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
  INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped')
         RETURN
 END

--declare @Body varchar(4000)
--select @Body = 'This is a Test Message'
--exec sp_send_cdosysmail 'someone@example.com','someone2@example.com','Test of CDOSYS',@Body
GO


二.  Function  fn_Split

CREATE FUNCTION fn_Split(@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
 @value varchar(8000),
 @bcontinue bit,
 @iStrike smallint,
 @iDelimlength tinyint

IF @sDelim = 'Space'
 BEGIN
 SET @sDelim = ' '
 END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
 BEGIN
 WHILE @bcontinue = 1
  BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
 
  IF CHARINDEX(@sDelim, @sText)>0
   BEGIN
   SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
    BEGIN
    INSERT @retArray (idx, value)
    VALUES (@idx, @value)
    END
   
--Trim the element and its delimiter from the front of the string.
   --Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
   SET @idx = @idx + 1
   SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
  
   END
  ELSE
   BEGIN
--If you can抰 find the delimiter in the text, @sText is the last value in
--@retArray.
 SET @value = @sText
    BEGIN
    INSERT @retArray (idx, value)
    VALUES (@idx, @value)
    END
   --Exit the WHILE loop.
SET @bcontinue = 0
   END
  END
 END
ELSE
 BEGIN
 WHILE @bcontinue=1
  BEGIN
  --If the delimiter is an empty string, check for remaining text
  --instead of a delimiter. Insert the first character into the
  --retArray table. Trim the character from the front of the string.
--Increment the index and loop.
  IF DATALENGTH(@sText)>1
   BEGIN
   SET @value = SUBSTRING(@sText,1,1)
    BEGIN
    INSERT @retArray (idx, value)
    VALUES (@idx, @value)
    END
   SET @idx = @idx+1
   SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
   
   END
  ELSE
   BEGIN
   --One character remains.
   --Insert the character, and exit the WHILE loop.
   INSERT @retArray (idx, value)
   VALUES (@idx, @sText)
   SET @bcontinue = 0 
   END
 END

END

RETURN
END