Extract Table from Outlook Email and copy it into existing excel doc

[cms457]

Hey all,

I've been scouring the internet for some kind of answer to this problem with no success, I'm new to VBA so more specific/complex solutions have been difficult for me to adapt(started teaching myself VBA and complex excel functions 8 months ago).

My issue is:

I do Sales Operations for my company, and I've built numerous personalized spreadsheets for my sales reps to save on their desktops. These spreadsheets contain functions that will pull their sales data from a 'Master Spreadsheet' saved onto our company network drive and calculate a summary of their sales data.

Every day, to update the data, I have to run a report in our Database and export the records to an excel doc. I then copy that exported data table and paste it over the existing data in my 'Master Spreadsheet' and save. Then, of course, all of the sales data in my sales reps personalized spreadsheets is updated from the new data 'Master Spreadsheet' automatically.

So I can receive emails with reports built by our IT but they're in the email body, not in excel format, and they can't autosave the reports as an existing excel doc to our company drive.

Short of it:

I've been trying to create a Macro that I can embed into an Outlook Rule that will run whenever an automated report from IT with a specific subject-lets say "test macro code"-shows up in my inbox. The macro I'm trying to create will clear the existing data in the existing "Master Spreadsheet" document, in columns A through K(all rows) and then copy the table in the email body(variable in size-we're talking anywhere from 500 to 25,000 records), and paste the new data into cell A1. Then save and close the "Master Spreadsheet".

I've been skating by using 'Frankenstein' macros I've patched together but this one has escaped me. Any help is welcome.

 

[John_w]

There is Excel VBA code at https://www.mrexcel.com/forum/excel...ted-folder-excel-post4727139.html#post4727139 which imports data from Outlook tables into Excel, which you might be able to adapt.

One change, since you want the macro to run from Outlook would be to change the

Set oApp = GetObject(, "OUTLOOK.APPLICATION")

and related lines to

Dim ExcelApp As Excel.Application  'early binding, so requires reference to Microsoft Office Excel nn.0
Set ExcelApp = GetObject(, "Excel.Application")

etc., and then:

Dim ExcelWb As Excel.Workbook  
Set ExcelWb = ExcelApp.Workbooks.Open("C:\Path\To\Master Spreadsheet.xlsx")

to open the destination workbook so it can be written to.