Create email from partial workbook

[Borg2of6]

I have a workbook which has quite a lot of vba code and forms in it. I need to create an option to allow an email to be created (but not to be sent automatically) to forward details to other people. However due to the total size of the workbook(over1.25MB) the email will not be accepted by some email servers. I also can not zip the file as at least intended recipient does not have WinZip (or equivalent) and is not allowed to install outside / new software onto the pc.

Is there a way to create a new Excel spreadsheet (under vba control) of several sheets from the workbook and attach this new workbook to an email?

The new workbook will contain copies of worksheets 7-11 from the original workbook. These will be copies of sheets 2-6 but with cell cross-references removed and replaced by static current value (ie I am using Paste Special - Values only).

The static worksheets are required as they need to be read only (with password protection applied by vba - password not supplied by user but coded into vba) for audit purposes.

In advance of your help - Thank you.

[Ecniv]

Should be able to create a new workbook, add in a sheet. Set a range object to the whole of the source sheet. Copy it and paste into the new destination sheet. Repeat for how ever many you want.

Passwording etc, probably available too.

Something like:

dim wrk as workbook
dim shtSrc as worksheet, shtdest as worksheet

set wrk = application.workbooks.add
set shtDest = wrk.sheets.add
'...

[Borg2of6]

Please can you expand on above as I am unsure how this works.
How does excel know which workbook is being referenced?
How to I copy across multiple sheets?
How do I attach newly created workbook to an email. As I see it the new workbook will have no vba.