Capture Outlook body fields and copy to excel

Hi Forum Members,
Good day to you all.
I have a folder in outlook where all the email messages from the other team will fall in to that folder. i want the VBA code that capture all the messages one by one in excel sheet.
The first slide is sample email message with the fields with column order number.
The second slide, the capture results should come same as like the table to the excel sheet. I have attached sample sheets for your reference.
I hope my question is clear to understand.
Thanks in advance.

You can start with loop through the emails - search for how-to-parse-outlook-emails-and-show-in-excel-worksheet-using-vba

To access a specific folder

Please Login or Register  to view this content.

I would load the message body as HTML into an HTML document object and then find your data by parsing it.
search for parse-html-in-excel-vba

If you have any issues, post your code and what/where the problem is and I'm sure someone can help you out.

Dear Gurus,
I Got the basic code, but it read/capture basic elements in the email not the Body fields. The code below, can you tweak the code for my actual question. Thanks

Please Login or Register  to view this content.

Save one of the emails you'd like parsed to a file and upload it, so I can see the actual email body.

That is a powerpoint file, I'd like to see an actual email to see which html elements are used in it.

Can you please check the zip file, i have attached the email message

Hmm this one is blank for some reason

My apologies. My organization do allow me to send any mail attachments outside the organization.
Can you try this attachment if not opening, please tell me find the outlook elements

Hi Jdelano, I hope you are working on my request, please let me know if you have any queries. Thanks in advance

So here is some code, you can delete some of the commented lines to clean it up,
I just wanted to show that as you go through the process of creating some code, it is good to be aware that you don't repeat the same code over and over.

I stopped it and created a new function to do the repeated code for me and then just call that as many times as needed. I hope that makes sense.

Please Login or Register  to view this content.

To make the code cleaner, you can make a separate sub procedure to write to the sheet, just make the variables that hold the extracted data module scoped.
Just move the DIM statements for them to the very top of the sheet1 module so that both the ParseHTML procedure and a new WriteToSheet procedure can access them

Hi Jdelano,
sorry for delay in my response. I am not welll, now much better.

Can you please share me the sample file with code with button, where i can execute the code? coz i cannot run the code with private sub procedure.

Here you are

For some reason i cannot run the 'ParseHtml' Private procedure code, what should i do? can you help me.
I am getting- 'user defined type not defined' error

Originally Posted by farhanahamed

For some reason i cannot run the 'ParseHtml' Private procedure code, what should i do? can you help me.
I am getting- 'user defined type not defined' error

Make sure that you have a reference to Microsoft Outlook (Tools > References > Microsoft Outlook ## Object Library) maybe you don't have the same version.

When you say it isn't working, what exactly isn't working? Is FedEx ID is not parsed out?

Please Login or Register  to view this content.

, you need to use a lowercase p, when using MSHTML you must use the same case at the actual HTML element. You will notice in my examples they are all lowercase.

"The getElementsByTagName method in MSHTML (Microsoft HTML Object Library) is case-sensitive when searching for HTML elements."

jdelano,
even i have changed to 'p' in the code, the Fedx Id is not parsed out. I tried to parse only fedx id by adding code for Fedx id along with my old code.
can you provide me the straight forward macro, please?

Wha is the value of HeaderHTMLTable?

As per the above screen shot, the value is 'Lunux-27760-CISCO'

It would help to see the actual HTML that the variable HeaderHTMLTable contains to see why selecting the paragraph child element fails.

I do not understand, So you can not able to solve the problem or you cannot give the exact code what I am looking for?

You don't understand the question of posting the value of a variable?
Also, I am helping out of kindness, please understand that.

I Know that, you guys helping us. Saviors.
What is wrong with the code? Can you correct the code, can you please include the basic code with Body fields and Table. Thanks in advance

Hi Jdelano, Why no reply, can you support for this VBA request, please?