MrExcel.com - Your One Stop for Excel Tips & Solutions

Email An Excel Workbook

January 15, 2002 - by

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 ask@mrexcel.com with a particular subject line, you would use this Excel macro. 

Sub SendIt()
    Application.Dialogs(xlDialogSendMail).Show  arg1:="ask@mrexcel.com",  _
					  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
    Sheets("Report").Select
    Range("A1").CurrentRegion.ClearContents
    ' Sort data by region
    Sheets("Data").Select
    Range("A1").CurrentRegion.Select
    Selection.Sort Key1:=Range("A2"), Header:=xlYes
    ' Process each record on Distribution
    Sheets("Distribution").Select
    FinalRow = Range("A15000").End(xlUp).Row
    For i = 2 To FinalRow
        Sheets("Distribution").Select
        RegionToGet = Range("A" & i).Value
        Recipient = Range("B" & i).Value
        ' Clear out any old data on Report
        Sheets("Report").Select
        Range("A1").CurrentRegion.ClearContents
        ' Get records from Data
        Sheets("Data").Select
        Range("A1").CurrentRegion.Select
        ' 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.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy Destination:=Sheets("Report").Range("A1")
        ' Turn off the Autofilter
        Selection.AutoFilter
        ' Copy the Report sheet to a new book and e-mail
        Sheets("Report").Copy
        Application.Dialogs(xlDialogSendMail).Show _
            arg1:=Recipient, _
            arg2:="Report for " & RegionToGet
        ActiveWorkbook.Close SaveChanges:=False
    Next i
End Sub