Show worksheet name on Email attachment

[birchetts]

Good morning

I have successfully written a macro which allows me to email an individual worksheet from a workbook:

Sub eMail()
ActiveSheet.Copy
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close SaveChanges:=False
End Sub

But the attachment shows 'Book1.xls' rather than the actual worksheet name, is there a way to alter this?

Thank you

[Mallycat]

This is happening because you are copying a Sheet from your workbook, effectively creating a new book. why don't you just delete the first line

Please Login or Register  to view this content.

Alternatively, you need to rename the new book to be something similar, but not the same as the main book

Please Login or Register  to view this content.

[birchetts]

Thanks Mallycat, I have tried both your suggestions but maybe am doing something wrong??, I am a complete novice at this so you need to bear with me..., each worksheet I have is actually an invoice which will be sent to separate clients, there are approx 60 worksheets in the workbook so each time I highlight the worksheet I just want to be able to invoke the macro and for the attachment to reflect the name of the worksheet(i.e. client).
I have tried deleting the first line'ActiveSheet.Copy' but am advised that there is a sytax error, please could you clarify what you mean?
I have also tried the 2nd code and it just comes up with the name of the workbook file name?.
In essence I really don''t know how to incorporate your suggestions, are you suggesting that there should be a macro for each worksheet with the worksheet name in it?

Thanks for your patience

birchetts

[Mallycat]

OK, sorry my mistake. I misread the detail.

Try this

Please Login or Register  to view this content.

this will leave a copy of the saved workbook on your drive.

[birchetts]

This works great Mallycat thank you very much, can you just clarify what you mean when you say 'this will leave a copy of the saved workbook on your drive'?, is this in addition to the email copy and where would it be located?

[Mallycat]

Yes, it leaves a copy of the sheet in your folder. I didn't have time to sort this out this morning. You could try this. Please be careful and back up first. This is crashing my Excel, but I think the code is correct but there is something wrong with my PC. but I can't be sure.

Please Login or Register  to view this content.

[birchetts]

Sorry to seem dumb but where is this folder located as I can't see it?

[Mallycat]

Just open a book, and then save it (file save as). Where ever the book is saved, that is the folder

[birchetts]

Many thanks again Mallycat