Find email address in text file using vba

[Guest]

I need to find email address in a text file (c:\File.txt). The file is not
delimited, but all email addresses have "@" in them. How can I find the email
address (there would be only one per file, but there are other words in the
file), and then pass it to strEmail = "The found email address".

Thanks.

- Steve

 

[John Nurick]

Hi Steve,

The simplest way is to read the text file into a string variable and
then use a regular expression to find and extract the email address.

This will work fine for "ordinary" email addresses that look like
(e-mail address removed). (Things are *much* more
complicated if you need to find any syntactically valid email address
rather than just this common form.)

The FileContents() and rgxExtract() functions here
http://www.j.nurick.dial.pipex.com/Code/index.htm
will do most of the work. There's also a link on that page to
information about regular expressions.

There are some regular expressions for matching email addresses at
http://regexlib.com/Search.aspx?k=email

 

[Guest]

John,

Thanks for the code.

I have it in the modules, but am getting "Invalid use of Null" This is the
code I have:

Private Sub Command35_Click()

Dim strEmail As String
Dim strExtract As String
Dim strContent As Variant

strContent = FileContents("e:\rep_temp.txt", False, 1)
strExtract = rgxExtract(strContent,
"^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$")
strEmail = strExtract

olSendRpt strEmail, "Please see registration information below." & vbCrLf,
"Registration", "Registration"

End Sub

The file "e:\rep_temp.txt" has many words and dashes and then an email
address somewhere in the file. I am trying to extract that email address and
place in the strEmail part of the olSendRpt function.

Thanks again.

- Steve

 

[John Nurick]

Steve,

It's midnight, so this is a bit sketchy. One thing that seems wrong is
your passing 1 as the optional third argument to FileContents(). This
needs to be a variable declared as a Long and FileContents() uses it to
return an error code if FileContents() doesn't run successfully.
Simplest to omit it.

Next: the Invalid Use Of Null error often results from an attempt to
assign a Null value to a String variable. Both FileContents() and
rgxExtract return Variants which normally contain strings but may be
Null (e.g. if the file can't be found or if rgxExtract doesn't find a
matching string).

Last: the regex you use begins with ^ - signalling the beginning of the
string - and ends with $ - signalling the end of the string.
Consequently it will only match if the string contains nothing but an
email address. Try omitting these two characters.

John,

Thanks for the code.

I have it in the modules, but am getting "Invalid use of Null" This is the
code I have:

Private Sub Command35_Click()

Dim strEmail As String
Dim strExtract As String
Dim strContent As Variant

strContent = FileContents("e:\rep_temp.txt", False, 1)
strExtract = rgxExtract(strContent,
"^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$")
strEmail = strExtract

olSendRpt strEmail, "Please see registration information below." & vbCrLf,
"Registration", "Registration"

End Sub

The file "e:\rep_temp.txt" has many words and dashes and then an email
address somewhere in the file. I am trying to extract that email address and
place in the strEmail part of the olSendRpt function.

Thanks again.

- Steve

Hi Steve,

The simplest way is to read the text file into a string variable and
then use a regular expression to find and extract the email address.

This will work fine for "ordinary" email addresses that look like
(e-mail address removed). (Things are *much* more
complicated if you need to find any syntactically valid email address
rather than just this common form.)

The FileContents() and rgxExtract() functions here
http://www.j.nurick.dial.pipex.com/Code/index.htm
will do most of the work. There's also a link on that page to
information about regular expressions.

There are some regular expressions for matching email addresses at
http://regexlib.com/Search.aspx?k=email