Email hyperlink to text

[Pat]

I have emails where i am trying to pickup the email address embedded in the body.
The email addresses are typically:
"mailto:xxxxx@iprimus.com.au"xxxxx@iprimus.com.au
is there a clever way to extract the email address from that or am i up for coding to extract it?

[HansV]

You have posted this in the Access forum. Are the e-mails stored in Access, or should Access check your inbox, or ...?

[Pat]

This is in Access using Outlook automation to check the inbox.

[HansV]

1) Will there be one e-mail address in the body or several ones?
2) Will the e-mail address always be listed in the form "mailto:address"adress ?

[Pat]

1. i need to first email address i come to.
2. yes, it would appear so

[HansV]

Assuming that you know how to automate Outlook and set a string variable strBody to the Body of an Outlook.MailItem object:

Code: Select all

  Dim strBody As String
  Dim intPos1 As String
  Dim intPos2 As String
  Dim strMailAddress As String

  ...

  strBody = ...
  intPos1 = InStr(strBody, Chr(34) & "mailto:")
  If intPos1 > 0 Then
    intPos2 = InStr(intPos1 + 8, strBody, Chr(34))
    If intPos2 > 0 Then
      strMailAddress = Mid(strBody, intPos1 + 8, intPos2 - intPos1 - 8)
    End If
  End If
  ...

If strMailAddress is an empty string "" at the end of this code fragment, no e-mail address was found.

[Pat]

Thanks but i have already coded that, i was wondering if there was a more elegant way of doing it.

[HansV]

Not that I know of.

[Pat]

Didn't think so, but then again it's amazing what you come up with from time to time.

Thanks again Hans

[grovelli]

Hi Pat, if you viewed your inbox folder as a linked table, couldn't you use SQL rather than VBA to process it?

[HansV]

Extracting the e-mail address in a query would be complicated but possible, but I don't know how Pat wants to use the e-mail addresses.