MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Email An Excel Workbook

January 15, 2002 - by Bill Jelen

Thanks to Katherine who sent the question for this week's Excel tip:

I want to place a form button on an Excel worksheet that, when clicked, will email the same Excel worksheet to someone.

To send the whole book to with a particular subject line, you would use this Excel macro.

Sub SendIt()
    Application.Dialogs(xlDialogSendMail).Show  arg1:="",  _
					  arg2:="This goes in the subject line"
End Sub

There is a toolbar icon that lets you add a command button. Follow these steps to add it to an existing toolbar:

  • View>ToolBars>Customize
  • Click the commands tab
  • In the Category box, scroll down to Control Toolbox
  • In the commands box, scroll down to Command Button
  • Click the command button and drag it up and drop it on an existing toolbar
  • Dismiss the Customize dialog by hitting Close

Now that you have the command button icon: Click the icon. Drag in your worksheet to create the button. Make it as big or as small as you want. Excel will ask you to assign a macro to the button. Pick SendIt. Click and drag to highlight the word Button 1 on the button. Type something like "E-Mail this File". Click outside of the button, and you are ready to go. If you ever need to reassign the macro attached to that button, right-click the button and pick assign macro.

Sample Data Set

What if you have a large list of information which you want to distribute to a number of users, but you only want each user to see their own information? Below is a slightly more complex macro which will do this. At the right is a sample list of sales for three regions.

Before writing the macro, set up the workbook as follows:

Report Worksheet
  • Copy the data worksheet by right clicking the data tab, pick "move or copy", check "create a copy", OK. Rename this new worksheet "Report". You can rename a sheet by right clicking the tab, picking "rename", then typing Report.
  • On the report tab, delete all of the data rows. Keep only the headings and titles.
Distribution Worksheet
  • Insert a new worksheet with these columns: Region, Recipient. On this worksheet, type each region name and the user who should receive the report. Rename this worksheet "Distribution".

Here is the macro. Lines that start with apostrophes (') are comments designed to help you follow the flow.

Public Sub SendItAll()
    ' Clear out any old data on Report
    ' Sort data by region
    Selection.Sort Key1:=Range("A2"), Header:=xlYes
    ' Process each record on Distribution
    FinalRow = Range("A15000").End(xlUp).Row
    For i = 2 To FinalRow
        RegionToGet = Range("A" & i).Value
        Recipient = Range("B" & i).Value
        ' Clear out any old data on Report
        ' Get records from Data
        ' Turn on AutoFilter, if it is not on
        If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
        ' Filter the data to just this region
        Selection.AutoFilter Field:=1, Criteria1:=RegionToGet
        ' Select only the visible cells and copy to Report
        Selection.Copy Destination:=Sheets("Report").Range("A1")
        ' Turn off the Autofilter
        ' Copy the Report sheet to a new book and e-mail
        Application.Dialogs(xlDialogSendMail).Show _
            arg1:=Recipient, _
            arg2:="Report for " & RegionToGet
        ActiveWorkbook.Close SaveChanges:=False
    Next i
End Sub

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.