Attaching File to Mail

[jpiscit1]

I have an existing stored procedure that sends mail for us. Since we use SMTP (Lotus Notes) mail we could not use the conventional xp supplied for MAPI.

With that, I am trying to write a stored procedure that will attach a file when sending mail. For those of you that helped with my previous problem your probably realizing I had to much data to send in a simple text format. Here's where I am:

The existing procedure we call to send mail is:

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

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference 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

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
--

http://msdn.microsoft.com/library/d...ml/_cdosys_schema_configuration_sendusing.asp

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', 'mailservername'

-- 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

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
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 need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO


The query I am executing to send simple text messages is:

declare @Body varchar(4000)
select
@body = 'See attached production report'
Select @Body
exec sp_send_cdosysmail 'someone@microsoft.com','someone2@microsoft.com','Test of CDOSYS',@Body

Where someone = the sender and someone2 is the reciever. What code do I need to add to attach a file to this email?

Any help you can provide would be greatly appreciated...

Thanks!

John

 

[osjohnm]

Hi

Here is a script to create a stored proc that uses SMTP so there is no need to use a MAPI profile on the server. I haven't used it yet but it does allow you to add attachments. I found it at

http://www.sqlservercentral.com.

It seems to create a table to store the attachments in and then generates a file to attach from this table.

Be warned it is long

if exists (select *
from sysobjects
where id = object_id(N'[dbo].[sp_SQLSMTPMail]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SQLSMTPMail]
GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

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) = '',
@vcBCC varchar(2048) = '',
@vcSMTPServer varchar(255) = '', -- 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',
@vcSenderName varchar(128) = null,
@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.
--
--Parameters : See the 'Syntax' Print statements below or call the
-- sp with '?' as the first input.
--
--Date : 08/22/2001
--Author : Clinton Herring
--
--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 ''
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 char(25)
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 &quot;subject&quot; if one is not provided.
If @vcSubject is null
Set @vcSubject = 'Message from SQL Server ' + @vcServerName

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

-- Set a default &quot;sender name&quot; 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 &quot;To&quot;.'
Goto ErrMssg
End

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

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

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

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

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

-- Sender
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Sender', @vcSenderName
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter &quot;Sender&quot;.'
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 wof the drive with the most free space
Select @vcQueryOutPath = Drive + ':\TempQueryOut.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&quot;' + @vcQuery +
'&quot; /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 &quot;TextBody&quot;.'
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(&quot;

http://schemas.microsoft.com/cdo/configuration/smtpserver&quot;).Value',

@vcSMTPServer
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field &quot;smtpserver&quot;.'
Goto ErrMssg
End

-- Sendusing
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields(&quot;

http://schemas.microsoft.com/cdo/configuration/sendusing&quot;).Value',

@cSendUsing
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field &quot;sendusing&quot;.'
Goto ErrMssg
End

-- SMTPConnectionTimeout
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields(&quot;

http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout&quot;).Value',

@vcTimeout
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field &quot;SMTPConnectionTimeout&quot;.'
Goto ErrMssg
End

-- SMTPServerPort
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields(&quot;

http://schemas.microsoft.com/cdo/configuration/SMTPServerPort&quot;).Value',

@vcPort
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field &quot;SMTPServerPort&quot;.'
Goto ErrMssg
End

-- SMTPAuthenticate
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields(&quot;

http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate&quot;).Value',

@cAuthenticate
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field &quot;SMTPAuthenticate&quot;.'
Goto ErrMssg
End

-- Other Message Configuration fields for reference
--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields(&quot;

http://schemas.microsoft.com/cdo/configuration/SMTPUseSSL&quot;).Value',True

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields(&quot;

http://schemas.microsoft.com/cdo/configuration/LanguageCode&quot;).Value','en'

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields(&quot;

http://schemas.microsoft.com/cdo/configuration/SendEmailAddress&quot;).Value',

'Test User'

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields(&quot;

http://schemas.microsoft.com/cdo/configuration/SendUserName&quot;).Value',null

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields(&quot;

http://schemas.microsoft.com/cdo/configuration/SendPassword&quot;).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

Grant Execute on dbo.sp_SQLSMTPMail to Public
Go

Hope that helps

John