stored procedure email attachments CDONTS

[Shal2]

Hi all,

I am using the stored procedure below to send emails. It was posted by Colin Leversuch-Roberts on the web.

It works very well, I get the emails, but without the attachment. Is it because of the file path I have given which is on my local machine? Should the file be on the database server? I don't know much about database server stuff.

Any help is appreciated,
thanks,

Shal


exec sp_send_cdontsmail 'your.account@your-company.com', 'another.account@another-company.com', 'Inbound File', 'Please find attached Inbound Export.', null, null, 'C:\Documents and Settings\Shal\My Documents\testwrite.txt'


CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null,
@Attachment varchar(150) = null
AS
Declare @MailID int
Declare @hr int
print @attachment
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OASetProperty @MailID, 'MailFormat', 0 -- MIME format to ensure attachments are sent correctly via internet
EXEC @hr = sp_OAMethod @MailID, 'AttachFile', NULL, @Attachment
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
GO

 

[ClaireHCM]

Your guessing is right, when you execute T-SQL with filepath, SQL Server look for the file located in the server itself, so us UNC path instead.

The only exception is spcified filepath in DTS package. So dont get confused with the operation in DTS package.

 

[Shal2]

Thank you ClaireHsu,

I will work on this.

Regards,
Shal

 

[eja2000]

i ran the following against the proc above and the email was sent but there was no attachment;

exec sp_send_cdontsmail 'your.account@your-company.com', 'another.account@another-company.com', 'Inbound File', 'Please find attached Inbound Export.', null, null, '//ZXU10RTS8421/c/mail_tests/

http://www_xxx_11_09_04.xls'

can anyone tell me where i went wrong?
thanks.. ZXU10RTS8421 is my machine name.

 

[danshelb]

Two things I see...first your slashes are wrong, should be \\ZXU10RTS8421\... instead of //.

Second for the c in \\ZXU10RTS8421\c, are you sure you don't want the admin share c$ instead?

Hope this helps.

Dan