using a query to extract information from a text field

[shaunacol]

I have linked my MS Access 2016 dbase to my Outlook and the table is working. I now want to extract some information from the content of the emails. An example of the email is below. How can I extract the customer name, number of tickets please? Its been a few years since I programmed and am rusty but I can remember doing this before from within a query. Any help appreciated!



You have received an order from John Doe. The order is as follows:


Order #18893 (June 23, 2017)

No of tickets 3 adults, 2 children

 

[MajP]

There are a lot of string functions. Are the messages always in a common format? If so you can find the position of "order from" a pull everything up to the period. Look at instr, mid, left, right string functions.

 

[shaunacol]

Thanks so much. Yes the emails are always exactly the same format. The problem I can foresee with these string functions is that they seem to require me to specify the exact number of characters. I'm not sure how I can specify that when I don't know how long or short the customer name is. What I really need is to say "find 'order from'" then "tell me next 2 words after this". Not sure if that's possible but ill have a play around. thanks again for pointing me in right direction.

 

[dhookom]

The first function MajP suggested is Instr(). This will provide the start of one string within another string.

? instr("You have received an order from John Doe. The order is as follows:","order from")
 22

You can also find the first period and do the math to return Mid().

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

It would also help if you would indicate which portions of your strings are constant, and which ones change.
For example, let's say all [Blue]Blue[/Blue] parts change and they are the once you want to retrieve:

You have received an order from [Blue]John Doe[/Blue]. The order is as follows:
Order #[Blue]18893 (June 23, 2017)[/Blue]
No of tickets [Blue]3[/Blue] adults, [Blue]2[/Blue] children

You may also check Split() function...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Public Function GetName(EmailText As Variant) As String
  Dim lngStart As Long
  Dim lngEnd As Long
  If Not IsNull(EmailText) Then
    lngStart = InStr(EmailText, "Order from ")
    lngStart = lngStart + Len("order from ")
    lngEnd = InStr(lngStart, EmailText, ".")
    GetName = Mid(EmailText, lngStart, lngEnd - lngStart)
    GetName = Trim(GetName)
  End If
End Function

Assuming the emails are exactly like you say and the only thing that changes is the name and the ticket counts, the above will return john doe
In a query you call the function and pass the email field name like
select field1, field2,...GetName([SomeField]) as OrderName

The Get number of tickets would be similar but not sure what you want. Do you want 5, or two fields 3 and 2, or the string "3 adults, 2 children".

 

[strongm]

A regular expression solution:

[blue]Public Function getOrder(strText As String) As String()
    With CreateObject("vbscript.regexp")
        .Pattern = "(?:.*m )(.*)(?:\.[\s\S]*s )(.*)\r"
        getOrder = Split(.Replace(strText, "$1|$2"), "|")
    End With
End Function[/blue]

 

[dhookom]

Strongm,

Probably a very elegant and efficient solution but it's a bit presumptuous to think most people here could apply this without some additional assistance or links to resources. I would expect a solution that requires copying the code into a module and then using something like:

GetOrder("Order #18893 (June 23, 2017)")

GetOrder("No of tickets 3 adults, 2 children")

My ignorance is showing ;-)

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[strongm]

>it's a bit presumptuous

Seriously? More presumptuous than providing an overview of instr without then explaining how that might be used to extract the relevant data?

Feed the whole email to it, and it returns an array with the two bit of info required. It is possible that the ticket info isn't quite in the format required, but given the example code that ought (given these are work forums for computer professionals) to be reasonably straightforward to figure out.

So ... assuming MailText contains the entire text we need to analyse as per the OP

[blue]Option Explicit

Private Sub CommandButton1_Click()
    Dim bitofinfo
    For Each bitofinfo In getOrder(MailText)
        MsgBox bitofinfo
    Next
End Sub

Public Function getOrder(strText As String) As String()
    With CreateObject("vbscript.regexp")
        .Pattern = "(?:.*m )(.*)(?:\.[\s\S]*ts )(.*)"
        getOrder = Split(.Replace(strText, "$1|$2"), "|")
    End With
End Function[/blue]

This is not production code BTW, just an illustration of what you can do.