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.
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
- Include
- the group owner
- the members
- any security \ restrictions on who can email the list
Note: If your unluckily enough to be running an old version of Excel, it possible to do the same thing in Power BI Desktop, which is a free download.
Below is the M code to do this:
For more help on how to enter the M code into Excel see my previous post.
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.