Search

Send Mail through SQL (using CDOSYS object)

--Start SP # 3 Send Mail through SQL (using CDOSYS object)

-- drop old cdosysmail_failures table if exists
IF (EXISTS (SELECT
  * FROM dbo.sysobjects WHERE name = N'cdosysmail_failures' AND type='U')) DROP TABLE [dbo].[cdosysmail_failures]
GO
-- Create new cdosysmail_failures table
CREATE TABLE [dbo].[cdosysmail_failures]
        ([Date
  of Failure] datetime,
        [Spid]
  int NULL,
        [From] varchar(100)
  NULL,
        [To] varchar(100)
  NULL,
        [Subject] varchar(100)
  NULL,
        [Body] varchar(4000)
  NULL,
        [iMsg] int NULL,
        [Hr]
  int NULL,
        [Source of Failure]
  varchar(255) NULL,
        [Description
  of Failure] varchar(500) NULL,
        [Output
  from Failure] varchar(1000) NULL,
        [Comment
  about Failure] varchar(50) NULL)
GO

IF (EXISTS (SELECT * FROM
  dbo.sysobjects WHERE name = N'sp_send_cdosysmail' AND type='P')) DROP PROCEDURE [dbo].[sp_send_cdosysmail]
GO

    CREATE
  PROCEDURE [dbo].[sp_send_cdosysmail]
      
  @From varchar(100) ,
       @To varchar(100)
  ,
       @Subject varchar(100)=" ",
      
  @Body varchar(4000) =" "
    /*********************************************************************

    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)

    --************* 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', cdoSMTPServerName
       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, 'TextBody', @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

      
  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


--Next, use the stored procedure that you created and provide the correct parameters.
  declare @Body varchar(4000)
   select @Body =
  'This is a Test Message'
   exec sp_send_cdosysmail ''dummyuserreceipient@gmail.com',''dummyuserreceipient@gmail.com','Test of CDOSYS',@Body

--End SP # 3 Send Mail through SQL