Thread: [SOLVED] How to Verify that the contents of the cell is an Email Addy

As there was no response about using an external .msg file in order to send information from within Excel, I will have to manage with the attached.

I do, however, need help to verify that the contents of the cell is an Email Addy.
Currently I can only test to see if it's blank.

If anyone has better suggestions about using a .msg file OR how to check that the contents are in email@address.com format, PLEASE send me a note.

Scott
mailto:sellen@aol.com

~~~~~~~~~~~~~~~~~~

Sub Email_standard_Product_info()
'
' Email_standard_Product_info Macro
' This macro will read the current cell (which should contain an email@address.info type of contents) and then send a message and standard product literature to that addy.
'
' Keyboard Shortcut: Ctrl+m
'
' Macro recorded 01-11-2011 By Scott Ellenwood
'
    'Dimension the Variables as Strings or Objects
    Dim OutApp As Object
    Dim OutMail As Object
    Dim ToAddy As String
    Dim Signature As String
    Dim Bodytext As String
    
    'Copy the contents of the current cell as the TO address. Needs to be in name@whatever.com format
    ToAddy = ActiveCell.Value

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    
    'Designate the text etc that will comprise the body of the message or signature.
    '   How to bold it??
        Bodytext = "Dear Sir or Madam -" & vbCrLf & vbCrLf & _
            "Attached, Please Blah Blah Blah"& vbCrLf & _
            "Regards,"
              
    'Now Designating the contents of the Signature using the following text.
        Signature = "Wonderful Little Me" & vbCrLf & _
            "Outside Sales And Development"
          
    
    'Test to make certain the Email TO value is not blank
    If ActiveCell.Value = "" Then
        GoTo ErrorTrap1
    Else
    End If

    On Error Resume Next
    With OutMail
            .To = ToAddy
            .CC = ""
            .BCC = ""
            .Subject = "Product Information Enclosed"
            .Body = Bodytext & vbCrLf & Signature
            .Attachments.Add "C:\other_stuff.pdf"
            .Send
            .Close SaveChanges:=False
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = False

    End With
    MsgBox "Email Sent to [ " & ToAddy & " ]. Have a nice day."
    GoTo EndofMacro
    
ErrorTrap1:
    MsgBox "Sorry, Charlie. Blank Cells Ain't gonna work."
    
EndofMacro:
End Sub

Function GetBoiler(ByVal sFile As String) As String
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

Hello Scott,

I have included 2 different e-mail validations. The first is very basic. It simply checks that the address contains one or more characters, an "@" symbol, one or more characters followed by a period "." and one or more characters after that.

The second method is based on Function validates an email address as defined in RFC 5321 and RFC 5322 standards using Regular Expressions. The email address must pass six tests before it is considered valid.

However, e-mail systems may also impose further syntax checking. This may lead to an e-mail address being invalid even though it is considered valid by the Internet Standards.

Basic E-mail Address Test

    'Copy the contents of the current cell as the TO address. Needs to be in name@whatever.com format
    ToAddy = ActiveCell.Value

    'EXAMPLE 1
    If Not ToAddy Like Like "?*@?*.?*" Then
           MsgBox "You entered and Invalid e-mail Address"
           Exit Sub
        End If

    'EXAMPLE 2
    If Not IsEmailAddress(ToAddy) Then
           MsgBox "You entered and Invalid e-mail Address"
           Exit Sub
        End If

UDF to Validate E-mail Address Based on Internet Standards
The email address must pass six tests to be considered valid. Copy this code into a Standard VBA module in your workbook.

'Written: November 09, 2010
'Author:  Leith Ross
'Summary: Function validates an email address as defined in RFC 5321 and
'         RFC 5322 standards using Regular Expressions. The email address
'         must pass six tests before it is considered valid.

Function IsEmailAddress(ByVal Email_Address As String) As Boolean

 'Check if the string conforms to the basic internet Email address standards.
 'The string is an Email address if it has the form of <local>@<domain.name>
 'and both parts have no invalid characters. Each Email system will decide if
 'the address syntax is acceptable or not based on its own internal standards.
 
  Dim DomainName As String
  Dim EmailError As Boolean
  Dim LocalPart As String
  Dim RegExp As Object
  
    Set RegExp = CreateObject("VBScript.RegExp")
      
     'Check if the @ sign missing?
      RegExp.Pattern = "^(.+)\@(.+)$"
        EmailError = Not RegExp.Test(Email_Address)
      
     'Extract the Local part and the Domain Name.
      LocalPart = RegExp.Replace(Email_Address, "$1")
      DomainName = RegExp.Replace(Email_Address, "$2")
            
     'Validate Local part characters.
      RegExp.Pattern = "([^\!\\\""\#\$\%\&\'\*\+\-\/\=\?\^\_\`\~\{\}\.0-9\@A-Za-z]+)"
        EmailError = EmailError Or RegExp.Test(LocalPart)
      
     'Check the Local part doesn't start or end with a period.
      RegExp.Pattern = "^(\..)|(.\.)$"
        EmailError = EmailError Or RegExp.Test(LocalPart)
      
     'Validate Domain name characters.
      RegExp.Pattern = "([^\.\-0-9a-z]+)"
        EmailError = EmailError Or RegExp.Test(DomainName)
      
     'Check the Domain name doesn't start or end with a hyphen.
      RegExp.Pattern = "^(\-.)|(.\-)$"
        EmailError = EmailError Or RegExp.Test(DomainName)
     
    IsEmailAddress = Not EmailError
    
End Function