Validating an Email Address

[Maneesh Massey]

Hello,
I am working on an application in Excel VBA which is almost complete. Just need to know how to validate email addresses in text box entry on a UserForm.
I googled and found this code from a website.

I would like to validate email addresses on Textbox_Exit Event on the form.

Option Explicit

Const MODULE_NAME As String = "modMail"
'' Validate email address
Public Function ValidateEmailAddress(ByVal strEmailAddress As String) As Boolean
On Error GoTo Catch

Dim objRegExp As New RegExp
Dim blnIsValidEmail As Boolean

objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"

blnIsValidEmail = objRegExp.Test(strEmailAddress)
ValidateEmailAddress = blnIsValidEmail
Exit Function

Catch:
ValidateEmailAddress = False
MsgBox "Module: " & MODULE_NAME & " - ValidateEmailAddress function" & vbCrLf & vbCrLf _
& "Error#: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

I do not how to use the above code. Where should this code go and how to call it from the Textbox_Exit Event ?
Please help.
Appreciate all help.
Regards,
Maneesh

 

[Hui]

Maneesh
Copy the code and paste it into a Code Module

To use it simply use =ValidateEmailAddress(email_address)

where the email_address is a string

It will return true if it's a valid email address
False if it's not

 

[Maneesh Massey]

Maneesh
Copy the code and paste it into a Code Module

To use it simply use =ValidateEmailAddress(email_address)

where the email_address is a string

It will return true if it's a valid email address
False if it's not

Hi,
Wow Brilliant !!! Tested your code above and it works like a charm !!! Thanks so much, appreciate you so much !!!
Regards,
Maneesh

 

[SirJB7]

Hi, Maneesh Massey!

Just a warning light: you won't be able to fully validate email addresses, since each service provider or webmail portals may have different constraints, the one it now comes to my mind is that about the no. of dots allowed in the name address (left to the @), some services allow you 1 dot, others more than 1, and surely others would allow none.

Regards!