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:

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

Test the code:

  1. Go to Tools-Macro-Macros or simply press Alt + F8.
  2. Choose "email" and then choose "Execute" or simply double-click "email".
  3. An InputBox will appear asking you whats the e-mail address
  4. A MsgBox will appear telling you if its a valid one or not
  5. You can also use this in a formula on your worksheet as follows =IsEmailValid("someaddy@somedomain.com")
  6. 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 635 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express