Need help with my valid email address check function!

[Ten98]

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:

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?

 

[pgc01]

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]

Try

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]

Sorry yes, a semi colon separated with a space.

VoG, that works perfectly, thanks so much

 

[pgc01]

Another option:

Replace in the original code the pattern:

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

 

[Ten98]

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:

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...

 

[Norie]

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]

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:

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