Button to Email - and everything in between

[NamssoB]

I have a project and I'm looking for some help. My first question is, will what I need actually be possible? Here is some info:

- I have a workbook with three t abs (Main, Data, Extras).
- In the Main worksheet, I have multiple named sections (Accounts, Matrix, & Payouts).
- I want to add a button to the worksheet that emails ONLY the Accounts named space as an Excel file attachment (NOT an image, or pasted into an email body). None of the other tabs should be included, nor should the other named areas.

My thought for the steps would be something like this:
- Copy the Named area to a new tab
- Delete the original tabs
- Save as a NEW Excel file (resulting in one workbook with one worksheet - with one named area (Accounts)
- Open Outlook object with appropriate To/From header information, including a brief body message
- Attach the new Workbook - and SEND

^ Can this even be done? if yes, I don't know how to make it work. I can make the button, and probably figure out the copy & past of the named Account area. But after that, I don't know how to remove the original tabs while preserving the code to do everything. I also don't know how to attach the existing (running) workbook into an Email and send it (using Outlook).

 

[ChrisBM]

Good news, it can be done and it's not immensely difficult either. Give me a few minutes, I'll write it for you

 

[ChrisBM]

    'Create new excel workbook
    Dim EmailBook as Workbook: Set EmailBook = Application.Workbooks.Add
  
    'Copy over appropriate range
    ThisWorkbook.Sheets("Main").Range("Accounts").Copy Destination:=EmailBook.Sheets(1).Range("A1")

    'Rename Sheet
    EmailBook.Sheets(1).Name = "Accounts"    

    'Save new book
    EmailBook.SaveAs (FILEPATH)

    'Prepare Email
    EmailBook.Application.Dialogs(xlDialogSendMail).Show

I am pretty sure that is as short as you can make it.

Hope this helps,
Chris

 

[NamssoB]

Great - thanks! This works!

In the meantime, I also found this site below that works also. Thanks again!!

Mail Range or Selection