Email Macro with Active Sheet as body of the attachment ... AND ... .pdf attachment

[kevn.hudson]

Help! I'm working to create a macro within Excel 2007 that emails (via Outlook 2007) the contents of a single worksheet within the body of an email ... AND ... attaches a .pdf file to email. I'd rather not create a new email if at all possible.

Below is the code I'm using to create the email, and it has shown the ability to successfully send the contents of a worksheet within the body of the email.


Sub Send_Range()
ActiveSheet.Range("A1:J58").Select

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
.Introduction = "This is a sample worksheet."
.Item.To = "xxxx@email.com"
.Item.Subject = "My subject"
.Item
End With

End Sub

BUT!!! Every time that I use the following command line (in theory to add the single file to be attached to the email), the debugger comes back and flags a problem.

.Attachments.Add "c:\users\ccccc.pdf"

I've also tried to use this command line, also to no avail ...
.Item.Attachments.Add "c:\users\ccccc.pdf"[/COLOR]

Any ideas, suggestions, thoughts, help ...

Signed, Completely Baffled

 

[sous2817]

do you need the .add part?

Have you tried

.Attachments = "c:\users\ccccc.pdf"

 

[kevn.hudson]

Hi Sous ... I did try it without the .add ... I also tried it as an .Item.Attachment ... In this case, it did send an email and made it through the debugger without an error - but didn't attach the file.

 

[sous2817]

are you sure that the file exists in the path that you have in your code?

 

[kevn.hudson]

Yep, I checked that ...

 

[sous2817]

Sorry, I'm at a loss. Hopefully someone else can chime in and save the day...good luck!

 

[Norie]

You want to send an email and not actually create it.