Solved

How do I check for a valid email address before sending?

Posted on 2010-09-01
13
1,207 Views
Last Modified: 2012-06-21
Hello experts;

I have an Access database with code that sends out emails, the pointy end of the code looks like;

                        Set objOutlookRecip = objOutlookMsg.Recipients.Add(clientemail)
                        objOutlookRecip.Type = olTo
                        objOutlookMsg.Send

The problem is sometimes people have entered invalid email addresses, and when it gets to the send line, the code breaks.

How do I check BEFORE sending that the email address is valid according to Outlook?
0
Comment
Question by:OzoneFriendly
  • 3
  • 2
  • 2
  • +4
13 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 125 total points
ID: 33575536
Validate email addresses using VB.NET
http://www.geekpedia.com/code18_Validate-email-addresses-using-VB.NET.html

Validate email address with Regular Expressions
http://www.dreamincode.net/code/snippet1394.htm

Check an Email Address for Validity (VB.NET and VB6)
http://www.freevbcode.com/ShowCode.asp?ID=4904

Effective Email Address Validation
http://www.codeproject.com/KB/validation/Valid_Email_Addresses.aspx
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 125 total points
ID: 33575561
OzoneFriendly,

One way would be to use Regular Expressions to see if the email address "looks" valid. You can add the function below to a regular VBA module; the function is fully described in my article http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

You can then use the RegExpFind function to validate the email address:

If RegExpFind(clientemail, "[\w-]+(\.[\w-]+)*@[\w-]+(\.[\w-]+)*\.[A-Za-z]{2,4}", 1) = "" Then
'code for invalid email address
End If

Patrick
Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _

    Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _

    Optional MultiLine As Boolean = False)

    

    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,

    ' as long as you properly credit and attribute authorship and the URL of where you

    ' found the code

    

    ' For more info, please see:

    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

    

    ' This function relies on the VBScript version of Regular Expressions, and thus some of

    ' the functionality available in Perl and/or .Net may not be available.  The full extent

    ' of what functionality will be available on any given computer is based on which version

    ' of the VBScript runtime is installed on that computer

    

    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a

    ' pattern (PatternStr).  Use Pos to indicate which match you want:

    ' Pos omitted               : function returns a zero-based array of all matches

    ' Pos = 1                   : the first match

    ' Pos = 2                   : the second match

    ' Pos = <positive integer>  : the Nth match

    ' Pos = 0                   : the last match

    ' Pos = -1                  : the last match

    ' Pos = -2                  : the 2nd to last match

    ' Pos = <negative integer>  : the Nth to last match

    ' If Pos is non-numeric, or if the absolute value of Pos is greater than the number of

    ' matches, the function returns an empty string.  If no match is found, the function returns

    ' an empty string.  (Earlier versions of this code used zero for the last match; this is

    ' retained for backward compatibility)

    

    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and

    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).

    

    ' ReturnType indicates what information you want to return:

    ' ReturnType = 0            : the matched values

    ' ReturnType = 1            : the starting character positions for the matched values

    ' ReturnType = 2            : the lengths of the matched values

    

    ' If you use this function in Excel, you can use range references for any of the arguments.

    ' If you use this in Excel and return the full array, make sure to set up the formula as an

    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()

    

    ' Note: RegExp counts the character positions for the Match.FirstIndex property as starting

    ' at zero.  Since VB6 and VBA has strings starting at position 1, I have added one to make

    ' the character positions conform to VBA/VB6 expectations

    

    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases

    ' where a large number of calls to this function are made, making RegX a static variable that

    ' preserves its state in between calls significantly improves performance

    

    Static RegX As Object

    Dim TheMatches As Object

    Dim Answer()

    Dim Counter As Long

    

    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long

    

    If Not IsMissing(Pos) Then

        If Not IsNumeric(Pos) Then

            RegExpFind = ""

            Exit Function

        Else

            Pos = CLng(Pos)

        End If

    End If

    

    ' Evaluate ReturnType

    

    If ReturnType < 0 Or ReturnType > 2 Then

        RegExpFind = ""

        Exit Function

    End If

    

    ' Create instance of RegExp object if needed, and set properties

    

    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")

    With RegX

        .Pattern = PatternStr

        .Global = True

        .IgnoreCase = Not MatchCase

        .MultiLine = MultiLine

    End With

        

    ' Test to see if there are any matches

    

    If RegX.Test(LookIn) Then

        

        ' Run RegExp to get the matches, which are returned as a zero-based collection

        

        Set TheMatches = RegX.Execute(LookIn)

        

        ' Test to see if Pos is negative, which indicates the user wants the Nth to last

        ' match.  If it is, then based on the number of matches convert Pos to a positive

        ' number, or zero for the last match

        

        If Not IsMissing(Pos) Then

            If Pos < 0 Then

                If Pos = -1 Then

                    Pos = 0

                Else

                    

                    ' If Abs(Pos) > number of matches, then the Nth to last match does not

                    ' exist.  Return a zero-length string

                    

                    If Abs(Pos) <= TheMatches.Count Then

                        Pos = TheMatches.Count + Pos + 1

                    Else

                        RegExpFind = ""

                        GoTo Cleanup

                    End If

                End If

            End If

        End If

        

        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the

        ' function's return value

        

        If IsMissing(Pos) Then

            ReDim Answer(0 To TheMatches.Count - 1)

            For Counter = 0 To UBound(Answer)

                Select Case ReturnType

                    Case 0: Answer(Counter) = TheMatches(Counter)

                    Case 1: Answer(Counter) = TheMatches(Counter).FirstIndex + 1

                    Case 2: Answer(Counter) = TheMatches(Counter).Length

                End Select

            Next

            RegExpFind = Answer

        

        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible

        

        Else

            Select Case Pos

                Case 0                          ' Last match

                    Select Case ReturnType

                        Case 0: RegExpFind = TheMatches(TheMatches.Count - 1)

                        Case 1: RegExpFind = TheMatches(TheMatches.Count - 1).FirstIndex + 1

                        Case 2: RegExpFind = TheMatches(TheMatches.Count - 1).Length

                    End Select

                Case 1 To TheMatches.Count      ' Nth match

                    Select Case ReturnType

                        Case 0: RegExpFind = TheMatches(Pos - 1)

                        Case 1: RegExpFind = TheMatches(Pos - 1).FirstIndex + 1

                        Case 2: RegExpFind = TheMatches(Pos - 1).Length

                    End Select

                Case Else                       ' Invalid item number

                    RegExpFind = ""

            End Select

        End If

    

    ' If there are no matches, return empty string

    

    Else

        RegExpFind = ""

    End If

    

Cleanup:

    ' Release object variables

    

    Set TheMatches = Nothing

    

End Function

Open in new window

0
 
LVL 6

Assisted Solution

by:YohanF
YohanF earned 125 total points
ID: 33575566
best thing is to, or a way you can use is regular expressions. I have attached a piece of code I am using, and I should warn you that its not Perfect in terms of checking every bit of validity. However it checks most of it.

Public Function validateEmailAddress(EmailAddress As String) As String
    Dim reEx As RegExp
    Dim reEx1 As RegExp
    
    Set reEx = New RegExp
    Set reEx1 = New RegExp
    
    reEx.Pattern = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}\b"
    reEx.IgnoreCase = True
    
    reEx1.Pattern = "\b[\s]\b"
    reEx1.IgnoreCase = True
        
    If reEx.Test(EmailAddress) = True And reEx1.Test(EmailAddress) = False Then
            validateEmailAddress = "EMAILVERIFIED: Email address is valid"
    Else
        validateEmailAddress = "EMAILVALIDATE: Invalid Email Syntax, please correct the email format and try again" 
    End If
End Function

Open in new window

0
 

Author Comment

by:OzoneFriendly
ID: 33575583
Cool, I'll try and implement one of those solutions and get back to you. I was hoping there was a simple "outlook" command to do it, but this is cool. :-)

Out of interest, would any of these validate as okay the following string;

email1@domain.com; email2@domain.com

I know some instances might be formatted like that, which Outlook WILL accept, but will that code?
0
 
LVL 6

Expert Comment

by:YohanF
ID: 33575592
Mine wont, It will only process an email at a time.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33575600
If you add recipients one at a time, no, passing >1 address will not work.  However, this will:    objOutlookMsg.To = "addr1@foo.com;addr2@foo.com;addr3@foo.com"
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 11

Assisted Solution

by:kbirecki
kbirecki earned 125 total points
ID: 33585190
You can use the Split() function to separate out each address btw the semicolons, process each address individually with one of the methods the others provided, and then concatenate them back together.
0
 
LVL 4

Expert Comment

by:coolcurrent4u
ID: 33592398
Basically, you cannot check if an email address is valid, but you can check if it is proper in "form" e.g someone@domain.tld

1 either you connect to the email domain server and check
2. try sending the email, some servers will reject the email if the email is not balid (ie, does not exist)

To check for valid form, use regular expression
check here for example

http://www.vbforums.com/showthread.php?t=559741&highlight=valid+email+regex
0
 

Author Comment

by:OzoneFriendly
ID: 33872191
I will check out these options and see which one will work in my situation soon. Thanks for the advice.
0
 

Author Comment

by:OzoneFriendly
ID: 34708135
I really must get around to trying these solutions. I've been soooo busy. :-(
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34709276
>> I really must get around to trying these solutions

As far as I see, all possible solutions are provided.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34959143
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now