Hi,
I have Outlook distribution lists of up to 1,000 members. The membership
changes (to some degree) each month. I have a MS Access database which
compares the list of people who were on the list last month to the list of
people who should be in the distribution list this month. It identifies
people to remove from the distribution list and people to add to the
distribution list. Is there a good way to use MS Access VBA to automatically
make these changes to the distribution lists?
For example, last month Tony (and 145 other people) were part of the
1stComplaintHandler Email list, but have moved to other responsibilities, so
shouldn't recevie any more email for that group. At the same time, Joan (and
43 other people) have joined the group that handles 1st Complaints, so they
should be added to the email group "1stComplaintHandler".
Any help is greatly appreciated (suggestions or even articles to read...)
Thank you
SoCal Rick

Maintaining DLs is a nightmare in the making. I don't recommend using them
at all, except for small, relatively static ones.

You could, in theory, write Access VBA code using the Outlook object model
to work with the properties and methods of the DistListItem object and do
updates. (Whether you'll be interrupted by security prompts depends on your
Outlook version, which you should always mention when posting to an Outlook
forum.) However, better solutions for mass mailings exist, including Word
mail merge directly from your database.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54


"SoCal Rick" wrote in message
...
Hi,
I have Outlook distribution lists of up to 1,000 members. The membership
changes (to some degree) each month. I have a MS Access database which
compares the list of people who were on the list last month to the list of
people who should be in the distribution list this month. It identifies
people to remove from the distribution list and people to add to the
distribution list. Is there a good way to use MS Access VBA to
automatically
make these changes to the distribution lists?
For example, last month Tony (and 145 other people) were part of the
1stComplaintHandler Email list, but have moved to other responsibilities,
so
shouldn't recevie any more email for that group. At the same time, Joan
(and
43 other people) have joined the group that handles 1st Complaints, so
they
should be added to the email group "1stComplaintHandler".
Any help is greatly appreciated (suggestions or even articles to read...)
Thank you
SoCal Rick

Hi Sue,
First, thank you for responding to my question.

The Outlook version we use is Outlook 2003(11.8313.8221) SP3. I do most of
my work in Excel and Access, so I don't have much experience with Outlook.

An option available, but I haven't tried it with this quantity, is to
maintain the mailing lists in the Access database. When the user wants to
send a message to everyone on this list, they could have a prompt for the
message and other critical information, then I could open an instance of
Outlook and fill in the "To" and "CC" based on the database. This would
avoid maintaining the DLs in Outlook, however, it would limit the users of
these special lists to people who have access to the MS Access database.
Since there are only a couple of people, this is doable.

I should explain how this is used. We get a download of temporary
assignments for a few thousand people every month. We classify these people
into groups based on information from the database. Then we compare the
results to the prior month's results to determine changes in assignment. We
print out lists of changes and someone has the task of going into Outlook and
modifying the contacts accordingly.

My thought was there must be a way to simplify this so once the updated
information is available, we could automatically update it. When you
mentioned using mail merge in Word, it drove me to think of sending the email
directly from the Access database. This is something I do for some of our
users now, but the mail lists are not very big (up to 20 receipients).

So your thoughts or directions to look at other sources are extremely
valuable to me and I do appreciate your time and expertise.

Thank you,
Rick