/**********************************************************************
 * Name:        usp_Send_CDOSysMail
 * Author:      Microsoft / customized by: Jonathan Kehayias
 *				Customized a bit by Mtassin for Win 2k3 compatibility
 * Date:        12 April 2007
 * Database:    DBA_Data
 *
 * Purpose:
 * Creates a OLE Automation object to send an email using a SMTP Server
 * configured in the Parameters table of the DBA_Data database.  If an
 * attachment is specified, it will be added to the message.
 *
 * 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
 *
 * Changes
 **********************************************************************
 * No Changes
 *
 **********************************************************************/
ALTER PROCEDURE [Send_CDOSysMail] 
   @From varchar(4000),
   @To varchar(4000),
   @Subject varchar(4000)=' ',
   @Body varchar(4000) = ' ',
   @Attachment varchar(4000) = ' ',
   @Importance int,
   @Priority int
 
   AS
   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)
   Declare @mailserver varchar(255)
 
   SELECT @mailserver = 'asi-exhub-1.asi.corp'
 
--************* Create the CDO.Message Object ************************
   EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
 
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- 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', @mailserver
 
-- Save the configurations to the message object.
   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
 
-- Set the e-mail parameters.
   EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
   EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
   EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- Check for Attachment specified and attach if necessary.
   IF @Attachment IS NOT NULL
	BEGIN
	   EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', null, @Attachment
	END
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
   EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
   EXEC @hr = sp_OASetProperty @iMsg,'fields("urn:schemas:httpmail:importance").Value',@Importance
   EXEC @hr = sp_OASetProperty @iMsg,'fields("urn:schemas:httpmail:priority").Value',@Priority
--   EXEC @hr = sp_OASetProperty @iMsg, 'Priority', 1
   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
 
 
-- Sample error handling.
   IF @hr <>0 
     select @hr
     BEGIN
       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
         END
       ELSE
         BEGIN
           PRINT '  sp_OAGetErrorInfo failed.'
           RETURN
         END
     END
 
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg