Code Snippets

In all the times I have spent participating in forum discussions about all sorts of users' questions, I have learnt a great deal from all the participants.

I have decided to post some of the sample codes that I found users were asking for almost all the time to help me keep them in one place and to make it easier for me to share them again and again.

Hope you find them useful.



Extract Email Address using Regular Expression

You may have seen my blog on using regular expression to extract an email address from a Memo field. This function is a modified version of the code in that article. This one will extract all email addresses it can find using regular expression and return the list of email addresses as a delimited string. You can use it to construct a To, Cc, or Bcc address line for an email message.

I rarely use regular expressions and thought that this was a good exercise to try them out. Hope you find it useful and informative.

Public Function ExtractEmailAddress(strData As String, _
	Optional strDelim As String = ";") As String
'http://www.accessmvp.com/thedbguy
'6/28/2015
'Extracts any email address found within a string
'regex email pattern source: 
'http://www.regular-expressions.info/email.html

Dim regEx As Object
Dim regExMatch As Object
Dim var As Variant
Dim strEmail As String

Set regEx = CreateObject("VBScript.RegExp")

With regEx
    .Global = True
    .IgnoreCase = True
    .Pattern = "\b[0-9A-Z._%+-]+@[0-9A-Z.-]+.[A-Z]{2,3}\b"
    Set regExMatch = .Execute(strData)
    For Each var In regExMatch
        strEmail = strEmail & strDelim & var
    Next
End With

ExtractEmailAddress = Mid(strEmail, Len(strDelim) + 1)

Set regEx = Nothing
Set regExMatch = Nothing

End Function