Enabling Macros for an E-mailed Workbook

When John emails .XLSM (macro enabled) Excel files, some recipients are only able to open the workbook in a Protected View. This causes all sorts of different results/errors. John wonders if there is a way to either prevent this or provide the recipient with instructions to open the workbook without going into Protected View.

Ready for a trip down memory lane? In the early days of the Internet, people could send Excel workbooks as attachments, and recipients could double-click them and open them with no problem, macros or not.

Simple, right? It was, until bad actors started adding malicious macro code to the workbooks. (This also happened with Word documents.) Open the workbook, the macro ran, and your system was compromised. It was a bad situation.

So, Microsoft started clamping down on macros. First, they separated macro-enabled workbooks (.XLSM) from regular workbooks (.XLSX). Second, they added the Trust Center to Office applications, including Excel, that indicates how incoming workbooks should be handled.

For someone (like John) who sends out workbooks, there is not much that can be done. Why? Because if a "good actor" could circumvent the precautions, so could a "bad actor," and things would be right back where they were a couple of decades ago.

The solution is to educate the recipients on how to enable the opening of the workbooks. Here are the steps that someone would need to do:

1. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 or a later version, display the File tab of the ribbon and then click Options.)
2. At the left side of the dialog box click Trust Center.
3. Click Trust Center Settings. Excel displays the Trust Center dialog box.
4. At the left side of the dialog box click Protected View. (See Figure 1.)



Figure 1. The Protected View options in the Trust Center dialog box.

5. Clear the Enable Protected View for Files Originating from the Internet check box.
6. At the left side of the dialog box click Macro Settings. (See Figure 2.)



Figure 2. The Macro Settings in the Trust Center dialog box.

7. Select the Disable VBA Macros with Notification radio button.
8. Click on OK.

This will allow your recipients to open the attached workbooks. The macros won't run automatically, but the user will have the option, upon opening, to enable the macros.

There are two huge caveats here. First, if your recipient is in a corporate environment, taking these steps may really tick off the IT department, as they may have rules about what an employee can or cannot do with macros. If that is the case, your recipient should work with the IT folks to determine how to best use the workbook you are sending.

Second, your recipient will need to make sure that he or she saves your workbook on their computer before trying to open it. I've seen lots of people simply double-click an e-mail attachment in order to open it. In such a case, even if the above steps are followed, there is a good chance the workbook will still open in Protected View. Why? Because in the Protected View options (step 4) there is an option that says "Enable Protected View for files located in potentially unsafe locations." The temporary folders used by e-mail programs to store attachments are one of those "potentially unsafe locations," and therefore the workbook may not open.

Now, there is a time when none of the above will work—when the recipient has filters and blocks in place that prohibit the receipt of e-mail messages that have Excel workbooks (especially macro-enabled workbooks) as an attachment. This may sound like an extreme precaution, but there are many companies that have such restrictions. In that case, your recipient will need to work with their IT department to figure out how to receive and use the workbook you want to send.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12696) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.