Parsing values from an email import

[shaunacol]

I have imported some emails and now want to extract the name of a person and their email address from the contents of the email. Their Email address in the contents of the email occurs after the work E-mail so using the code below in a query looks as though it truend the value beautifully. however, it also returns the rest of the email content after the email address! Is there another bit of code that I can write to delete everything after the email address or perhaps a better way of extracting. I have included the contents of the email as well below.



Mid([Contents];InStrRev([Contents];"E-mail")+7)




EMAIL CONTENTS
Hi Chris,

The LANDING has been submitted, see below for details:


First Name: patrickdoyle
E-mail: patrickdoyle748@BTINTERNET.com
Phone: 0000000


This form was submitted live


-----
No virus found in this message.
Version: 2012.0.2221 / Virus Database: 2441/5331 - Release Date: 10/14/12

 

[PHV]

A starting point:
Left(Mid([Contents],InStr([Contents],"E-mail: ")+8),InStr(Mid([Contents],InStr([Contents],"E-mail: ")+8),Chr(10))-1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shaunacol]

That lookgs good but I get th error "invalid syntax" it says I have ommitted an operand, entered an invlaid comma or character or entered text without surroounding by quotation marks. Not Can anyone help

 

[PHV]

Try to replace all commas (,) with semi-colons (.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shaunacol]

I tried but am now getting 8 characters from the beginning of the email up until about charater 28. Not sure why...ill keep looking

 

[Golom]

Alternatively

Mid$([Contents], InStr([Contents],"E-mail: ") + 8, 
InStr(InStr([Contents],"E-mail: ") + 8, 
[Contents], Chr$(10)) - (InStr([Contents],"E-mail: ") + 8))

You might also try changing Chr$(10) to Chr$(13) just in case the line break is a carriage return rather than a line feed.

You could also set up a public function that returns the Email address

Public Function GetEmail(ByVal Contents As String, _
                     Optional SearchFor As String = "E-Mail: ") As String

Dim BeginAt                    As Integer
Dim EndAt                      As Integer
Dim EndAtCR                    As Integer
Dim EndAtLF                    As Integer
Dim Length                     As Integer

BeginAt = InStr(LCase$(Contents), LCase$(SearchFor)) + Len(SearchFor)
If BeginAt > Len(SearchFor) Then
    EndAtCR = InStr(BeginAt, Contents, vbCr)
    EndAtLF = InStr(BeginAt, Contents, vbLf)
    EndAt = IIf(EndAtCR > EndAtLF And EndAtLF = 0, EndAtCR, EndAtLF)
    If EndAt > 0 Then
        Length = EndAt - BeginAt
        GetEmail = Replace(Replace(Mid$(Contents, BeginAt, Length), Chr$(13), ""), Chr$(10), "")
    End If
End If

End Function

Which allows you to handle problems in the structure of the line.

 

[shaunacol]

It worked when I replaced the commas!!!!!!!!!!!!! I had just mistyped something in the process but now it works!!!! Hooray!

Left(Mid([Contents];InStr([Contents];"E-mail: ")+8);InStr(Mid([Contents];InStr([Contents];"E-mail: ")+8);Chr(10))-1)