Extracting certain text from Outlook email to an excel spreadsheet

[bclor3591]

Hello,

I'm trying to extract certain text within Outlook 2003 emails an put the information into an Excel 2003 Spreadsheet with the column headings of:

DATE/TIME SENDER RECEIVER APP SENDER REF# ERROR MSG

--------------------------------------------------------------------------------------------------------

This is what EVERY EMAIL looks like:

ERROR DATE/TIME 22-Aug-2013 13:35:00

SENDER 012345678 (this can be different)
RECEIVER 987654321A (this can be different)
APP 810 (this can be different)
SOURCE (this field not needed on spreadsheet)
ERROR (303) Missing Trading Partner Profile for...(can be up to 3 lines of text)


xxx SESSION ID 00000123
xxx REF# F-12345678


Does anyone have any ideas how this can be accomplished with either a Macro or VBA?

Any help would be greatly appreciated.
Bob

 

[Trevor G]

The following code will extract the body email within your inbox, you will need to set the reference to use Outlook in Excel VBA screen. Use Alt + F11 in Excel then Select Tools and References then search down to Outlook Object Library and click the box. Copy the code into a new module through the Insert Menu and Module. Give it a go to test getting the data into a workbook and we can look to build the next step

Sub OutlookEMails1()
Dim ns As Namespace
Dim Inbox As MAPIFolder
Dim myitem As Outlook.MailItem
Dim FileName As String
Dim i As Integer
Dim objSearchFolder As Outlook.MAPIFolder
Dim item As Object
Dim mai As MailItem

Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set objSearchFolder = Inbox
i = 0
If Inbox.Items.Count = 0 Then
MsgBox "Inbox is Empty", vbInformation, "Nothing Found"
End If
For Each item In Inbox.Items
vbody = item.Body
Range("A2").Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1).Select
Loop
ActiveCell.Value = vbody
Next
Set objSearchFolder = Nothing
Set Inbox = Nothing
Set ns = Nothing


End Sub

 

[bclor3591]

Hello Trevor,

That Macro worked like a charm. No for the next leg, how do I extract the COLUMN INFO I mentioned in my previous post to the various columns? I assume I would probably use =find in the excel spreadsheet, but not sure if that way is the easiest and fastest?

Here is what some of the data looks like extracted:

ERROR DATE/TIME: 09-SEP-2013 09:36:29
SENDER 133024604
RECEIVER 1065144JS
APRF 997
SNRF 997000432
SOURCE AI
ERROR (303) Missing Trading Partner Profile For
Missing Trading Partner Profile '(GS)' 'SVG [01-133024604] / Our Co. [16-10651444JS]_ISA' Containing --> Trading Partner ID- 133024604 Your ID- 10651444JS
GXS SESSION NUMBER 000326292
GXS REFERENCE NUMBER F-10492981

 

[Trevor G]

From the above does each line go into separate cells in each row, it maybe useful to see some actual data. Would you be able to create a small sample in a workbook, say the extract on sheet one as it is now extracted from your emails and perhaps on sheet 2 what you want to see and how you want it laid out. Then upload via a 3rd party and place a link into your thread and I can take a look.