Send Mail

-- this creates a table needed to trap errors

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[cdosysmail_failures](
    [Date of Failure] [datetime] NULL,
    [Spid] [int] NULL,
    [From] [varchar](100) NULL,
    [To] [varchar](100) NULL,
    [CC] [varchar](2048) NULL,
    [BCC] [varchar](2048) 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
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

-- This will create the stored proc

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*******************************************************************/--Name : sp_send_cdosysmail
--Server : Generic 
--Description : SQL smtp e-mail using CDOSYS, OLE Automation and a
-- network smtp server;
--
--Note : Be sure to set the default for @SMTPServer to 
-- the company network smtp server or you will have to 
-- pass it in each time. 
--
--Comments : Getting the network SMTP configured to work properly
-- may require engaging your company network or 
-- server people who deal with the netowrk SMTP server. 
-- Some errors that the stored proc returns relate to 
-- incorrect permissions for the various SQL Servers to 
-- use the SMTP relay server to bouce out going mail. 
-- Without proper permissions the SQL server appears as 
-- a spammer to the local SMTP network server.
--
--Parameters : See the 'Syntax' Print statements below or call the 
-- sp with '?' as the first input.
-- 
--Date : 06/25/08
--Author : Scott Williams - Digital Transformations, Inc.
--
--History : 
/*******************************************************************/
    CREATE PROCEDURE [dbo].[sp_send_cdosysmail] 
     @From        VARCHAR(100) = 'sw7886@att.com' ,
     @To            VARCHAR(100) = null ,
     @CC            VARCHAR(2048) = '',
     @BCC            VARCHAR(2048) = '',
     @Subject        VARCHAR(100) = '',
     @Body        VARCHAR(4000)= '',
     @BodyType    VARCHAR(50) = "HTMLBody",            -- If you are using HTML e-mail, use 'HTMLBody' otherwise use 'TextBody'
     @SMTPServer VARCHAR(255) = 'mailhost.bscc.bls.com'    
     
    /*********************************************************************
    
    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)
     
    Set nocount on

-- Determine if the user requested syntax.
If @To = '?'
 Begin
 Print 'Syntax for sp_send_cdosysmail (based on CDOSYS):'
 Print 'Exec master.dbo.sp_send_cdosysmail'
 Print ' @To (varchar(2048)) - Recipient e-mail address list separating each with a '';'' '
 Print ' or a '',''. Use a ''?'' to return the syntax.'
 Print ' @Body (varchar(8000)) - Text body; use embedded char(13) + char(10)'
 Print ' for carriage returns. The default is nothing'
 Print ' @Subject (varchar(255))) - E-mail subject. The default is a message from'
 Print ' @@servername.'
 Print ' @Attachments (varchar(1024)) - Attachment list separating each with a '';''.'
 Print ' The default is no attachments.'
 Print ' @From (varchar(128)) - Sender list defaulted to @@ServerName.'
 Print ' @CC (varchar(2048)) - CC list separating each with a '';'' or a '','''
 Print ' The default is no CC addresses.'
 Print ' @BCC (varchar(2048)) - Blind CC list separating each with a '';'' or a '','''
 Print ' The default is no BCC addresses.'
 Print ' @SMTPServer (varchar(255)) - Network smtp server defaulted to your companies network'
 Print ' smtp server. Set this in the stored proc code.'
 Print ''
 Print ''
 Print 'Example:'
 Print 'sp_send_cdosysmail ''<''">user@mycompany.com>'', ''This is a test'', @SMTPServer = <network smtp relay server>'
 Print ''
 Print 'The above example will send an smpt e-mail to <user@mycompany.com> from @@ServerName'
 Print 'with a subject of ''Message from SQL Server <@@ServerName>'' and a'
 Print 'text body of ''This is a test'' using the network smtp server specified.'
 Print ''
 Print 'Be sure to set the default for @SMTPServer before compiling this stored procedure.'
 Print ''
 Return
 End
    
    
    
    --************* 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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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', @SMTPServer 
     IF @hr <>0 
     BEGIN
     SELECT @hr
     INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, 'CC', @CC
     IF @hr <>0 
     BEGIN
     SELECT @hr
     INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @CC , @BCC, @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, @CC, @BCC, @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, 'BCC', @BCC
     IF @hr <>0 
     BEGIN
     SELECT @hr
     INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @CC , @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @BodyType, @Body
     IF @hr <>0 
     BEGIN
     SELECT @hr
     INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @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, @CC, @BCC, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped')
     RETURN
    END