Automate Excel sheet to create attachment. 2

[kphu]

Greetings All...

I have a weekly sales report that I create for each sales rep and email each individual report. The amt of time spent sending each email is getting to be a nuisance.

I've been working on automating my excel sheet so that it creates an email of itself as an attachment. The problem that I'm having is that when it creates the email itself it creates a name of True.xls instead of the name that I saved it as. Was wondering if anyone knows how to keep it as its current name.

This is my basic code so far.

ActiveWorkbook.SaveAs Application.Dialogs(xlDialogSaveAs).Show
Activeworkbook.sendmail Application.Dialogs(xlDialogSendMail).Show

Any help would be greatly appreciated.

Thanks,

Ken

 

[SkipVought]

Hi,

Do you do the Save As before the Send Mail dialog?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[kphu]

yes, I save the file before the send mail dialog.

 

[SkipVought]

I am confused.

You rename the file and then do the send mail dialog and it uses the PREVIOUS filename?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[kphu]

I create a new file.

I save the file with the following code.

ActiveWorkbook.SaveAs Application.Dialogs(xlDialogSaveAs).Show

The dialog comes up and it saves the file with the new name and location\path that I set it as.

Then the next statement.

Activeworkbook.sendmail Application.Dialogs(xlDialogSendMail).Show

executes and composes an email with the file attached.
The problem is that the name of the file is now True.xls instead of the name I gave it.

 

[SkipVought]

Here's what you need to do...

Application.Dialogs(xlDialogSaveAs).Show
Activeworkbook.sendmail Application.Dialogs(xlDialogSendMail).Show

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[kphu]

Thanks skip, I appreciate your help. Can't believe it was something as simple as that. You earned your star today.

 

[Chattin]

For info here's a load of good stuff about mailing with xl

http://www.rondebruin.nl/sendmail.htm

HTH

 

[kphu]

Good info...for that you get a star.