Extract data from outlook email body

[didacticone]

Hi Everyone,

I have linked my outlook folder to a table in access. My problem is the body of the email is in a field called contents. I need to extract certain information from the body of the email or contents field. For example, within the body of the email there is a start date, laid out in the email as "Start Date/Time: 1/1/18 at 7:00". I need to be able to extract that piece of information for queries and the like. Perhaps making it its own field? I am new at working with outlook imports and appreciate any feedback. Thank you!

[NeoPa]

If that's really the format of your date and time then you should look at the basic formats on how your version of Windows is configured. I thought 2-digit years went out at the end of the last millennium, but maybe there are still those yet to learn their lessons. Times in non 24-hour format need an AM or PM indicator.

All that said, and I will assume you're using the American date format as your example data leaves that unclear, you could use something like the following to get a full Date/Time value from that string :

StartPoint: CDate(Replace(Replace([Start Date],'Start Date/Time: ',''),' at ',' '))

Obviously this will only work as required if the information you've included is accurate. If any names need to be changed you'll need to do that before testing.

[didacticone]

I appreciate your help, and apologies for not making this clear. the emails I am receiving are from an outside company in regards to work that needs to be completed by my company. I do not have control over how they format anything in the body of their emails. I am just trying to work with what I am given. The body of the email contains a lot of data, the exact row in the body of the email looks like this "Start Date/Time: 07/23/19 At 08:00 Expiration Date: 09/19/19" I am not concerned about format, I am looking to extract that, or for that matter, learn how to extract any information from the body of an email into usable data for queries, etc. I would like to be able to query by that date and access would pull up everything with that date as a result. With it all condensed in the one field I dont believe i can do that correct? Thank you.

[NeoPa]

I'm surprised by your response. I showed in my earlier post how it could be done for the format of string that you posted originally. It sounds like you didn't try that.

It may be that, as you now say there's more data in the string you're working from than originally specified, we may need to ensure that's properly handled. Other than that though, what I posted earlier should get you there with tweaks depending on exactly how the data is presented to you. All I know at the moment is that there's more data after that containing the date and time, but apparently that's still at the start. That's fine for the start of it but how can we recognise the end when the lengths of the dates themselves can vary? You've not told us much about the format of the data itself or what Field it's in for instance, so finding a way process it will be difficult. What we're looking for to help determine how to approach this is what data can be relied on after the date string. It clearly isn't formatted in a way to make life easy for you. That's why two digits for days, months, hours and generally four digits for years is used so heavily. It displays in a way that's easy to read from lists and it's much easier to process and convert to & from text. Obviously whoever prepared the format of your data didn't appreciate those subtleties - and you're left with the fallout. Nice.

There are ways to handle most things, but until you give more details of what's where it's very hard to know what might be appropriate for your situation.

[didacticone]

I understand what you provided, but yes the issue is, it is the body of an email, and as i said in my first post when imported over, the body of an email goes into a field that is auto created called contents. you are correct that the people providing this information are not concerned with making my life easier, but i have certain requirements that i have to follow and figuring this out would certainly make everything easier. What i did not explain fully enough was that there is text before the line with the start date and text after the line. It also is not a consistent amount of spaces. every email has certain variables that will move where that start time/date is. so basically i was trying to see if there is a code to use certain words as a starting point, perhaps some combination of the mid or instr function? Or maybe it is better to not link the email account directly but use vba to import manually with a command button? again working with importing from outlook is new to me so im just looking for some advice, because as you said i am most certainly left with dealing with the fallout lol... thanks for your help

[NeoPa]

There certainly are ways. I was looking to you for information on what you CAN rely on. The text immediately beforehand is important info. As yet we have no knowledge of what must come afterwards - if indeed there is anything that predetermined.

Obviously the easiest way to handle this is to provide a Public Function from a Standard Module that does the heavy lifting for you. That way you can use VBA features that are unavailable to SQL directly (When I say SQL here I'm referring to The Expression Service (ES) which is used to handle calls that are not native SQL from either the Jet or ACE SQL engines). The easiest approach, or maybe just the first that comes to my mind, is to use Split(), but this relies on the ability to handle array references. Easy in VBA. Unsupported by the ES.

If there were something else known, and 100% reliable, about the data we could look at a SQL/ES-only solution. On the other hand, if there may be a number of different requirements that you will have to handle across the [Contents] field (Sorry. From your earlier use of the word (contents) I was under the impression you were using it as a noun rather than a proper noun, IE a general descriptive term rather than the name of a Field per se).

It may well be that you would be best served by a Function, as described earlier, that will take the [Contents] Field data as well as another parameter indicating which result is required, and return data that matches the specified requirement. As a basic start you could have :

Public Function Translate(strContents As String, strReturn As String) As Variant
    Dim strWork As String

    Select Case strReturn
    Case "StartDate"
        'Assumes "." comes after the relevant text in [Contents].
        strWork = Split(Split(strContents, "Start Date/Time: ")(1), ".")(0)
        Translate = CDate(Replace(strWork, " at ", " "))
'    Case "..."
'        Translate = ...
    Case Else
        Translate = Null
    End Select
End Function

Notice the return value is declared as Variant so when no value is found then it can return Null, but it also allows Dates and other Variant types to be used as return values of the Function.

NB. I wasn't being unhelpful before. It's just that many don't understand how important information is when considering how best to handle it. Slightly different environments (or data structure) can mean great differences to how best it should be handled. As a general rule I will always avoid the Public Function approach as it is not very performant for large quantities of records. On the other hand it's very flexible and powerful. From what we know so far it's the only approach that we know can work.

[Rabbit]

I think the misunderstandin g here is the word "format"

I presume the OP is referring to visual formatting. i.e. italics, bold, font size, etc.

NeoPa is referring to data formatting. e.g. Start Date/Time vs Start Time/Date