Need help with my valid email address check function!

Ten98

Board Regular
Joined
Aug 24, 2010
Messages
53
Ok, so I have a fabulous little function which uses a regular expression to check if a string is a valid email address or not:

Code:
Function isemail(inputtext As String) As Boolean
'Detects if a text string is a valid email address
isemail = False
With CreateObject("VBScript.RegExp")
    .Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
    If .test(inputtext) Then
    isemail = True
    End If
End With
End Function

it works great and returns the boolean "True" if the string is a valid email address and "False" if it is not.

However...

My users are starting to be a little clever, and want to include MULTIPLE email addresses in their input fields separated by semi-colons...

So for example: Joe.Bloggs@somemail.com; Dave.Bloggs@somemail.com; Steve.Bloggs@somemail.com

This doesn't break anything EXCEPT for my validity checker, which sees them as one long, invalid string.

I'm not very good with regular expressions, I got the expression in my function from this forum. Is there a way that my expression could be modified to allow this type of list to be checked? It would need to be able to check any number of email addresses.

If that can't be done with a regular expression, how could I modify my function so it sees this type of list as valid?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,855
Hi

In the text you say that the email addresses are separated by semicolons but in the example you posted they are separated by a semicolon followed by a space.

Can you clarify the syntax?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Function isemail(inputtext As String) As Boolean
'Detects if a text string is a valid email address
Dim i As Long
Dim x
isemail = True
x = Split(inputtext, "; ")
With CreateObject("VBScript.RegExp")
    .Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
    For i = LBound(x) To UBound(x)
        If Not .test(x(i)) Then
            isemail = False
            Exit Function
        End If
    Next i
End With
End Function
 

Ten98

Board Regular
Joined
Aug 24, 2010
Messages
53
Sorry yes, a semi colon separated with a space.

VoG, that works perfectly, thanks so much :)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,855

ADVERTISEMENT

Another option:

Replace in the original code the pattern:

Code:
    .Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}(; [\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3})*$"
 

Ten98

Board Regular
Joined
Aug 24, 2010
Messages
53
I thought some more about the with or without spaces question, and actually it's likely that my users will use single spaces, no spaces, and too many spaces both in front of and behind the semi-colon.

Outlook deals with multiple email addresses in the address bar perfectly well regardless of how many spaces you put before or after the semi-colon. Since my macro is loading the strings into Outlook, I figured it's silly to penalise my users for mistakes which don't matter...

Here's my rather amateurish way to solve the "semi colons with or without spaces" user input issue:

Code:
Function isemail(inputtext As String) As Boolean
'Detects if a text string is a valid email address or a list of email addresses separated by semi colons and up to 5 spaces either side of the semi colon.
inputtext = Replace(inputtext, "  ", " ")
inputtext = Replace(inputtext, "  ", " ")
inputtext = Replace(inputtext, "  ", " ")
inputtext = Replace(inputtext, "; ", ";")
inputtext = Replace(inputtext, " ;", ";")
Dim i As Long
Dim x
isemail = True
x = Split(inputtext, ";")
With CreateObject("VBScript.RegExp")
    .Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$"
    For i = LBound(x) To UBound(x)
        If Not .test(x(i)) Then
            isemail = False
            Exit Function
        End If
    Next i
End With
End Function

Works perfect, and allows up to 5 spaces before and/or after the semi colon, but still returns a value of FALSE if they put a space in the email address itself...
:biggrin:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,835
Office Version
  1. 365
Platform
  1. Windows
If there is definitely going to be a colon delimiting the email addresses you could still use the Split function VoG used.

All you would 'need' to do would get rid of the leading/trailing spaces using Trim.

Then the users can have as many spaces as they want, just as long as they use the colon.:)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,855
I thought some more about the with or without spaces question, and actually it's likely that my users will use single spaces, no spaces, and too many spaces both in front of and behind the semi-colon.

Another option:

Replace in the original code the pattern:

Code:
    .Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}(\s*;\s*[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3})*$"
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,429
Members
409,876
Latest member
Akash Yadav
Top