Macro to E-Mail an Excel Workbook
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 firstname.lastname@example.org with a particular subject line, you would use this Excel macro.
Sub SendIt() Application.Dialogs(xlDialogSendMail).Show arg1:="email@example.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:
- 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.
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:
- 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.
- 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