VBA Express : Excel - Validating an email

Excel

Validating an email

Ease of Use

Easy

Version tested with

2003

Submitted by:

Paleo

Description:

This code validates an email syntax and its domain, and can be used either in a VBA routine, or in a worksheet cell.

Discussion:

Sometimes we need to validate and e-mail in our application. This VBA code does this.

Code:

instructions for use

			Sub email() 
    Dim txtEmail As String 
    txtEmail = InputBox("Type the address", "e-mail address") 
     
    Dim Situacao As String 
     
     ' Check e-mail syntax
    If IsEmailValid(txtEmail) Then 
        Situacao = "Valid e-mail syntax!" 
    Else 
        Situacao = "Invalid e-mail syntax!" 
    End If 
     ' Shows the result
    MsgBox Situacao 
End Sub 
Function IsEmailValid(strEmail) 
    Dim strArray As Variant 
    Dim strItem As Variant 
    Dim i As Long, c As String, blnIsItValid As Boolean 
    blnIsItValid = True 
     
    i = Len(strEmail) - Len(Application.Substitute(strEmail, "@", "")) 
    If i <> 1 Then IsEmailValid = False: Exit Function 
    ReDim strArray(1 To 2) 
    strArray(1) = Left(strEmail, InStr(1, strEmail, "@", 1) - 1) 
    strArray(2) = Application.Substitute(Right(strEmail, Len(strEmail) - Len(strArray(1))), "@", "") 
    For Each strItem In strArray 
        If Len(strItem) <= 0 Then 
            blnIsItValid = False 
            IsEmailValid = blnIsItValid 
            Exit Function 
        End If 
        For i = 1 To Len(strItem) 
            c = LCase(Mid(strItem, i, 1)) 
            If InStr("abcdefghijklmnopqrstuvwxyz_-.", c) <= 0 And Not IsNumeric(c) Then 
                blnIsItValid = False 
                IsEmailValid = blnIsItValid 
                Exit Function 
            End If 
        Next i 
        If Left(strItem, 1) = "." Or Right(strItem, 1) = "." Then 
            blnIsItValid = False 
            IsEmailValid = blnIsItValid 
            Exit Function 
        End If 
    Next strItem 
    If InStr(strArray(2), ".") <= 0 Then 
        blnIsItValid = False 
        IsEmailValid = blnIsItValid 
        Exit Function 
    End If 
    i = Len(strArray(2)) - InStrRev(strArray(2), ".") 
    If i <> 2 And i <> 3 Then 
        blnIsItValid = False 
        IsEmailValid = blnIsItValid 
        Exit Function 
    End If 
    If InStr(strEmail, "..") > 0 Then 
        blnIsItValid = False 
        IsEmailValid = blnIsItValid 
        Exit Function 
    End If 
    IsEmailValid = blnIsItValid 
End Function

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
From the Menu, choose Insert-Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.

Test the code:

Go to Tools-Macro-Macros or simply press Alt + F8.
Choose "email" and then choose "Execute" or simply double-click "email".
An InputBox will appear asking you whats the e-mail address
A MsgBox will appear telling you if its a valid one or not
You can also use this in a formula on your worksheet as follows =IsEmailValid("someaddy@somedomain.com")
If the email address is valid, the formula will return TRUE

Sample File:

CheckEmailSyntax.zip 9.5KB

Approved by Paleo

This entry has been viewed 652 times.