Email Reports to Multiple Recipients using VBA

In this post I will show you how to email reports (as attachments) to various recipients via Outlook using VBA. It is a less commonly-used function of VBA to control other Windows processes beyond Excel, but no less powerful. Once we have the Macro in place, we will be able to:

1),Send territory-level reports to corresponding territories. Each territory will only receive one email with one report attached.

2), Send each email with customized Subject line and body message showing, for example, territory name.

Before I show the code, we need email addresses for all territories as shown in the following snapshot:

Add the following Macro to your workbook. Download the example here.

The code above calls an Outlook object and then create a new mail item. Once that's done, we can change the properties of that item (such as .To, .Subject, .Body) to send an email to a specific address with specific messages. Finally, .Attachments.Add method is used to attach the currently opened workbook with the outgoing email.

Several things to keep in mind:

1), Outlook must be open and running when you execute the Macro.

2), Outlook may give a security warning every time an email is sent. Readjust the security settings in your Outlook to disable that message.

3), To add a line break in the body of the message, use vbCrLf as part of the string.

Finalize Call Panel Report - Add Filtering, Remove Opt-out MD Info, Protect Sheets, and Some More

Let's add some final features to our national level call panel file and call it a day.

One is to add the function for the end-user to filter records by specialty. For example if the user chooses "Spec3", then only those MDs with a specialty of "Spec3" would show up. All others will be hidden. Not the most fancy, but many times it would prove to be useful for people not familiar with using Auto-Filter.

To do this, we create another drop-down list which allows the user to choose one of the six specialties, and add the following code. You can download the workbook here.

In the above code we use "RowHeight" property to display/hide rows. Here we set "RowHeight" to 13 (default row height on my computer) to display the rows; and to 0 to hide them. If necessary, this property can also be read first to determine what is the default row height on the end-user's machine.

Let's move on to another topic: Opt-out physicians - doctors who do not wish to share their information (can be sales data, can be demographic info) with any sales reps. Biotech managers are legally required to remove such information in any reports sent to the sales force. In our example, let's assume that we have a list of 400 physicians who are opt-out doctors. The only information we are allowed to show is their MD#, Region and Territory, Specialty and Name. All other fields need to be blocked. The VBA code is shown below:

We can see from the above code that this 400 Opt-Out physician list is on sheet "DropDownList" and occupies the range "G1:G400". If you do not wish to show this list to the sales force, you can choose to hide the sheet (see below), or clear the list by using .Range().ClearContents, or have the list resided on a different Excel file.

Finally, before we send out the file to everybody, we would like to hide the sheets "Ter List" and "DropDownList" by using:

.Sheets(1).Visible = False

If you also prefer to hide column and row headings ("A", "B", "C", etc.) you can use:

ActiveWindow.DisplayHeadings = False

Some people prefer to protect all sheets in the workbook with a password so that no (unintentional) changes can be made to any contents within the sheets. To do this, use:

.Sheets(1).Protect(Password)

Word of caution. After you protect a sheet, if a Macro needs to write to/change the sheet, an error will be generated. To avoid this, use:

.Sheets(1).UnProtect(Password)

to temporarily unprotect the sheet, do your thing, then protect it again.

Finally, to protect the VBA code itself with a password (after all, any person with some VBA knowledge can easily go through your code and retrieve the password contained in the above Protect and UnProtect methods), go to VBA Editor, right click on the VBA Project, and choose "VBAProject Properties...".

Add Sorting and Filtering Capabilities to Reports - Part II

In the last post all sortings are done on either an Ascending or Descending order. For example, when we choose "Specialty" in the drop-down list, the entire physician record is sorted based on an ascending order of "Spec1 -> Spec2 -> Spec3 -> Spec4 -> Spec5 -> Spec6". No surprise here. However, how do we customize the above sorting order? What if "Spec3" is the most important specialty for our sales force and we want those physicians to always appear at the top when sorted? Let's further assume that we would like to sort "Specialty" from top to bottom in an order of "Spec3 -> Spec2 -> Spec6 -> Spec4 -> Spec1 -> Spec5". Can we do this?

It turns out we can, by using something called "Custom Lists". To see your current "Custom Lists", assuming that you are using Excel 2007-2010, go to Excel Options -> Popular -> Edit Custom Lists. Our task is to programatically add a new "Custom List" so that Excel VBA may use that list to perform a customized sorting.

Here is the code. Download the example here.

The key section in the above code utilizes a method called Application.GetCustomListNum(), which returns the position of a particular custom list within all the custom lists on end-user's machine. The method will return 0 if the custom list has not been found. Then we can use Application.AddCustomList to add our custom list. Once we know for sure that there is such custom list on the user's machine, we can use .Range().Sort OrderCustom=CustomListNum+1. Note that we always need to make sure to add 1 to the CustonListNum. Don't ask me why.

For those people who do not want to leave their custom lists on end-user's machine when the user closes the workbook, you can add the following code to your excel file's "ThisWorkBook" section.

Add Sorting and Filtering Capabilities to Reports - Part I

One feature that always helps the usability of reports is the addition of various sorting (i.e. re-arranging the order of records) and filtering (i.e. show/hide records based on certain criteria) capabilities for the end-user. The easiest way to include such functionality is to add "Auto-Filter" to the header row using the following code:

.Range("A3:L3").AutoFilter

The Range() object above would contain all the headers of the list. Run the code again, you would remove the Auto-Filter.

Problem solved, right? Except things rarely get that easy. In reality people who are not Excel geeks like you and me may not feel comfortable using Auto-Filter and many of its functions. In oder to create a more user-friendly report, I always recommend using drop-down lists to let the user choose which metric to sort or filter. See a snapshot below:

You can see that once again we are using our physician record file "Mikoudai Inc Sales Hierarchy.xls" as the example. The above drop-down list (also called Combo Box) resides in the worksheet "Nat Data", and obtains its listed items from the range "A1:A12" in another worksheet named "DropDownList". The listed items correspond with all the headers we have for the physician record list, such as "MD #", "Region #", "Address", etc. The "Cell Link" property refers to range "B1" in "DropDownList". Further assume that we would like to have an ascending order for all the columns except "Level" (we want to show "RxDx" first) when sorting. Here is the macro:

Don't forget to associate the macro with the drop-down list. Download the Excel file here.

Application.Calculation = xlCalculationManual stops the automatic re-calculation of all the cell formulas in the entire workbook, until it is reset back to Automatic Calculation. This helps improve the speed of the macro execution.

On Error Resume Next prevents the macro execution from being stopped by any (potential) errors generated. The macro will just ignore the error and skip the line that causes the error and goes to the next line. Of course any error may render the entire macro unusable, however at least your user will not be alerted by the sudden appearance of an error message.

.Range().Sort is the main method that does the sorting job. Key1 denotes the first "field" - under most circumstances being the column - to be sorted. If necessary, Key2 (and Key3) may be added to perform up to three sorts. "Order1" decides if the sorting for Key1 is A->Z (Ascending) or Z->A (Descending). "Header" tells the macro whether the header row has been included in the Range() object and this is needed if we don't want the header row to be sorted as well (and we don't).

In the next post I will show you how to do a sorting based on customized order.