Hello all,

I need some help coding some VBA that involves both Microsoft Outlook and Excel.

I have a folder in outlook that contains emails from a few hundred suppliers. These suppliers were all sent the below table to populate:
2022-04-21 16_16_00-Start.png

I then received responses from the supplier who either replied in a new email or populated the table in the original email.

My goal is to extract all of the populated tables from outlook and have them exported into an excel sheet in the exact same format as the table.

Additional requirements include:
1. Where an email contains multiple tables then all tables should be copied into Excel UNLESS the table is empty – a table is empty if only the first row and/or first column (ie the heading row / heading column) are populated … this is to eliminate tables where the user has copied the ‘template’ table (as sent in the email to them) into their reply and populated that (ie and so the ‘template’ table is empty)
2. Where a table has the correct number of columns, it will be assumed that those columns are in the correct order and cell contents will be copy/pasted in that order … where a table has an incorrect (ie more or less) columns then the heading text for each column will be used to copy/paste the right values into the right Excel cells … columns that have been ‘appended’ (ie we say an example where the table had extra cells appended to the right for some rows) will be ignored
a. Note that if a table has been returned with the heading row removed (and so the heading text is not present) then it will be assumed that those columns are in the correct order
3. Where a table has a heading row as its first row then that row will not be copy/pasted into Excel …
b. Check the text in the first row (either one, several or all cells of that row?) … if it matches EXACTLY to the expected heading row text then ignore that row
4. Where a row has 1 department but multiple people listed then in Excel that should be broken out into one row for each person all with the same ‘department’
a. Note that this will be done by detecting ‘carriage returns’ within the text of cells eg the user has entered a name, pressed ‘enter’ (to create a ‘carriage return’) then typed some further text such as a second name … as such, the alignment will be based on presence of carriage returns … the VBA code will not ‘understand’ what is a name, phone number etc
5. Where a row in a table is empty (ie other than the first ‘heading’ cell in the row) then do not copy that row


Please let me know if any further information is required and I hope you'll be able to help me