Can Access scrub data from Outlook email (1 Viewer)

[atol]

Hey guys,
Here is the issue. I am receiving emails (work related) in the same format (where the information is in the body of the Outlook email). Basically the emails contain some financial data that the team needs to manually enter in Access database.
The great question is - is there a way to somehow parse the emails and get the data from the email directly into an Access table?
The emails are coming into a group emailbox in Outlook.
Any assistance is greatly appreciated.

Regards
Atol

 

[pbaldy]

Sure, start here:

http://www.thatlldoit.com/Pages/howtosarticles.aspx

Specifically the email attachment section. You'd want to access the body rather than n attachment. You can also move the email once handled.

 

[atol]

Thank you for your advice Paul. Once saved, I guess the challenge I would foresee would be related how to extract the data from the email...I am not sure if Outlook can save in xml, or I can import to Access as xml, and the upload to a table from that potential xml file? Here is an example:
Email body:

Loan #: 11111111

Outstatding balance: 222,333.11 ...... Interest Rate: 5.22%
Next Reset Date: 1/1/2011....

and on and on... The emails have the same format but different senders.

So, if I save the email, I was hoping I could somehow export those numbers (loan amount, interest rate, and next reset data) into a table in Access.
If someone can just give me a hint how to approach this, or where to read, I will make sure i can graps this through and work on it.
Again, I didn't really find any info on Internet; or parhaps i didnt use the right key words.
I will appreciate any thoughts or ideas.
Rgds
Atol

 

[ChrisO]

The above information is incorrect:-

Outstatding is misspelt.
Loan amount does not exist.
The date would be ambiguous.
We do not know if the values are at the end of a line.
I doubt if ...... or .... are in the email.

The actual return data from the email body is required.

Chris.

 

[pbaldy]

I use an adaptation of that technique to pull data out of the body of emails (where each line contains a different piece of data). I use the Split() function to get each line of the body into an array. Then I loop the array, use InStr() to see if there's a colon in the line, and the Mid() function to peel out what's before/after the colon. Then with a Select/Case block I test what's before the colon and add to strings to build an SQL statement to append the data. It isn't beginner-level stuff, but it's certainly doable.

 

[ChrisO]

Hi Paul.

Yes but it can depend on how the data is represented in the string of the body and or if some of the lines are missing or if there are other colons in the body.

If on separate lines and always included then the way you describe is fine.

I was thinking more along the lines of using InStr() on the actual markers such as ‘Interest Rate:’ and ‘Next Reset Date:’ and getting the data from the end of that marker up until the next marker or end of line.

And the marker signatures also define the data type of the text returned, apart from the date which is ambiguous. (Month/day or day/month needs to be known because the value will be in text.)

But I can’t do that if ‘Outstatding balance:’ is misspelt.

BTW, Bill’s function seems to have a bug in it in as much that Dim objOLApp As Object is used on the line Set outNameSpace = objOLApp.GetNamespace("MAPI") before it is set to something. You can tell him if you like.

In any case I’ve got it down to this:-

Sub TestIt()
    Dim strSubject   As String
    Dim strBody      As String
    Dim objNameSpace As Object

    strSubject = "Test"
    Set objNameSpace = GetObject("", "Outlook.Application").GetNamespace("MAPI")

    Do
        strBody = GetOutlookBody(objNameSpace, strSubject)
        MsgBox strBody  [color=green]' Here we need the exact definition of the data.[/color]
    Loop Until strBody = ""
    
End Sub

Public Function GetOutlookBody(ByRef objNameSpace As Object, _
                               ByVal strSubject As String) As String
    Dim objMail As Object

    Const olFolderInbox        As Long = 6
    Const olFolderDeletedItems As Long = 3

    [color=green]' Search for Subject in Inbox.[/color]
    For Each objMail In objNameSpace.GetDefaultFolder(olFolderInbox).Items
        If objMail.Subject = strSubject Then
            GetOutlookBody = objMail.Body
            objMail.Move objNameSpace.GetDefaultFolder(olFolderDeletedItems)
            Exit For
        End If
    Next objMail

End Function

But it is at the point of the comment “Here we need the exact definition of the data.” that I need a sample of the actual data and not some typo’s.

Fun and games.

Regards,
Chris.

 

[pbaldy]

Thanks Chris, just emailed him. You're certainly correct that much depends on the actual contents. I was just giving some general direction.

 

[atol]

Hey guys,
Apologies about the misspelled word. I wanted to conceptually understand if this is doable; and it seems it is, based on what you guys have mentioned on this tread.
This is a good start for me. Of course I don’t expect to pick up everything you are saying here since it requires some in-debt knowledge, but eventually I will get there…J
Paul and Chris – Thanks again!

 

[pbaldy]

Happy to help! Post back if you get stuck.