Managing Outlook Distribution Lists with Excel.

I have recently started working with a group of 7 people. We do a lot of daily research reports that are sent to various distribution lists using outlook 365 (the desktop version) not the website.

The challenge we are having is that not everyone’s lists are the same, as these 7 people have added new people and POTENTIAL nonpaying clients to their own lists. Over time the 7 sometimes forget to remove the nonpaying client and a 10-day trial turns into a year before its caught.

I know there are many cloud solutions to fixing this, but it all requires the 7 to log into something new, do some new things, and then send out the reports. Silly as this sounds, it won’t work. These 7 are very stuck in their ways and I have been brought in to fix a solution, not offer new products for them to have to pay for/learn to use.

I have talked with the IT department and am currently working on something that would allow the 7 to access new community distribution lists (so they are all using the same names).

Doing my research, I have also found several examples of using excel to maintain distribution lists.

Per my example attached- I would like to have an excel document that will list all of our clients and prospects. Sorting out when someone who is a prospect gets cut off and all the other logistics, I can handle just fine. You will see an Update List button.

What I would LOVE to have this do…… is take the group lists and update the associated distribution lists in Outlook.

Can this be done? I am thinking that what it COULD do is save the distribution lists in a specific location… then I would run another macro from OUTLOOK that would go and get these new files and update the groups?? If this would work, I have no knowledge of with VBA in outlook however.

Anything you guys could do here would be great, you have always been so kind in assisting, I hope this request will be no different.

Friendly bump? Any direction would be helpful.

Does it look like i will need to create macros on both sides? one on excel to create the lists and a second one on the outlook side to grab them?

As a side note, working with the IT department, we have permissionioned the 7 to view MY new distribution groups so it will only me ME that needs to edit the lists.

Hi sungen99,

Per my example attached- I would like to have an excel document that will list all of our clients and prospects. Sorting out when someone who is a prospect gets cut off and all the other logistics, I can handle just fine. You will see an Update List button.

What I would LOVE to have this do…… is take the group lists and update the associated distribution lists in Outlook.

You created an interesting Challenge. First I had to build the tools to add/delete Contacts, Add/Delete Groups and Add/Delete Contacts from Groups. See the attached modified copy of your file as a starting point. Please let me know if you need any changes.

I propose the following which is similar to what you requested. All Activity is from Excel (Outlook must be open).
a. You modify the file and test to make sure Contacts and Groups are correct.
b. You make a copy of the file and send the copy as an Email Attachment to your users.
c. The users Download the file, and run 'Update Lists'.
d. Update Lists automatically sends an email to you (with a cc to the User) showing the status of what was done.
e. The return emails will help you verify when a User has the current list.

For the Benefit of others here are a few non-working code excerpts. Full working code is in the attached file.

To add an Outlook Contact:

Please Login or Register  to view this content.

To delete an Outlook Contact (Contacts may still exist inside Groups after a Contact is deleted):

Please Login or Register  to view this content.

To add a Group:

Please Login or Register  to view this content.

To delete a Group (does not Delete Contacts):

Please Login or Register  to view this content.

To add a Contact to an Existing Group:

Please Login or Register  to view this content.

To Delete a Member from a Group:

Please Login or Register  to view this content.

To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis