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.
Download the Code
This blog post describes the code used to create and send PDF documents, but I've also written a version that sends the attachment as an XLSX workbook. Download both versions here.
Enter your email address below to download the sample workbooks containing the code in this post.
Send email attachment as XLSX workbook.
Note: These are .xlsm files, please ensure your browser doesn't change the file extension on download.
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.
Hi.
Thanks for that code.
Works wonders.
but my problem is the blank values in Filter which result a blank email.
any code to not even make a blank value mail?
Hi Marko,
Why have you got blanks in the filter? Can you remove them?
Otherwise you can use an IF statement to check for (blank) in the filter and do nothing if it’s there.
You need to add 2 lines of code:
After pf.CurrentPage = pf.PivotItems(i).name
insert If pf.CurrentPage <> “(blank)” Then
and Before Next i
insert End If
Regards
Phil
Hi Phil!
Well i solved the thing with (blank) by making the Pivot brand new 😀
Now i Have a different issue:
one email macro is working (Daily)
the other one (weekly) not
Debuger stops at
pf.CurrentPage = pf.PivotItems(i).Name
How comes that on one sheet it works on the other one not?
Hi Marko,
I can’t debug it without having the file with the code. Please start a topic on our forum and attach your file.
regards
Phil
Hi,
When I use the macro to send the email attachment as as .xls, all Managers will be able to filter the excel file and to see sales data for other Categories.
How do I adjust the Macro so that Managers can only see sales for their own categories?
Thanks,
Sonia
Hi Sonia,
This is the reason why the report is sent as pdf.
If you want to send an excel file, you will have to split data into separate files.
Try this solution, you can add your code to send separated data into excel files to your recipients.
Phil,
How can I change From add ?
Hi Ed,
Not sure what you mean. Are you asking how to you change the From email? That is, send from a specific email address?
Regards
Phil
Hello, I am having some difficulty with this code. I am copying it so I can do the same thing to my pivot table but when I run the code, I get
Run-timer error ‘1004’
Application-defined or object-defined error.
The debugger highlights this line:
Set pt = Sheets(“Main Pivot”).PivotTables(“pivot_table1”)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Hi Richard,
Without seeing your code it’s hard to say what the issue is. But I’d guess either you don’t have a worksheet called Main Pivot, or you don’t have a pivot table called pivot_table1 on that sheet.
If you start a topic on our forum and attach your file we can take a look at your code.
Regards
Phil
Hello,
I was able to fix it, the reason why it was not working was because my pivot table was a power pivot.
Question: I would I go about creating an excel attachment of the current pivot table report instead of a PDF?
Hi Richard,
You can save the active sheet (the one with your PT report on it) as a new file and then attach that file to your email. Here’s the code, you need to insert the .Attachments.Add line into the section of code beginning With OutlookMail
Dim NewFile As StringNewFile = "d:\temp\NewWB.xlsx"
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs Filename:=NewFile, FileFormat:=xlOpenXMLWorkbook
.Close SaveChanges:=False
End With
.Attachments.Add NewFile
Regards
Phil
Perfect it works thank you so much. Is there anyway I can prevent an email from populating if the filtering of the pivot table results in an empty table?
Hi Richard,
You can check the number of rows in the PT with this
pt.DataBodyRange.Rows.Count
If I have a row in my Raw Data that has a category but nothing else I have 2rowsin the PT when this ’empty’ category is selected as the filter.
Alternatively you can check the first data field in the PT for the text (blank)
Regards
Phil
Hi Phil,
Please could you clarify where in the original script I should input the code to send as an attachment (instead of PDF)
Hi David,
I’ve created a new file that sends the attachment as an XLSX workbook. Check the section towards the top of the post where you can download the code.
regards
Phil
Hi Phil,
I’ve used this a lot and it’s really useful – many thanks. Some of my users have Macs and I wondered if the VBA can be modified to accommodate that platform – specifically with Outlook as part of Office 365.
Thanks,
Pieter
Hi Pieter,
Sorry I have no access to a Mac in order to be able to test this.
Regards
Phil
Thanks for this. It really helped. However, could you please suggest how to add the pivot table to the body of email rather than attaching as pdf? I would really appreciate the changes.
Hi Alice,
See this topic, there is a RangeToHTML function that you can use.
Hey there,
Thank you for all that you do. I’ve learned more in the last week than in the past few years (regarding Excel at least ;))
How would I insert some standard email body text while customising the Salutation using the recipient first name? (eg. using an additional column in your ‘Managers’ sheet containing the recipient First Name?) This would be if I’m not viewing the email before sending it.
Cheers, Al.
In the above, I do have multiple contacts per company which could be contributing to the below.
…also…
I’ve been tinkering with the code and there seems to be an issue with the lines,
Set pt = Sheets(“Pivot – Ticket Time”).PivotTables(“ConsolidTicketInfo”)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
pt.PivotCache.Refresh
Set pf = pt.PivotFields(“Company Name”)
when running it against my workbook. Getting Run-Time Error 1004 in the MissingItemsLimit and the PivotFields lines.
Hi Al,
I’ve replied to your question on the forum with code that avoids this issue.
Regards
Phil
Thanks Al, glad we’re able to help.
To insert a body to the email you’ll need to insert some new lines of code. After the line
add this (as an example). Make sure it goes into the VBA editor as 4 lines. The first line is quite long and wraps around in these comments.
.Body = "Dear " & WorksheetFunction.VLookup(Range("B1").Value, Worksheets("Managers").Range("Managers"), 3) & vbCrLf & vbCrLf .Body = .Body & "Here is your report" & vbCrLf & vbCrLf .Body = .Body & "Regards" & vbCrLf & vbCrLf .Body = .Body & "The Boss" & vbCrLf & vbCrLfthis will result in the email body looking like
If you add a 3rd column to the table on the Managers sheet containing first names, the VLOOKUP in the fist line picks out the recipient’s name from that 3rd column.
Regards
Phil
Hi There
I did download the file excel and ths for sharing the excel file.
But I m having problem in step “Create an email and attach the PDF”. The fomular of Vlookup does not perform as usual. When I press Run the VBA , the fomular start to pick random email from database, not matching value. I did change fomular a lit bit and format in number order but problem is not solved. Could you help me out of this, I am appreciate your help
Below is fomular I am having trouble with.
.Display
.To = WorksheetFunction.VLookup(Range(“B1”).Value, Worksheets(“Managers”).Range(“Managers!A2:B15”), 2, True)
Hi,
Without seeing your workbook it’s impossible to debug this problem.
Please start a topic on our forum and attach your workbook.
Regards
Phil
Thanks for this amazing code. Works perfectly.
Would it be difficult to paste the screenshot of each pivot into a MSWord template (bookmark) and save that word as pdf . This should the be the attachment.
Thanks again
Hi Olaf,
You want to do this with VBA? Does the screenshot have to go into a Word doc? It could be saved direct to PDF from Excel.
Please post a topic on the forum and supply your Word template.
Regards
Phil
Thank you so much for this code, it is going to save e a lot of time! I am having an issue though that if I delete one of the underlying categories, in this case all the condiments data, the email function stops and I get an “error invalid procedure call or argument” with this code highlighted when I hit debug pf.CurrentPage = pf.PivotItems(i).name
I’ve had this same error using the code for other pivot tables that if the underlying data changes and the contents of the filter change, the program halts when it gets to the record that is now gone. How do I get it to recognize the new or changed filter list?
Thanks, Marc
You’re welcome Marc.
When you deleted source data the PT was retaining deleted data in the PivotCache even after a refresh, sorry!
I’ve added some code to prevent this happening so deleted data is not retained.
You can download the new file from the same file download link in the post.
Regards
Phil
Thank you Philip for this wonderful Macro!
I have an issue related to this.
When the underlying data set changes, and a new category is added e.g. Lobsters, the code is not generating a pdf for the new category.
Do you have any idea how to work around this?
Thank you!
Ah no worries I figured it out! Please disregard my query. And thanks again for this wonderful tool!
Hello and thank you to both Philip and Mynda for ALL you do! I have adapted and used the heck out of this code in several different scenarios, but the current roadblock I’m facing is to create and save (not email) only a single pdf that has a page for every item in my Slicer/Filter. I’ve already adapted to create and save all 141 individual files, which is awesome, but I need to have a clerk print hi-res/presentation quality handouts of each file and I’m trying to eliminate some thinking on her part. Do I just need to change where the loop occurs to do this? I hope my question is clear, I’m saying “create the 1st page based on the first item in the slicer, insert a blank page and populate it with the report from the 2nd item in the slicer, “rinse and repeat”, and then save the compiled file. Per Mynda’s best practice, I put every PT on a separate worksheet, but they are hidden and only the report page (where the visuals and slicer resides) is visible. I’m not the best VBA person so my question may be amateurish, but I could really benefit from any help you can provide.Thanks in advance and keep washing your hands!
Hi Kathi,
You said you already have report pages on multiple sheets?
Why not unhide them, print to pdf the desired sheets, then hide back?
Sheets(Array(“Sheet1″,”Sheet2”)).ExportAsFixedFormat will print all these sheets as a single PDF.
It is also possible to print to pdf one sheet at a time on a temp folder, then use other tools to combine them to one combined pdf, but selecting all sheets is the easiest way.
If you need more help, please upload a sample file on our forum, will be easier to help you.
Hi
What a tremendous article.
I’m quite new to VBA and have a pivot table without filters but I can’t seem to get this to work.
It is the pf declaration that is throwing me and then of course the loop is not required.
Also, I would like the pivot table, with formatting, to be in the body of the email and not an attachment. Can you help steer me in the right direction.
I love reading your articles.
Regards, Tanja
Thanks Tanja.
I’ll need to see your workbook in order to help, can you please start a topic on the forum and attach your workbook to it.
Regards
Phil
Hi Phil,
the last black box as simple as it is, helped me to reduce the number of clicking I have to do while sending out regular mails to various departments. I use this with active mail (objApp.ActiveInspector.CurrentItem) which makes it very flexible as I can still prepare individual mails. Thanks a lot!
No worries 🙂
I’m getting an error trying to use this code and I’m wondering if it’s because I am using the Data Model for my Pivot Table source. The error is at this line:
Set pf = Sheets(“Pivot Tables”).PivotTables(“Vacancies”).PivotFields(“Location”)
Any thoughts?
Hi Kathi,
Yes, the code is for regular pivot tables, not power pivot tables. For PP tables, you can see that the option from Pivot table toolbar-Options-Show Report Filter Pages is greyed out.
You can still record a macro when applying manual filters on that category filter. Apply a filter for 2 separate items, stop the recorder, inspect the differences to identify the variable, this will provide the basic code syntax for that power pivot table.
Thank you Catalin, I ended up loading an abbreviated PQ to a Table and got everything working.
can you please elaborate it little with example as i am a beginner and i want to use it with power pivot.
Thanks & Regards
Hi Khurram,
This article applies only to normal pivot tables, not to power pivot tables. If there will be an alternative article for power pivots, please subscribe to our newsletter, will let you know.
Regards
Catalin
Thank you very much, I’ve learned a lot from you.
Thanks, glad to help
This is a great code. I am trying to tailor it to my situation and it looks like there would be a bit of error handling and filtering needed for it to work correctly. I have multiple years pulled together using a power query and use the power query to filter my list of results by one year.
When I run this, it begins by grabbing everyone in the list, regardless of whether they have statistics in the year I want or not and regardless if they are on my manager list or not.Then an email is generated for everyone – even those who no longer work here – and opens an email for them with a blank address.
I think it would be beneficial for a PDF file and an email NOT be generated if the email does not exist in the manager table with an error message popping up stating such. So maybe the PDF would be generated only if the email exists in the managers table.
Hi Norma,
You’ll need to supply your workbook for us to be able to help you.
Please start a topic on the forum and attach your workbook.
Regards
Phil