Excel VBA - email select Pivot Table data RRS feed

  • Question

  • I've been tasked with converting an existing spreadsheet set up to email pivot table data via Lotus Notes, to make it work with Outlook, which we recently migrated to (Office 365).

    There is a workbook with eighteen worksheets.  The last one contains email addresses; each of the rest have a pivot table with gathered data from each of the seventeen workstations.  The first workbook has a button, when the user clicks it, they are asked for the date and shift number...these are the two rows in the pivot tables.  This then gathers two columns that I am interested in: Actual and Labor.  This all results in a table that is put into an email, with this data: user input-date (chartDate); machine name (ws.Name, as the name of each worksheet is the machine name I need), user input-shift (Shift), column Actual, column Goal & column Labor.

    I'm looking for something like this:

    9/21/2015 - Shift 1
    Machine | Actual | Goal | Labor
    Machine1 | 12000 | 12500 | 40
    Machine2 | 11000 | 10000 | 36 

    And so on, pasted into an email and sent to the recipients listed in the email worksheet.  Getting the recipients is the easy part. I'm just not quite sure where to go towards manipulating the pivot tables and putting them into the email.  The existing code has lots of Lotus Notes-specific formatting code, and lots of commented out stuff, so I'd prefer to start clean.  Anyone have any thoughts as to how I can do this?

    - Fenfool

    Monday, September 21, 2015 5:31 PM