Checking Outlook Email Address

[Trefor]

I have written some VBA that sends a bunch of email to internal email
address. I am using a table that contains their name not their email address
and sometimes it will resolve to their correct email address, sometimes it
won't. Is there a way to programmatically check to see if the email has
resolved correctly? Currently I am using .Display and then manually sending
if they are ok, or correcting if they are wrong, ideally I would like to
..Send them all and perhaps hold the ones that don’t resolve.

 

[JP]

The Resolved Property of the Recipient Object returns True/False
depending on whether the recipient name has been resolved. Also the
Resolve Method does the same thing. You can loop through the
Recipients Collection and run the Resolve method on each one. If
False, you can remove them from the Recipient list, build a string
showing which recipients were removed, etc.

--JP

 

[Trefor]

JP,

Sounds good. Where I can get more info on this? I tried help on a few key
words on your post, but could not find out any more details. Do you have a
reference or sample code?

 

[JP]

I guess I should have mentioned that if you are only having trouble
with specific email addresses, just convert them to (e-mail address removed)
instead of trying to use the Addressbook name. This happens to me when
someone in my Addressbook has a fax number and email address, Outlook
doesn't know which one to use, so the email cannot be sent.

If the display name is "John Smith" and the email address is
"(e-mail address removed)", just replace the name with the email address
and it should resolve every time.

If that's not possible, there is some sample code in the Outlook VBIDE
if you type "ResolveAll" and press F1. I modified it slightly to work
in Excel. Keep in mind that those code will trigger the Outlook
security prompt, since you are accessing the Recipients collection.

Sub CheckRecipients()
Dim myOlApp As Object
Dim MyItem As Object
Dim myRecipients As Object
Dim myRecipient As Object
Dim ThisRecip As Object
Dim i As Long

Set myOlApp = CreateObject("Outlook.Application")
Set MyItem = myOlApp.CreateItem(0)
'MyItem.Display
Set myRecipients = MyItem.Recipients

myRecipients.Add ("Aaron Con")
myRecipients.Add ("Nate Sun")
myRecipients.Add ("Dan Wilson")

If Not myRecipients.ResolveAll Then
For i = myRecipients.Count To 1 Step -1
If Not myRecipients.Item(i).Resolved Then
myRecipients.Remove (i)
End If
Next i
End If
End Sub


This code checks if all the names are resolved, and if not, it steps
through the Recipients collection and removes the ones that aren't
resolved. If you wanted to do something else with the names (i.e.
build a string of names of people who weren't resolved), write back
and we can work on that.

--JP

 

[Trefor]

JP,

Yes I understand if I construct the full email address I won't need to
resolve them. Problem is I don't have the full email address and for some
reason our names are full names including the middle name and to make it
worse shortened name so:

"Jones, Rodney John"

Might be:

"Jones, Rod" as a resolved name

or (e-mail address removed)

I have tried taking the left part of the name up to and including the first
3 characters of the first name and this fixes the middle name issue and most
short name. Problem is we are a big company and chances are there is more
than one entry that now matches my choice.

All I want to do at this stage is check if it resolves, if it does I will
..Send the message and if it doesn't I will just .Display it and someone can
manually check the few that do not go through.

Below is my code can you suggest what I can do to check the names?

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = ETo
.CC = Ecc
.BCC = EBcc
.Subject = ESubject
.Body = Emsg

If EDisplayorSend = "Display" Then .Display

If EDisplayorSend = "Send" Then

If names.ResolveAll then <<< something here would work for me????
.Send
Else
.Display
End if

End With

 

[JP]

In your code, you have to set an object reference to the Recipients
collection, because ResolveAll is a method of the Recipients
collection. So your code would be

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim myRecipients As Outlook.Recipients

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.To = ETo
.CC = Ecc
.BCC = EBcc
.Subject = ESubject
.Body = Emsg

If EDisplayorSend = "Display" Then
.Display
Exit Sub

If EDisplayorSend = "Send" Then
Set myRecipients = .Recipients
If Not myRecipients.ResolveAll Then
.Display
Else
.Send
End If
End If

End With

Set myRecipients = Nothing
Set OutMail = Nothing
Set OutApp = Nothing



Just curious, why not try to change the inputs -- can you get the list
of email addresses instead of the names?

Is there any other way you can get a list of the email addresses?
Maybe you can export the contact information (name and email address)
from Outlook, then use VLOOKUP or a fuzzy match to match them to the
names from your list.

Where does the table come from that the names are written so
differently than what they are in Outlook?

--JP

 

[Trefor]

JP,

Many thanks for you help, your comments prompted to look in the Outlook
forum and I found a few hints and just tested by revised code (which is
essentially what you said below).

If EDisplayorSend = "Send" Then
If .Recipients.ResolveAll Then
.Send
Else
.Display
End If
Else
.Display
End If

 

[JP]

Glad to hear it!

--JP