/*  Name:       sp_sendSMTPmail

    Purpose:    Send an SMTP mail using CDONTS object. 
    
    Requirements:  SMTP server (comes with IIS but doesn't require IIS) must be loaded.

    Returns:    0 if successful, 1 if any errors
    
    Sample Usage:
    sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this', 
                    @cc='irmsqlmail@db.com', 
                    @Importance=1,  
                    @Attachments='c:\boot.ini;c:\autoexec.bat'
                    
    History:
      02/07/2001  VRI  Created.
      11/05/2001  VRI  Updated usage comment to @Importance=1 since =3 was invalid.  Swapped single for
                         double quotes.
*/
USE master
go

IF OBJECT_ID('sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE sp_hexadecimal
GO    

CREATE PROCEDURE sp_hexadecimal (@binvalue varbinary(255),
                                 @hexvalue varchar(255) OUTPUT)
AS
DECLARE @charvalue varchar(255)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH(@binvalue)
SELECT @hexstring = '0123456789abcdef'

WHILE (@i <= @length)
  BEGIN
    DECLARE @tempint int
    DECLARE @firstint int
    DECLARE @secondint int
    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
    SELECT @firstint = FLOOR(@tempint/16)
    SELECT @secondint = @tempint - (@firstint*16)
    SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
    SELECT @i = @i + 1
  END
SELECT @hexvalue = @charvalue
GO


IF OBJECT_ID('sp_displayoaerrorinfo ') IS NOT NULL
    DROP PROCEDURE sp_displayoaerrorinfo 
GO    

CREATE PROCEDURE sp_displayoaerrorinfo (@object int,
                                        @hresult int)
AS
DECLARE @output varchar(255)
DECLARE @hrhex char(10)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(255)

SET CONCAT_NULL_YIELDS_NULL OFF

PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @hresult, @hrhex OUT
SELECT @output = '  HRESULT: ' + @hrhex
PRINT @output
EXEC @hr = sp_OAGetErrorInfo @object, @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
GO


IF OBJECT_ID('sp_sendSMTPmail') IS NOT NULL
        DROP PROCEDURE sp_sendSMTPmail
GO

CREATE PROCEDURE sp_sendSMTPmail (@To varchar(8000),
                                  @Subject varchar(255),
                                  @Body text = null,
                                  @Importance int = 1,               -- 0=low, 1=normal, 2=high
                                  @Cc varchar(8000) = null,
                                  @Bcc varchar(8000) = null,
                                  @Attachments varchar(8000) = null, -- delimeter is ;
                                  @HTMLFormat int = 0,
                                  @From varchar(255) = null)

/*  Name:       sp_sendSMTPmail

    Purpose:    Send an SMTP mail using CDONTS object. 
    
    Requirements:  SMTP server (comes with IIS but doesn't require IIS) must be loaded.

    Returns:  0 if successful, 1 if any errors
    
    Sample Usage:
    sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this', 
                    @cc='irmsqlmail@db.com', 
                    @Importance=1,  
                    @Attachments='c:\boot.ini;c:\autoexec.bat'
                    
    History:
      02/07/2001  VRI  Created.
*/

AS

SET NOCOUNT ON

DECLARE @object int,
        @hr int,
        @StrEnd int,
        @Attachment varchar(255),
        @return int,
        @Msg varchar(255)

SELECT @From = isnull(@From, @@SERVERNAME)

-- Create the CDONTS NewMail object.
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError

-- Add the optional properties if they are specified
IF @Body IS NOT NULL
BEGIN
    EXEC @hr = sp_OASetProperty @object, 'Body', @Body
    IF @hr <> 0 GOTO ObjectError
END  
  
IF @Cc IS NOT NULL
BEGIN
    EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc
    IF @hr <> 0 GOTO ObjectError
END  
  
IF @Bcc IS NOT NULL
BEGIN
    EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
    IF @hr <> 0 GOTO ObjectError
END

IF @HTMLFormat <> 0
BEGIN
    EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0
    IF @hr <> 0 GOTO ObjectError
END

-- Loop through the ; delimited files to attach
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)

WHILE isnull(len(@Attachments),0) > 0
BEGIN
   SELECT @StrEnd = CASE  charindex(';', @Attachments)
                      WHEN 0 THEN len(@Attachments)
                      ELSE charindex(';', @Attachments) - 1
                    END  
   SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
   SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))

   -- Ensure we can find the file we want to send.
   DELETE #FileExists
   INSERT #FileExists
     EXEC master..xp_fileexist @Attachment

   IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
     BEGIN
       RAISERROR ('File %s does not exist.  Message not sent.', 16, 1, @Attachment)
       RETURN 1
     END  

   EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
   IF @hr <> 0 GOTO ObjectError

   SELECT @Msg = 'File ' + @Attachment + ' attached.'
   PRINT @Msg
END

-- Call the Send method with parms for standard properties
EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @To, @Subject, @Importance=@Importance
IF @hr <> 0 GOTO ObjectError
  
-- Destroy the NewMail object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError

PRINT 'Message sent.'
RETURN 0

ObjectError:
BEGIN
    EXEC sp_displayoaerrorinfo @object, @hr
    RETURN 1
END


GO




GRANT EXEC ON sp_sendSMTPmail TO public
GO