View Full Version : Consolidate Data from Email

2018-04-14, 07:44 PM
I have emails coming to my inbox on a daily basis. Starts with notification wording (approximately one or two paragraphs). Then goes table in a text format. Then goes another paragraph or two.

I connected through Microsoft Exchange to this folder. When consolidate, I need to remove all the data and hopefully can consolidate all text tables together (need monthly data) and then split them (text to columns or column split). Tried using Power Query, not working well. Cannot do it in consistent steps.

Can anyone advise what could be other options? Thanks!

The email looks like below:

From: Bank
Sent: Thursday, April 12, 2017 8:52 AM
Subject: Payment Notification

Payment Receipt Advice

Report Date: 12-Apr-17

Originator Receiver
ABC Company XYZ Companny

Email address:

Direct Responses to:

Payor: ABC Companny
Payment Date: 11-Apr-17
Payment Amt: $100

Code Reference Date Total Amt/ Amount Paid/ Discount/

Credit Note Number 91A 09-Apr-17 -100 -100 0.0
Invoice Number 38C 28-Feb-17 200 200 0.00

Totals: 100 100 0.00
The purpose of this communication is to notify the Receiver of an intended payment by
Originator. Failure to receive the Payment Amount on the Payment Date should be communicated by
Receiver to the Originator, who is responsible for the payment of such amount.