VBA Code to Keep File Open

[Michael Fowler]

I'm successfully using an Excel file with VBA code to send a copy of the Active Sheet as an attachment in an email, and this works very well. But now I have a similar situation except that now I need to send the whole file rather than just one sheet. I've changed this part of the code ...

Set Wb = Application.ActiveWorkbook
ActiveSheet.Copy
Set Wb2 = Application.ActiveWorkbook

to this ...

Set Wb = Application.ActiveWorkbook
ActiveWorkbook.Copy
Set Wb2 = Application.ActiveWorkbook

... and as a result of doing this, the original source Excel file closes after running the macro, whereas it doesn't when I'm only sending one sheet. Please advise what coding I need to insert or modify to keep the original Excel source file remaining open.

Thanks, Michael

 

[NdNoviceHlp]

Hi Micheal. SaveCopyAs might be better for you. See this link below. HTH. Dave

VBA whats the diff between SAVEAS and SAVECOPYAS?

Hi All, Whats the diff between SAVEAS and SAVECOPYAS in VBA? Thanks

www.mrexcel.com

 

[Michael Fowler]

Following on from my initial question, what VBA code do I need to send specific sheets in an email ? (I already have the code to send one sheet.)

 

[NdNoviceHlp]

" except that now I need to send the whole file rather than just one sheet" & "the original source Excel file closes after running the macro" Seemed like 2 issues that you were interested in. This seems different? If you want to send only some sheets copy the file to a new location, removed the undesired sheets and then e-mail it. Dave