Refresh all pivot tables when emailed spreadsheet is opened

Hello

I have an automated process that populates a spreadsheet and emails it. The spreadsheet contains pivot tables and the data for those tables is in the spreadsheet and gets updated by my process before the email. My process cannot update the pivot tables before sending the spreadsheet so I added a button that the recipients can click to refresh them. What I would like to do is remove this user step and just make the spreadsheet update automatically on opening. So I added this macro to do that.

Please Login or Register  to view this content.

This works for my copy when I open it from my folder, however when the spreadsheet is emailed and I open it, it is in protected view and when I enable editing the macro fails with an error "Object variable or with block variable not set", which I assume is because the macro ran when the spreadsheet was in protected mode.

Does anyone know a way to auto-refresh pivot tables when the user opens their emailed copy?

I'm on the 365 version of excel using windows 10.

Thanks.

Administrative Note:

Welcome to the forum.

You need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. As you are new here, I have done it for you this time.)