Email Excel as an attachment - with a Kick! 2

[spayne]

OK, I know how to automate Outlook and send an Excel spreadsheet as an attachment, but I need it to behave like when you select "Send To" and then "Mail Recipient (As Attachment) from the File menu. In other words, send the spreadsheet with the changes to it, without actually saving the changes to disk. This is a template file and we need to have it's original settings remain as is.

I am using Excel 2000 and Outlook 2003 if that helps.

Thanks in advance for any suggestions.

 

[SkipVought]

Hi,

How about, in Excel File > Send to...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[spayne]

Well, is there a VBA command that will allow this? Maybe our users are just lazy (ya think?), but they just want to be able to click a hyperlink in the spreadsheet to open up a new Outlook mail message with the appropriate email address added and the file attached.

Is there a way to automatically add the email address through File > Send to...?

 

[SkipVought]

Turn on your macro recorder and have at it.

Post back with your recorded code if you need help customizing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[spayne]

Well, here's my code and it works fine, except if I don't save the file, the changes made won't be included in the attachment. When you use File > Send to..., these changes ARE included in the attachment without saving, hence, my original request.


Dim objol As Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.createitem(olmailitem)
With objmail
.To = "Me@MyCompany.com"
.Subject = "My Subject"
.NoAging = True
.Attachments.Add "G:\My Path\My File.xls"
.display
End With
Set objmail = Nothing
Set objol = Nothing

 

[SkipVought]

That does not look like recorded code to me.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

very much not so!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ettienne]

Why not save the file as something else temporarily and then send it?
I understand you do not want to save over the original file.

 

[spayne]

Well, I have done very little in Excel, so cut me some slack. ;-) I've never had to create a macro before, so thanks for pointing me in the right direction, Skip.

I found this snippet of code on the Web and modified it and it works just like I need it to.

Application.Dialogs(xlDialogSendMail).Show "FName.LName@companyname.com", "Important Document", True

 

[SkipVought]

That's EXACTLY where recording a macro would have lead to.

Trying to get you to learn to fish, rather than giving you a fish sandwich.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}