Send email from SQL Server using stored procedure

CREATE Procedure dbo.sp_SQLSMTPMail
       @vcTo           varchar(2048) = null,
       @vcBody         varchar(8000) = '',
       @vcSubject      varchar(255)  = null,
       @vcAttachments  varchar(1024) = null,
       @vcQuery        varchar(8000) = null,
       @vcFrom         varchar(128)  = null,
       @vcCC           varchar(2048) = '',
      @vcSenderName   varchar(128)  = null, 
       @vcBCC          varchar(2048) = '',
      @vcSMTPServer   varchar(255)  = 'smtprelay.abc.com',    -- put local network smtp server name here
       @cSendUsing     char(1)       = '2',
       @vcPort         varchar(3)    = '25',
       @cAuthenticate  char(1)       = '0',
       @vcDSNOptions   varchar(2)    = '0',
       @vcTimeout      varchar(2)    = '30',
       @vcServerName   sysname       = null

As

/*******************************************************************/
--Name        : sp_SQLSMTPMail
--Server      : Generic
--Description : SQL smtp e-mail using CDOSYS, OLE Automation and a
--              network smtp server; For SQL Servers running on
--              windows 2000.
--
--Note        : Be sure to set the default for @vcSMTPServer above 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.
--History     :
/*******************************************************************/

Set nocount on

-- Determine if the user requested syntax.
If @vcTo = '?'
   Begin
      Print 'Syntax for sp_SQLSMTPMail (based on CDOSYS):'
      Print 'Exec master.dbo.sp_SQLSMTPMail'
      Print '     @vcTo          (varchar(2048)) - Recipient e-mail address list separating each with a '';'' '
      Print '                                       or a '',''. Use a ''?'' to return the syntax.'
      Print '     @vcBody        (varchar(8000)) - Text body; use embedded char(13) + char(10)'
      Print '                                       for carriage returns. The default is nothing'
      Print '     @vcSubject     (varchar(255))) - E-mail subject. The default is a message from'
      Print '                                       @@servername.'
      Print '     @vcAttachments (varchar(1024)) - Attachment list separating each with a '';''.'
      Print '                                       The default is no attachments.'
      Print '     @vcQuery       (varchar(8000)) - In-line query or a query file path; do not '
      Print '                                       use double quotes within the query.'
      Print '     @vcFrom        (varchar(128))  - Sender list defaulted to @@ServerName.'
      Print '     @vcCC          (varchar(2048)) - CC list separating each with a '';'' or a '','''
      Print '                                       The default is no CC addresses.'
      Print '     @vcBCC         (varchar(2048)) - Blind CC list separating each with a '';'' or a '','''
      Print '                                       The default is no BCC addresses.'
      Print '     @vcSMTPServer  (varchar(255))  - Network smtp server defaulted to your companies network'
      Print '                                       smtp server. Set this in the stored proc code.'
      Print '     @cSendUsing    (char(1))       - Specifies the smpt server method, local or network. The'
      Print '                                       default is network, a value of ''2''.'
      Print '     @vcPort        (varchar(3))    - The smtp server communication port defaulted to ''25''.'
      Print '     @cAuthenticate (char(1))       - The smtp server authentication method defaulted to '
      Print '                                       anonymous, a value of ''0''.'
      Print '     @vcDSNOptions  (varchar(2))    - The smtp server delivery status defaulted to none,'
      Print '                                       a value of ''0''.'
      Print '     @vcTimeout     (varchar(2))    - The smtp server connection timeout defaulted to 30 seconds.'
      Print '     @vcSenderName  (varchar(128))  - Primary sender name defaulted to @@ServerName.'
      Print '     @vcServerName  (sysname)       - SQL Server to which the query is directed defaulted'
      Print '                                       to @@ServerName.'
      Print ''
      Print ''
      Print 'Example:'
      Print 'sp_SQLSMTPMail ''<user@mycompany.com>'', ''This is a test'', @vcSMTPServer = <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 'See the MSDN online library, Messaging and Collaboration, at '
      Print 'http://www.msdn.microsoft.com/library/ for details about CDOSYS.'
      Print 'subheadings: Messaging and Collaboration>Collaboration Data Objects>CDO for Windows 2000>'
      Print 'Reference>Fields>http://schemas.microsoft.com/cdo/configuration/>smtpserver field'
      Print ''
      Print 'Be sure to set the default for @vcSMTPServer before compiling this stored procedure.'
      Print ''
      Return
   End

-- Declare variables
Declare @iMessageObjId    int
Declare @iHr              int
Declare @iRtn             int
Declare @iFileExists      tinyint
Declare @vcCmd            varchar(255)
Declare @vcQueryOutPath   varchar(50)
Declare @dtDatetime       datetime
Declare @vcErrMssg        varchar(255)
Declare @vcAttachment     varchar(1024)
Declare @iPos             int
Declare @vcErrSource      varchar(255)
Declare @vcErrDescription varchar(255)

-- Set local variables.
Set @dtDatetime = getdate()
Set @iHr = 0

-- Check for minimum parameters.
If @vcTo is null
   Begin
      Set @vcErrMssg = 'You must supply at least 1 recipient.'
      Goto ErrMssg
   End

-- CDOSYS uses commas to separate recipients. Allow users to use 
-- either a comma or a semi-colon by replacing semi-colons in the
-- To, CCs and BCCs.
Select @vcTo = Replace(@vcTo, ';', ',')
Select @vcCC = Replace(@vcCC, ';', ',')
Select @vcBCC = Replace(@vcBCC, ';', ',')

-- Set the default SQL Server to the local SQL Server if one 
-- is not provided to accommodate instances in SQL 2000.
If @vcServerName is null
   Set @vcServerName = @@servername

-- Set a default "subject" if one is not provided.
If @vcSubject is null
   Set @vcSubject = 'Message from SQL Server ' + @vcServerName

-- Set a default "from" if one is not provided.
If @vcFrom is null
   Set @vcFrom = 'SQL-' + Replace(@vcServerName,'\','_')

-- Set a default "sender name" if one is not provided.
If @vcSenderName is null
   Set @vcSenderName = 'SQL-' + Replace(@vcServerName,'\','_')

-- Create the SMTP message object.
EXEC @iHr = sp_OACreate 'CDO.Message', @iMessageObjId OUT
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error creating object CDO.Message.'
      Goto ErrMssg
   End

-- Set SMTP message object parameters.
-- To
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'To', @vcTo
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message parameter "To".'

      Goto ErrMssg
   End

-- Subject
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Subject', @vcSubject
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message parameter "Subject".'
      Goto ErrMssg
   End

-- From
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'From', @vcFrom
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message parameter "From".'
      Goto ErrMssg
   End

-- CC
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'CC', @vcCC
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message parameter "CC".'
      Goto ErrMssg
   End

-- BCC
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'BCC', @vcBCC
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message parameter "BCC".'
      Goto ErrMssg
   End

-- DSNOptions
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'DSNOptions', @vcDSNOptions
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message parameter "DSNOptions".'
      Goto ErrMssg
   End

-- Sender
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Sender', @vcSenderName
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message parameter "Sender".'
      Goto ErrMssg
   End

-- Is there a query to run?
If @vcQuery is not null and @vcQuery <> ''
   Begin
      -- We have a query result to include; temporarily send the output to the
      -- drive with the most free space. Use xp_fixeddrives to determine this.
      -- If a temp table exists with the following name drop it.
      If (Select object_id('tempdb.dbo.#fixeddrives')) > 0
         Exec ('Drop table #fixeddrives')
     
      -- Create a temp table to work with xp_fixeddrives.
      Create table #fixeddrives(
             Drive char(1) null,
             FreeSpace  varchar(15) null)

      -- Get the fixeddrive info.
      Insert into #fixeddrives Exec master.dbo.xp_fixeddrives

      -- Get the drive letter of the drive with the most free space
      -- Note: The OSQL output file name must be unique for each call within the same session.
      --       Apparently OSQL does not release its lock on the first file created until the session ends.
      --       Hence this alleviates a problem with queries from multiple calls in a cursor or other loop.
      Select @vcQueryOutPath = Drive + ':\TempQueryOut' +
                               ltrim(str(datepart(hh,getdate()))) +
                               ltrim(str(datepart(mi,getdate()))) +
                               ltrim(str(datepart(ss,getdate()))) +
                               ltrim(str(datepart(ms,getdate()))) + '.txt'
        from #fixeddrives
       where FreeSpace = (select max(FreeSpace) from #fixeddrives )
     
      -- Check for a pattern of '\\*\' or '?:\'.
      -- If found assume the query is a file path.
      If Left(@vcQuery, 35) like '\\%\%' or Left(@vcQuery, 5) like '_:\%'
         Begin
            Select @vcCmd = 'osql /S' + @vcServerName + ' /E /i' +
                            convert(varchar(1024),@vcQuery) +
                            ' /o' + @vcQueryOutPath + ' -n -w5000 '
         End
      Else
         Begin
            Select @vcCmd = 'osql /S' + @vcServerName + ' /E /Q"' + @vcQuery +
                            '" /o' + @vcQueryOutPath + ' -n -w5000 '
         End

      -- Execute the query
      Exec master.dbo.xp_cmdshell @vcCmd, no_output

      -- Add the query results as an attachment if the file was successfully created.
      -- Check to see if the file exists. Use xp_fileexist to determine this.
      -- If a temp table exists with the following name drop it.
      If (Select object_id('tempdb.dbo.#fileexists')) > 0
         Exec ('Drop table #fileexists')
     
      -- Create a temp table to work with xp_fileexist.
      Create table #fileexists(
             FileExists tinyint null,
             FileIsDirectory  tinyint null,
             ParentDirectoryExists  tinyint null)

      -- Execute xp_fileexist
      Insert into #fileexists exec master.dbo.xp_fileexist @vcQueryOutPath

      -- Now see if we need to add the file as an attachment
      If (select FileExists from #fileexists) = 1
         Begin
            -- Set a variable for later use to delete the file.
            Select @iFileExists = 1

            -- Add the file path to the attachment variable.
            If @vcAttachments is null
               Select @vcAttachments = @vcQueryOutPath
            Else
               Select @vcAttachments = @vcAttachments + '; ' + @vcQueryOutPath
         End
   End

-- Check for multiple attachments separated by a semi-colon ';'.
If @vcAttachments is not null
   Begin
      If right(@vcAttachments,1) <> ';'
         Select @vcAttachments = @vcAttachments + '; '
      Select @iPos = CharIndex(';', @vcAttachments, 1)
      While @iPos > 0
         Begin
            Select @vcAttachment = ltrim(rtrim(substring(@vcAttachments, 1, @iPos -1)))
            Select @vcAttachments = substring(@vcAttachments, @iPos + 1, Len(@vcAttachments)-@iPos)
            EXEC @iHr = sp_OAMethod @iMessageObjId, 'AddAttachment', @iRtn Out, @vcAttachment
            IF @iHr <> 0
               Begin
                  EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
                  Select @vcBody = @vcBody + char(13) + char(10) + char(13) + char(10) +
                                   char(13) + char(10) + 'Error adding attachment: ' +
                                   char(13) + char(10) + @vcErrSource + char(13) + char(10) +
                                   @vcAttachment
               End
            Select @iPos = CharIndex(';', @vcAttachments, 1)
         End
   End

-- TextBody
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'TextBody', @vcBody
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message parameter "TextBody".'
      Goto ErrMssg
   End

-- Other Message parameters for reference
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MimeFormatted', False
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'AutoGenerateTextBody', False
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MDNRequested', True

-- Set SMTP Message configuration property values.
-- Network SMTP Server location
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
@vcSMTPServer
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message configuraton field "smtpserver".'
      Goto ErrMssg
   End

-- Sendusing
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
@cSendUsing
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message configuraton field "sendusing".'
      Goto ErrMssg
   End

-- SMTPConnectionTimeout
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout").Value',
@vcTimeout
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message configuraton field "SMTPConnectionTimeout".'
      Goto ErrMssg
   End

-- SMTPServerPort
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPServerPort").Value',
@vcPort
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message configuraton field "SMTPServerPort".'
      Goto ErrMssg
   End

-- SMTPAuthenticate
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate").Value',
@cAuthenticate
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error setting Message configuraton field "SMTPAuthenticate".'
      Goto ErrMssg
   End

-- Other Message Configuration fields for reference
--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPUseSSL").Value',True

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/LanguageCode").Value','en'

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendEmailAddress").Value', 'Test User'

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendUserName").Value',null

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value',null

-- Update the Message object fields and configuration fields.
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Configuration.Fields.Update'
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error updating Message configuration fields.'
      Goto ErrMssg
   End

EXEC @iHr = sp_OAMethod @iMessageObjId, 'Fields.Update'
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error updating Message parameters.'
      Goto ErrMssg
   End

-- Send the message.
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Send'
IF @iHr <> 0
   Begin
      Set @vcErrMssg = 'Error Sending e-mail.'
      Goto ErrMssg
   End
Else
   Print 'Mail sent.'

Cleanup:
   -- Destroy the object and return.
   EXEC @iHr = sp_OADestroy @iMessageObjId
   --EXEC @iHr = sp_OAStop

   -- Delete the query output file if one exists.
   If @iFileExists = 1
      Begin
         Select @vcCmd = 'del ' + @vcQueryOutPath
         Exec master.dbo.xp_cmdshell @vcCmd, no_output
      End
   Return

ErrMssg:
   Begin
      Print @vcErrMssg
      If @iHr <> 0
         Begin
            EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out, @vcErrDescription Out
            Print @vcErrSource
            Print @vcErrDescription
         End

      -- Determine whether to exist or go to Cleanup.
      If @vcErrMssg = 'Error creating object CDO.Message.'
         Return
      Else
         Goto Cleanup
   End
GO