ExcelBanter - Validate email address

That can be a little tricky... Outlook gurads that list to ensure that
virus's don't use it. Depending on what version you are on this might work
for you...

Option Explicit
Const CdoPR_EMS_AB_PROXY_ADDRESSES = &H800F101E
Const g_strMAPILogOn As String = "MS Exchange Settings"
Const g_strAddressList As String = "Global Address List"
Const g_strEMailAddressIdentifier As String = "SMTP"

Sub Test()

MsgBox GetEMailAddress("James Thomlinson")

End Sub

Public Function GetEMailAddress(ByVal strName As String) As String
Dim objSession As MAPI.Session
Dim objField As MAPI.Field
Dim MyAddressList As MAPI.AddressList
Dim MyAddressEntries As MAPI.AddressEntries
Dim MyEntry As MAPI.AddressEntry
Dim SomeEntry As MAPI.AddressEntry
Dim MyRecipient As MAPI.Recipient
Dim v As Variant
Dim strReturnValue As String

'Initialize Local Variables
strReturnValue = "No Address Found" 'Retrun Value if not found

' Create Session object and Logon.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon (g_strMAPILogOn)

'Create the Address list from the Global Address List
Set MyAddressList = objSession.AddressLists(g_strAddressList)
If MyAddressList Is Nothing Then
MsgBox g_strAddressList & " Unavailable!", vbCritical, "Critical
Error"
Exit Function
End If

'Initialize MyAddressEntires with the entries in the Address List
Set MyAddressEntries = MyAddressList.AddressEntries

'Traverse through the entries searching for a match
For Each SomeEntry In MyAddressEntries
Set MyEntry = SomeEntry
If Trim(UCase(strName)) = Trim(UCase(MyEntry.Name)) Then
Set objField = MyEntry.Fields(CdoPR_EMS_AB_PROXY_ADDRESSES)

' PR_EMS_AB_PROXY_ADDRESSES is a multivalued property
(PT_MV_TSTRING).
' Therefore, you need to extract the individual members.
For Each v In objField.Value
If InStr(1, UCase(v), g_strEMailAddressIdentifier) Then
strReturnValue = Mid(v, 6, 256)
Exit For
End If
Next 'Next Field Value
Exit For
End If
Next 'Next Address Entry

'Return Function Value
GetEMailAddress = strReturnValue

'Housekeeping
Set objField = Nothing
Set MyAddressList = Nothing
Set MyAddressEntries = Nothing
Set MyEntry = Nothing
Set MyRecipient = Nothing
objSession.Logoff
Set objSession = Nothing

End Function

You need to reference the Microsoft CDO 1.2? (Part of the install for
outlook) library to use this code. It takes a person's name and returns their
E-mail address from the global address list...
--
HTH...

Jim Thomlinson

"Ken Valenti" wrote:

I have a macro that uses "SendMail" to send a workbook to a list of users.

If there is an invalid email address, it will not send the email at all.

Is there a way that I can verify the email addresses before the workbook is
sent?

Thanks in advance,