0
votes

I would like to know if there's a way to extract this email with the Email subject: Mani Inc. / ACF-SAFE / ABC123-563 / Submission document / MFR ABC1235 Follow-up #1 / Due Date: 10-Mar-2020 with the Email Body: Email body into this Excel template below? Fields to enter in Excel The 1st row is for Email subject: Mani Inc. / ACF-SAFE / ABC123-563 / Submission document / MFR ABC1235 Follow-up #1 / Due Date: 10-Mar-2020 while the 2nd row information is for Email subject: Mani Inc. / ACF-SAFE / ABC123-563 / Submission document / MFR ABC1235 Initial / Due Date: 10-Mar-2020

Currently the only way I can think of is to use VBA to extract the Email Subject and Email body into Excel and then use formulas to extract the specific information to the Excel template? Is there a simpler way?

Thanks for any help in advance!

2

2 Answers

0
votes

VBA can catch the email data in Outlook. Parse data in it and place it in the template.

VBA in Excel can also forward the template by email to next point in the process chain.

Your last question is impossible to answer concisely.

0
votes

You have two separate problems: (1) how to extract the data you want from the email and (2) how to store that data in an Excel worksheet in the format you require. There is little point worrying about problem 2 until you have solved problem 1.

An email can have three bodies: a text body, and an Html body and an RTF body. I have never seen a Rich Text Format body, so it is unlikely you have one of them.

If the sender only includes a text body, that is what you will see. Your image shows a table that you can only have with an Html body. If the sender includes both a text and an Html body, you see the Html body, but the text body is available to a VBA macro. In 99.9% of the emails that I have examined, the sender has only included an Html body. In such cases Outlook creates a text body by deleting all the Html formatting and replacing the major end tags with one or two CRLFs.

So, you have two choices: (1) extract the data you want from the Html body or (2) extract the data from the text body.

The advantage of trying to extract from the Html body is that it will include something like:

<p>Dear Colleagues</p>
<p>Kindly find attach a document for review</p>
<table xxxxxxxx>
  <tr xxxxxx>
    <td xxxxxxx>MFR#</td> 
    <td xxxxxxx>Report Date</td> 
    <td xxxxxxx>Due Date</td> 
    <td xxxxxxx>Protocol#</td> 
    <td xxxxxxx>Comment</td> 
  </tr>
  <tr xxxxxx>
    <td xxxxxxx>ABC1235</td> 
    <td xxxxxxx>01-Mar2020</td> 
    <td xxxxxxx>10-Mar-2020</td> 
    <td xxxxxxx>ABC123-45</td> 
    <td xxxxxxx>&nbsp;</td> 
  </tr>
</table>
<table xxxxxxxx>
  <tr xxxxxx>
    <td xxxxxxx>Time</td> 
    <td xxxxxxx>SCDAD</td> 
    <td xxxxxxx>SAFETY\180 Submission</td> 
  </tr>
</table>

That is all the data you require is delimited by <td> and </td>. The first disadvantage is all the xs which represent formatting instructions. The second, possible disadvantage is the sender may include CSS (Cascading style sheets) so the table will display one way on a PC and another way on a smartphone. In this case, the Html could be very much more complicated than I have shown.

The text body will probably look like this:

Dear ColleaguesCRLFCRLF
Kindly find attach a document for reviewCRLFCRLF
MFR#CRLFCRLF
Report DateCRLFCRLF 
Due DateCRLFCRLF
Protocol#CRLFCRLF
CommentCRLFCRLF
ABC1235CRLFCRLF
01-Mar2020CRLFCRLF 
10-Mar-2020CRLFCRLF
ABC123-45CRLFCRLF
 CRLFCRLF
TimeCRLFCRLF 
SCDADCRLFCRLF 
SAFETY\180 SubmissionCRLFCRLF

With the text body, all the formatting has been removed for you but there is nothing to indicate where the tables, rows and cells start and top.

Only by looking at the Html and text bodies can you decide which will be easier to process.

Please look at this answer of mine: https://stackoverflow.com/a/58000707/973283.

This answer includes an Outlook macro that outputs selected properties of selected emails to the Immediate Window or outputs many properties of selected emails to a desktop file. You need the second option since this includes both the text and the Html bodies. You can review the two bodies and decide which looks easier to process. Alternatively, you can include the relevant parts of the bodies in your question and someone may recommend an approach.