create attachement on E-Mail

Question

Post reply

create attachement on E-Mail

Narrie

Valued Member

Points: 67
More actions
May 30, 2003 at 7:47 am

#59137

I had a problem previously, when I could not use xp_sendmail because I did not have an Exchange sever. Got myself access to SMTP server and used the stored proc below to send mail:
REATE 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
AS
Declare @MailID int
Declare @hr int
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_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
The now is that I need to insert an attachemnt to the E-Mail. How do I do that?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Shas3 Hall of Fame Points: 3671 More actions · Answer 1

Your procedure is very confusing.

Here is the send mail example with attachments

EXEC xp_sendmail @recipients = 'Email@email.com',

@query = 'Query you want to specify',

@subject = 'Subject you want',

@attach_results = 'TRUE', @width = 250

Shas3

Narrie Valued Member Points: 67 More actions · Answer 2

Unfortunately Shas3, As I mentioned previously I do not have an exchange sever which is a pre-requisite for xp_sendmail.

matt1 SSCommitted Points: 1784 More actions · Answer 3

narrie, does you code actually create and send email using a trigger?

thanks,

matt

matt1 SSCommitted Points: 1784 More actions · Answer 4

narrie, does you code actually create and send email using a trigger?

thanks,

matt

Narrie Valued Member Points: 67 More actions · Answer 5

You have to execute the store proc via a package. All you need to do is pass the parameters(i.e. sender E-mail,Receipients E-mail address, Header and body content of the mail) to the stored proc.

mccork SSC-Insane Points: 22288 More actions · Answer 6

Narrie,

Try:

.

EXEC @hr = sp_OASetProperty @MailID, 'To', @To

-- Add the attachment file

EXEC @hr = sp_OAMethod @MailID, 'AddAttachment', NULL, 'InsertAttachmentPath&FileNameHere'

EXEC @hr = sp_OAMethod @MailID, 'Send', NULL

EXEC @hr = sp_OADestroy @MailID

Cheers,
- Mark