Validating email To field value in VBA

Question

I am looking to validate values sent to an Outlook email in VBA

I have found several examples, such as :-

http://www.geeksengine.com/article/validate-email-vba.html

Using the code from the site above, the email address [email protected] is returned True, or valid. However, [email protected]; [email protected] is returned as invalid. Whilst this isn't a valid email address, it is a valid value for a To field in Outlook.

Is it possible to validate a value such as [email protected]; [email protected] using VBA?

Answer 1

Validating an Outlook To field is a hard task.

Consider the following lines:

[email protected]<SomeName;[email protected] 'Valid, 2 addresses, first one named SomeName
a@a<a.com 'Invalid, < needs to be escaped
[email protected];;[email protected]; 'Valid, 2 addresses
[email protected];a 'Invalid, second address is not valid
a<[email protected] 'Weirdly enough, this is valid according to outlook, mails to [email protected] 
          '(ignores part before the <)
[email protected]<[email protected] 'But this isn't valid 
                '(closing > needed in this specific case, mail address = [email protected])

The only reasonable way to validate an Outlook To field in my opinion, is to check if Outlook thinks it's valid. Any approximation is bound to go wrong.

You can use the following code to let Outlook validate the to string, and check if it can determine a mail address for each field

Public Function IsToValid(ToLine As String) As Boolean
    Dim olApp As Object 'Outlook.Application
    Dim mail As Object 'Outlook.MailItem
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If Err.Number = 429 Then
        Set olApp = CreateObject("Outlook.Application")
    End If
    On Error GoTo 0
    Set mail = olApp.CreateItem(0)
    Dim rp As Object 'Outlook.Recipient
    With mail
        .To = ToLine
        .Recipients.ResolveAll
        For Each rp In .Recipients
            If rp.Address & "" = "" Then
                mail.Delete
                Exit Function
            End If
        Next
    End With
    mail.Delete
    IsToValid = True
End Function

Answer 2

Use the Split() function to split the string into the individual addresses, and check these in a loop with your function.

If all addresses are valid, the original string is valid.

The nice thing about it: you don't need separate cases. A single address without ; will return a single array element from Split(), and the loop will simply run once.