Here's the scenario: I want to send different pages of my pivot table report to different email recipients.
I can do this by hand but it takes forever to change the filter, copy/paste, or export the report and then create and send the email, even just for a few people.
And when I do this regularly it's even more tedious and time consuming.
So let's use VBA to automate all of this.
The Manual Process
My pivot table summarises sales data over a year for different categories of food and drinks.
I can view the sales data for each of these categories and I want to email that data to the manager in charge of that area. The Beverages manager gets the Beverages data, etc.
For each category, I want to save the report as a PDF and then email that to the manager responsible.
Automating the Process
We need to get a handle on the Category field so that we can change the category
Then by going through each pivot item (category), the pivot table report will change to reflect the sales data for that selected category.
The PDF that is created uses the page/print settings for the file so these need to be set before exporting a PDF.
My pivot table report spans across more than 1 page so I set the page orientation to landscape, and because I want eveything printed to 1 page, I set the entire report to fit into 1 page wide by 1 page tall.
If you need your PDF in portrait then set orientation to xlPortrait. More on the VBA PageSetup object here.
Create a PDF from the pivot table report on the current sheet
Create an email and attach the PDF.
The .To value of the email (who it's being sent to) is being picked up from the Managers sheet using VLOOKUP.
The .CC and .BCC fields of the email are not specified, because they are commented out, but you can uncomment these and copy the emails to whomever you wish.
Here's what it looks like in action
In the video above each email is displayed after it is created. Displaying the emails is only done to make sure they are created correctly.
Once you are happy that the code is working as you want it, change the value of the variable DisplayEmail to False, and emails will be sent without first being displayed to you on screen.