Button to Email - and everything in between

NamssoB

Board Regular
Joined
Jul 8, 2005
Messages
62
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).
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

ChrisBM

Board Regular
Joined
Sep 22, 2014
Messages
215
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

Board Regular
Joined
Sep 22, 2014
Messages
215
Code:
    '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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,359
Messages
5,528,226
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top