B
Bernie
This post has two purposes.
First, a few years from now I am probably going to have write some code
like this or read my current code and will have forgotten what I found
out. When I search on this topic maybe this post will show up and save
me the hours of work I did to finally get this done. [Note to me in
the future: How are you doing? Did you manage to keep the weight off?
You really did not think you would have to do this again did you?]
Second, my mother taught me to share. So thanks to many folks I will
never meet, I was able to get information to make this program work as
I wanted. So in case someone else is going down this path, hopefully
this will make their job easier.
The project that started all this was quite simple. A Microsoft Excel
spreadsheet and a Visual Basic for Application (VBA) program, to help
with managing our Microsoft Outlook Contact folders.
I will tell you the overview of the program but will limited the
comments to the core issue. Very simply, the program allows you to
select a contact folder, it runs through all the contacts, reporting
the name information in the spreadsheet along with the "categories"
for that contact. During this process the program creates a
"Distribution List" in that contact folder for each of the
different "categories" it finds and adds the contact to it.
The net outcome in Outlook is to have a distribution list for each
category containing the contacts that are a member of that category.
So there are lots of places that give you help and advice on how to add
a person to a distribution list. But if you follow most of that advice
you will find you indeed have a distribution list but if you click on
the contact in the list you end up with what I will call an "email
address" and not a "contact". This does not produce what we
wanted.
The code that is given as a starting sample looks something like
this...
Sub AddNewMembers()
Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myDistList As Outlook.DistListItem
Dim myTempItem As Outlook.MailItem
Dim myRecipients As Outlook.Recipients
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myDistList = myOlApp.CreateItem(olDistributionListItem)
Set myTempItem = myOlApp.CreateItem(olMailItem)
Set myRecipients = myTempItem.Recipients
myDistList.DLName = _
InputBox("Enter the name of the new distribution list")
myRecipients.Add myNameSpace.CurrentUser.Name
myRecipients.Add "Dan Wilson"
myRecipients.ResolveAll
myDistList.AddMembers myRecipients
myDistList.Save
myDistList.Display
End Sub
What is happening is the "ResolveAll" or the "Resolve" method
most times is going to fail. After some research and thought it makes
sense since most contacts have a) more than one email address b) have a
fax phone number. All of those create an entry in the
"AddressList" and the system is never going to locate a unique
"AddressEntry".
So some research suggests basically make the name used in the "Add"
method be the email address. That is format the name into something
like that described in "RFC # 822 - Standard for the Format of
ARPA Internet Text Messages". The structure like
myRecipients.Add _
myContactItem.FullName & _
"<" & myContactItem.Email2Address & ">"
myRecipients.ResolveAll
OK, now when you look at the distribution list you are getting
somewhere. You will have your email addresses all there. BUT when you
click on them you are going to get an "email address" and not a
"contact". Not so good.
The issue is again this "ResolveAll" or the "Resolve" method.
I don't know the internal details. However, it is clear these
methods first attempt to parse the "name" property as a RFC822
address. If finds it can do that ( i.e. it sees "(e-mail address removed)" or
it sees "Mr. Display Name <[email protected]>" ) it stops any sort of
looking, immediately creates a new "AddressEntry" for that email
address, and proudly announces "resolved". We want to have the
"contact" and not an "email address" in the distribution list.
So if you want just to have email addresses in a distribution list this
method will actually work fine.
Note here: If the "Email[1,2,3]DisplayName" is formatted properly
you can use that instead of generating a RFC822 address. This also
accounts for some folks who report the "...DisplayName" field works
to add an email address to the list; the default Outlook generated
format is that. However, anything can be placed in that field so that
is what leads to "erratic" operation.
The actually resolution for us was to add a step after the
"ResolveAll" method. Simply put, we search the
"AddressEntires" of the "AddressList" associated with the
"contacts folder" that created that "AddressList". Even more
basic, we do our own resolution work using the "AddressEntries".
Once we find the "AddressEntry" for that email address we assign it
to the "Recipent" and when it is all done, we have a perfectly
generated "DistributionList". When we click on any entry it brings
up the proper contact and everyone is happy.
The code we settled on looks like this. (We have to do this for each
non-blank email address in the "ContactItem")..
Dim myAddressEntry as AddressEntry
If myContactItem.Email3AddressType = "SMTP" Then
myRecipients.Add _
myContactItem.FullName &_
"<" & myContactItem.Email3Address & ">"
Set myAddressEntry = _
myRoutineToLookupInAddressList _
(myContactItem.Email3DisplayName)
If Not (myAddress Is Nothing) Then Set _
myRecipients.Item(myRecipients.Count).AddressEntry = _
myAddressEntry
Call myRecipients.Item(myRecipients.Count).resolve
End If
Note: We use the "myRecipents.Count" as index since we are
sequentially adding entries.
Note: The "myRoutineToLookupInAddressList" is coded to search the
address list for that folder and is setup elsewhere. Also since the
"sort" method on "AddressEntries" seems not to work we build a
"dictionary" object with the "name" property of each
"AddressEntry" as the key.
Note: We could use other combinations for the display part of the
generated email address. Like "FileAs" or create our own. But it
seems you are always sure that "FullName" has something in it.
Note: Really nice thing by creating the formatted name to be added, if
for some reason you can not find the email address in the address book,
you will have a fall back position on creating an "email entry".
Well there you are. Perhaps more than I needed to record for the
future but it will remind me of why all that code is in the final
product. In closing there are few items of note on our program that
are subtle ways to break it...
1. When locating the "AddressEntries" for the "contact
folder". It is possible that the name assigned to an
"AddressList" is NOT unique. So the chain goes from
"myContactFolder.AddressBookName" to match a
"myAddressList.Name". We can find no way to insure we get the
right one. For us, we know to insure all address lists have a unique
name. We did add code to check if another list has the same name and
then refuse to run. If you know how to make this connection let me
know.
2. When locating the specific "AddressEntry" in the
"myRoutineToLookupInAddressList" we are using ONLY the
"myAddressEntry.name" as the key. To be technically correct we
could use both the "myAddressEntry.name" and
"myAddress.Address" but even that can be a problem since you can
make Outlook create two contacts with the same email address (you do
get a warning) and the same display name. I have no idea how Outlook
internally can keep that straight. There must be something in Outlook
that is not exposed in VBA to make this always work for them.
So hope these notes help someone (maybe me again) who might be playing
around with distribution lists and is getting near the end of their
rope with all this.
Bernie
First, a few years from now I am probably going to have write some code
like this or read my current code and will have forgotten what I found
out. When I search on this topic maybe this post will show up and save
me the hours of work I did to finally get this done. [Note to me in
the future: How are you doing? Did you manage to keep the weight off?
You really did not think you would have to do this again did you?]
Second, my mother taught me to share. So thanks to many folks I will
never meet, I was able to get information to make this program work as
I wanted. So in case someone else is going down this path, hopefully
this will make their job easier.
The project that started all this was quite simple. A Microsoft Excel
spreadsheet and a Visual Basic for Application (VBA) program, to help
with managing our Microsoft Outlook Contact folders.
I will tell you the overview of the program but will limited the
comments to the core issue. Very simply, the program allows you to
select a contact folder, it runs through all the contacts, reporting
the name information in the spreadsheet along with the "categories"
for that contact. During this process the program creates a
"Distribution List" in that contact folder for each of the
different "categories" it finds and adds the contact to it.
The net outcome in Outlook is to have a distribution list for each
category containing the contacts that are a member of that category.
So there are lots of places that give you help and advice on how to add
a person to a distribution list. But if you follow most of that advice
you will find you indeed have a distribution list but if you click on
the contact in the list you end up with what I will call an "email
address" and not a "contact". This does not produce what we
wanted.
The code that is given as a starting sample looks something like
this...
Sub AddNewMembers()
Dim myOlApp As New Outlook.Application
Dim myNameSpace As Outlook.NameSpace
Dim myDistList As Outlook.DistListItem
Dim myTempItem As Outlook.MailItem
Dim myRecipients As Outlook.Recipients
Set myNameSpace = myOlApp.GetNamespace("MAPI")
Set myDistList = myOlApp.CreateItem(olDistributionListItem)
Set myTempItem = myOlApp.CreateItem(olMailItem)
Set myRecipients = myTempItem.Recipients
myDistList.DLName = _
InputBox("Enter the name of the new distribution list")
myRecipients.Add myNameSpace.CurrentUser.Name
myRecipients.Add "Dan Wilson"
myRecipients.ResolveAll
myDistList.AddMembers myRecipients
myDistList.Save
myDistList.Display
End Sub
What is happening is the "ResolveAll" or the "Resolve" method
most times is going to fail. After some research and thought it makes
sense since most contacts have a) more than one email address b) have a
fax phone number. All of those create an entry in the
"AddressList" and the system is never going to locate a unique
"AddressEntry".
So some research suggests basically make the name used in the "Add"
method be the email address. That is format the name into something
like that described in "RFC # 822 - Standard for the Format of
ARPA Internet Text Messages". The structure like
myRecipients.Add _
myContactItem.FullName & _
"<" & myContactItem.Email2Address & ">"
myRecipients.ResolveAll
OK, now when you look at the distribution list you are getting
somewhere. You will have your email addresses all there. BUT when you
click on them you are going to get an "email address" and not a
"contact". Not so good.
The issue is again this "ResolveAll" or the "Resolve" method.
I don't know the internal details. However, it is clear these
methods first attempt to parse the "name" property as a RFC822
address. If finds it can do that ( i.e. it sees "(e-mail address removed)" or
it sees "Mr. Display Name <[email protected]>" ) it stops any sort of
looking, immediately creates a new "AddressEntry" for that email
address, and proudly announces "resolved". We want to have the
"contact" and not an "email address" in the distribution list.
So if you want just to have email addresses in a distribution list this
method will actually work fine.
Note here: If the "Email[1,2,3]DisplayName" is formatted properly
you can use that instead of generating a RFC822 address. This also
accounts for some folks who report the "...DisplayName" field works
to add an email address to the list; the default Outlook generated
format is that. However, anything can be placed in that field so that
is what leads to "erratic" operation.
The actually resolution for us was to add a step after the
"ResolveAll" method. Simply put, we search the
"AddressEntires" of the "AddressList" associated with the
"contacts folder" that created that "AddressList". Even more
basic, we do our own resolution work using the "AddressEntries".
Once we find the "AddressEntry" for that email address we assign it
to the "Recipent" and when it is all done, we have a perfectly
generated "DistributionList". When we click on any entry it brings
up the proper contact and everyone is happy.
The code we settled on looks like this. (We have to do this for each
non-blank email address in the "ContactItem")..
Dim myAddressEntry as AddressEntry
If myContactItem.Email3AddressType = "SMTP" Then
myRecipients.Add _
myContactItem.FullName &_
"<" & myContactItem.Email3Address & ">"
Set myAddressEntry = _
myRoutineToLookupInAddressList _
(myContactItem.Email3DisplayName)
If Not (myAddress Is Nothing) Then Set _
myRecipients.Item(myRecipients.Count).AddressEntry = _
myAddressEntry
Call myRecipients.Item(myRecipients.Count).resolve
End If
Note: We use the "myRecipents.Count" as index since we are
sequentially adding entries.
Note: The "myRoutineToLookupInAddressList" is coded to search the
address list for that folder and is setup elsewhere. Also since the
"sort" method on "AddressEntries" seems not to work we build a
"dictionary" object with the "name" property of each
"AddressEntry" as the key.
Note: We could use other combinations for the display part of the
generated email address. Like "FileAs" or create our own. But it
seems you are always sure that "FullName" has something in it.
Note: Really nice thing by creating the formatted name to be added, if
for some reason you can not find the email address in the address book,
you will have a fall back position on creating an "email entry".
Well there you are. Perhaps more than I needed to record for the
future but it will remind me of why all that code is in the final
product. In closing there are few items of note on our program that
are subtle ways to break it...
1. When locating the "AddressEntries" for the "contact
folder". It is possible that the name assigned to an
"AddressList" is NOT unique. So the chain goes from
"myContactFolder.AddressBookName" to match a
"myAddressList.Name". We can find no way to insure we get the
right one. For us, we know to insure all address lists have a unique
name. We did add code to check if another list has the same name and
then refuse to run. If you know how to make this connection let me
know.
2. When locating the specific "AddressEntry" in the
"myRoutineToLookupInAddressList" we are using ONLY the
"myAddressEntry.name" as the key. To be technically correct we
could use both the "myAddressEntry.name" and
"myAddress.Address" but even that can be a problem since you can
make Outlook create two contacts with the same email address (you do
get a warning) and the same display name. I have no idea how Outlook
internally can keep that straight. There must be something in Outlook
that is not exposed in VBA to make this always work for them.
So hope these notes help someone (maybe me again) who might be playing
around with distribution lists and is getting near the end of their
rope with all this.
Bernie