Enabling Macros for an E-mailed Workbook

by Allen Wyatt
(last updated December 11, 2021)

6

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.)
  5. Figure 1. The Protected View options in the Trust Center dialog box.

  6. Clear the Enable Protected View for Files Originating from the Internet check box.
  7. At the left side of the dialog box click Macro Settings. (See Figure 2.)
  8. Figure 2. The Macro Settings in the Trust Center dialog box.

  9. Select the Disable VBA Macros with Notification radio button.
  10. 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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Getting Input from a Text File

You can use a macro to read information from a text file. The steps are easy, and then you can use that information in ...

Discover More

Checking Your Data File

When you get ready to merge a document with a data source, you'll want to make sure that everything is "as expected" ...

Discover More

Limiting Who Can Delete Data

Excel allows you to protect your worksheet data in several different ways. Unfortunately, it doesn't allow you to protect ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Converting to Hyperlinks in a Shared Workbook

When you enter a URL or e-mail address in a worksheet, Excel usually converts it to a clickable hyperlink. This doesn't ...

Discover More

Sending an E-mail when a Due Date is Reached

Wouldn't it be great if Excel could automatically e-mail you when a due date is reached? It can, if you are using Outlook ...

Discover More

Extracting Hyperlink Information

In Excel, a hyperlink consists of two parts: the text displayed for the link and the target of the link. You can use a ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 9?

2021-12-13 10:26:54

J. Woolley

Mea culpa. I should have used Google Search more carefully. See https://support.microsoft.com/en-us/office/add-remove-or-view-a-trusted-publisher-87b3d5a3-b68c-4023-87c4-7cc78a44d7ed#__toc311529723


2021-12-13 10:18:52

J. Woolley

By the way, the sequence described in my previous comment is the ONLY method I have discovered that will add a Trusted Publisher. If someone knows of another procedure, I would be very interested.


2021-12-12 13:45:06

David Gray

Thank you @J. Woolley for adding the illustrated detail. I would usually have done that myself, but I was running out of steam, and I had several other matters that needed my attention before I completely ran out.


2021-12-12 11:51:00

J. Woolley

@David Gray
Re. "When you send the first workbook that contains a signed VBA project, the recipient has the option of installing the digital certificate in the Trusted Publishers store on their machine."
1. The recipient will see a Security Warning (see Figure 1 below)
2. The recipient must click the "Macros have been disabled" link, NOT the "Enable Content" button.
3. The recipient will see a new Security Warning and must click that "Enable Content" button and pick "Advanced Options" (see Figure 2 below)
4. The recipient can then pick the "Trust all documents from this publisher" radio-button (see Figure 3 below)
That will make the publisher a Trusted Publishers on the recipient's computer.

Figure 1. 

Figure 2. 

Figure 3. 


2021-12-12 10:56:44

J. Woolley

@David Gray
You make a good point. For more on this subject, see https://support.microsoft.com/en-us/office/digitally-sign-your-macro-project-956e9cc8-bbf6-4365-8bfa-98505ecd1c01 and https://knowledge.digicert.com/solution/SO7000.html


2021-12-12 01:40:54

David Gray

Another option that I expected to see mentioned is digitally signing the macros. When you send the first workbook that contains a signed VBA project, the recipient has the option of installing the digital certificate in the Trusted Publishers store on their machine. Thereafter, future signed workbooks are recognized as originating from a trusted publisher.

If you are on the signing side of things, take care that you add the timestamp URL to the Windows Registry on your machine so that your signatures are timestamped. A timestamped signature lasts forever, even after the certificate that signed it expires, which it will inevitably do in no more than about 3 years.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.