Change the Name of a Temporary File

I have a macro that will make a copy of one sheet and send it out via email.

But the name of the temporary file is always called "Book1.xls" and the number steps up each time.

I would like to name the file that gets sent as the same name as the subject line

Please Login or Register  to view this content.

I did note that if I change statement

Please Login or Register  to view this content.

to True it would save the file in this folder: "C:\Program Files\Common Files\System\MSMAPI\1033".

I do not want to keep the temporary file on my PC. Any ideas on how to rename the Book1.xls to

Please Login or Register  to view this content.

Hello whisperinghill,

Save the file then remove it.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Leith, that didn't quite work, but I understand it
here are the changed portions of the code

Please Login or Register  to view this content.

When it gets to the Save changes, it brings up the save Book1.xls? dialog box
I think what is missing is a line something like this before it sends the message

Please Login or Register  to view this content.

But this doesn't work.
More help?

Hello mkebe84,

Let's try this approach. This will close a new workbook without saving or prompting.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

No The idea was to name the temporary file that was to be sent and the deleted.

Here is waht works

Please Login or Register  to view this content.

What should have worked was

Please Login or Register  to view this content.

But it didn't. Though I really don't want it to delete all the .xls files possibly that may be there temprarily.
What I have now works with one exception and that is, when it creates the temporary file, before saving it with the new name is asks if I want to save file Book1.xls. I want to just do the work and not ask any questions and require another click. What is the easy way to have the macro click the yes box on the pup-up?

Hello whisperinghill,

Since the default button of the Save Changes dialog is "Yes", we can have the system "click" it for us.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Worked like a charm! Thanks for all input

Here was the final code which copies one single shhet of the workbook, names it from a named range within the worksheet with date added and then send an email with it as an attachment with the Subject named the same as the file, minus the .xls

Please Login or Register  to view this content.