asked on

I Need an Excel Macro to Extract Data from the Body of Outlook Email items

I receive data in the Body of an email. I am looking for an Excel macro that looks in a designated Outlook folder, loops through the emails. extracts the data, and puts it in Excel.

The email body is structured , so it is the same every time. The content of the "fields" varies, and that is the data i want to extract.

Below is a sample email. Anything to the right of a colon is data

Fun Plant Hold ID 3366 (THIS IS the HEADER)
__________________________

Hold ID :3366
Date :06-AUG-18 06:19:38.0
Product :501179 - DS1173-11N-4MM
Machine :CRLINE07
Hold Tag Creator :And.factd.lab

Hold Category :QUALITY
Defect 1 :Wet Glass
Disposition :

Required Tests:

Number of Pallets on Hold: 2

PALLET ID LABEL CREATION TIME NET WEIGHT
---------------------------------------------------------------
125640400170 05-AUG-18 03:38:49.0 1091
125640400176 05-AUG-18 06:15:09.0 1091

Rob Brockett

Hi Rex,
Let’s take a step back before getting into any coding. Would it be possible to get the email/source application changed so that it sends a file rather than text within the email body?

Rob

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Steve Knight

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

@Steve
On the basis of past experience I can say that we cannot rely on rtrim.

Rex

ASKER

sorry for the delay.... i had to go out of town. I am working on it today.

Rex

ASKER

I cannot get the code above to run... I keep getting error upon trying to execute... These are screen shots of the error and what it shows after i click on the OK button
Error-Screen-shot-1.jpg
Error-Screen-shot-2.jpg

Subodh Tiwari (Neeraj)

You need to add reference to the Microsoft Outlook Object Library as I mentioned earlier.

Rex

ASKER

Thank you Neeraj.... That helped, but now it locks up at a variable not defined error. I apologize for my ignorance. I am new to coding.
Screen-shot-1.jpg
Screen-Shot-2.jpg

Subodh Tiwari (Neeraj)

That's because you commented the following line in the code. Why?

'Dim olApp As Outlook.Application

Open in new window

Just uncomment the above line by removing the single quote from the beginning of that line like below...

Dim olApp As Outlook.Application

Open in new window

Rex

ASKER

Thank you Neeraj. That fixed that.... now i have a data type mismatch error

I tried to set the folder to the one i want it to pull from... "0 0 Holds for Quality"

....but it's giving me a data type mismatch error?

I don't understand. I'm sorry.
screen-shot-3.jpg
screen-shot-4.jpg

Rex

ASKER

I changed to code to fix the data type mismatch error.... bu now i get an object not found error

Subodh Tiwari (Neeraj)

You will get that error if you are trying to set the folder to a non default Outlook folder. Is it the case?
If you have created that folder outside the default Outlook folders, try it like this...
Set oFolder = NS.Parent.Folders("0 0 Holds for Quality")

Make sure that the folder's name in bold is correct.

Subodh Tiwari (Neeraj)

Resolved the original question.