Distribution list are amazing things, they allow us to send mail to our team without having to remember who exactly is in our team, this is especial true in this modern age where we are part of many different teams, sometimes without even realising. These lists can contain hundreds or even thousands of members and the latest NHS IT bug has left IT departments maybe wanting to double-check their distribution lists – hopefully before the security team comes a knocking.
NHS 850k reply-all email fail: State health service blames Accenture for config cockup < by me https://t.co/TZ1AOqsdKC
— Gareth Corfield (@GazTheJourno) November 15, 2016
Luckily, this is really easy in Excel 2016. Built into Excel 2016 is PowerQuery and one of the out-of-box connectors is Active Directory. With a bit of PowerQuery magic, you can easily pull this data out.
So we want to:
- Connect to our Active Directory
- List out all our Groups
- Filter to email-able Groups
- the group owner
- the members
- any security \ restrictions on who can email the list
Below is the M code to do this:
UPDATE: Please note that this only works with Active Directory and doesn’t work with Azure Active Directory. Others have said this is possible via odata or such however I haven’t tried… yet.