I had a need for a function that checks if a recipient is valid. This could be useful in Excel to check if a name that someone input into a userform could resolve to a valid recipient. Hence the function name "ValidRecipient". 
The code creates a throwaway email and tries to resolve the name. Returns TRUE if the string passed to it resolves to a valid Outlook recipient in your addressbook. It uses a second function to grab a reference to Outlook, so the code is late-bound and can be used in any project. It can be used on the worksheet as a UDF, or (ideally) in your VBA code.
Beware the Outlook (OMG) object model guard, which will be triggered when you access the Recipients collection.
Function ValidRecipient(strRecip As String) As Boolean
Dim olApp As Object
Dim olMsg As Object
Dim olRecips As Object
Dim olCurrentRecip As Object
On Error Resume Next
Set olApp = GetOutlookApp
On Error GoTo 0
If Not olApp Is Nothing Then
Set olMsg = olApp.CreateItem(0)
Set olRecips = olMsg.Recipients
Set olCurrentRecip = olRecips.Add(strRecip)
If olCurrentRecip.Resolve Then
ValidRecipient = True
GoTo ExitProc
End If
' or:
' olMsg.To = strRecip
' If olMsg.Recipients.ResolveAll Then
' ValidRecipient = True
' GoTo ExitProc
' End If
End If
ExitProc:
olMsg.Close (1)
Set olCurrentRecip = Nothing
Set olRecips = Nothing
Set olMsg = Nothing
Set olApp = Nothing
End Function
Function GetOutlookApp() As Object
' returns a reference to Outlook to the calling sub
On Error Resume Next
Set GetOutlookApp = GetObject(, "Outlook.Application")
If GetOutlookApp Is Nothing Then
Set GetOutlookApp = CreateObject("Outlook.Application")
Exit Function
End If
On Error Goto 0
End Function
Usage:
If ValidRecipient("Jimmy Pena") Then
MsgBox "OK!"
End If
or:
=ValidRecipient("Jimmy Pena")
About JP
Follow Me