A macro to copy a worksheet to a new workbook and create an email draft with the new ws

I have a workbook with several tabs. Since there is no way to email only one worksheet from a workbook, I am trying to create a macro that will copy the worksheet "DV Only" from workbook "Yokota Leadership with Spouses" and I then want it to attach the new "DV Only" spreadsheet to outlook with the subject header "DV Info".

Not sure if this is possible, but thank you for your help. I'm very inexperienced in VBA so I'm hoping I can record it with macro, if not, I'll give it a shot.

Thank you

The below should help get you started. I've added a few comments to explain some of it but feel free to ask questions.
It uses Outlook to send the email. You will need to amend it if that's not the case for you.

Please Login or Register  to view this content.

BSB

I am getting a Compile error. This is what I put in my VBA.

VBA error.jpg

Originally Posted by Keebellah

You have to add Outlook reference to the tools in your VBA

Not quite. The code is 'late binding' and therefore needs no reference to be added.

The problem is you're missing a vital part of the code.
The first line should read:

Please Login or Register  to view this content.

BSB

Thank you for the excellent response, that solved it. I may have another question coming up, I want a VBA to do the same thing, but I want it to add all the email addresses on worksheet "DV Emails" Column B2-B90. If Column A=DV then I want the email address in the TO: box, if A2=DVS I want the email address in CC: Let me know if I need to close this and open a new topic.