My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / Automating and Emailing Pivot Table Reports
automating and emailing pivot table reports
by

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.

By submitting your email address you agree that we can email you our Excel newsletter.

The Manual Process

My pivot table summarises sales data over a year for different categories of food and drinks.

pivot table sample

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

Set pivot field vba

Then by going through each pivot item (category), the pivot table report will change to reflect the sales data for that selected category.

Select category filter in pivot table with vba

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.

Page setup for printing

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 PDF with VBA

Create an email and attach the PDF.

Create and send email with vba

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.

automating and emailing pivot table reports

More Excel VBA Posts

Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA
Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image
List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists
Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar
Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA
Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba
Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject
Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List
Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points
Calculate end of period dates in Excel
Calculating Excel End of Period Dates can be tricky if you don't use standard month ends. Here is a formula and UDF that solves the problem.

More Pivot Tables Posts

fixing incorrect totals in dax
If you're getting incorrect totals in your DAX measures, this post explains how to fix them. VIDEO and Sample file to download.
excel pivot table group data
How to group data like dates into years, quarters or months, and ages into groups. Video instructions and sample workbook to download.
Excel PivotTables Unique Count 3 Ways
Excel PivotTables Unique Count, or distinct count as it's also know, is easy, but there are different approaches depending on your version of Excel.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Tag: ,

Reader Interactions

Comments

  1. Marko

    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?

    Reply

    • Philip Treacy

      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

      Reply

      • Marko

        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

        Reply

          • Philip Treacy

            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

  2. Sonia Alwani

    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

    Reply

    • Catalin Bombea

      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.

      Reply

    • Philip Treacy

      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

      Reply

  4. Richard Garcia

    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

    Reply

    • Philip Treacy

      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

      Reply

      • Richard Garcia

        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?

        Reply

        • Philip Treacy

          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 String

          NewFile = "d:\temp\NewWB.xlsx"

          ActiveSheet.Copy

          With ActiveWorkbook

          .SaveAs Filename:=NewFile, FileFormat:=xlOpenXMLWorkbook
          .Close SaveChanges:=False

          End With

          .Attachments.Add NewFile

          Regards

          Phil

          Reply

          • Richard Garcia

            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?

          • Philip Treacy

            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

          • David Evans

            Hi Phil,

            Please could you clarify where in the original script I should input the code to send as an attachment (instead of PDF)

          • Philip Treacy

            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

  5. Pieter Cox

    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

    Reply

  6. Alice

    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.

    Reply

  7. Al Newman

    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.

    Reply

    • Al Newman

      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.

      Reply

    • Philip Treacy

      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 

      With OutlookMail

      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 & vbCrLf

      this will result in the email body looking like

         Dear  Alice 

   Here is your report 

   Regards 

   The Boss

      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

      Reply

  8. huy dang

    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)

    Reply

    • Philip Treacy

      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

      Reply

  9. Olaf

    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

    Reply

    • Philip Treacy

      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

      Reply

  10. Marc Durant

    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

    Reply

    • Philip Treacy

      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

      Reply

      • Sonia

        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!

        Reply

  11. Kathi Mills

    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!

    Reply

    • Catalin Bombea

      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.

      Reply

  12. Tanja Skocic

    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

    Reply

    • Philip Treacy

      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

      Reply

  13. Matthias

    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!

    Reply

  14. Kathi Mills

    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?

    Reply

    • Catalin Bombea

      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.

      Reply

      • Khurram Hanif

        can you please elaborate it little with example as i am a beginner and i want to use it with power pivot.

        Thanks & Regards

        Reply

        • Catalin Bombea

          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

          Reply

  16. Norma

    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.

    Reply

    • Philip Treacy

      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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *