Export Distribution List to Excel from Office 365 Account in 5+ Ways

Approved By Anuraag Singh

Published On July 10th, 2024

Reading Time 6 Minutes Reading

Category Office 365

When ever Microsoft 365 business plan holders want to perform a mass email rollout they first need to export distribution list to excel from the Office 365 account. Having an additional copy of the membership data is useful in compliance, as well as secondary verification.

Moreover, its easier to share the spreadsheet as CSV format is universally readable. Not to mention that it is an essential part during Microsoft Office 365 email export process. So without further ado let’s start with the task from the default location which is none other than the Admin Center.

Use O365 Admin Center and Get the Distribution Group Members

Like many of the Office 365 backup best practices Here too we have to involve the EAC, although in a different capacity. The steps in on themselves are more than enough explanation.

Step 1. Log on to the M365 Exchange admin center.
Step 2. On left Hand Pane find Recipients category then select Groups option.
Step 3. Toggle the Distribution list tab and hit the Export button.
Step 4. Press “Export groups in this list” button to export specific group members.
Step 5. In the popup box you can get a estimated time of completion. Once done hit Continue.
Step 6. You receive the distribution group member export in CSV file.

If admin center doesn’t do it for you then we have a alternative waiting for those scenarios too.

PowerShell Script Based Distribution List Member Extraction

The following .ps1 script is more than enough to get the a list of Users part of a Distribution list.

# Initialize an empty array to store results
$results = @()

# Get all distribution groups
$distributionGroups = Get-DistributionGroup -ResultSize Unlimited

# Get the total number of groups
$totalGroupsCount = $distributionGroups.Count

# Progress counter
$processedCount = 1
# Loop through each distribution group
$distributionGroups | ForEach-Object {
  # Set current group
  $currentGroup = $_

  # Display progress information
  Write-Progress -Activity "Processing $($currentGroup.DisplayName)" -Status "$processedCount out of $totalGroupsCount completed"

  # Get members of the current group
  $groupMembers = Get-DistributionGroupMember -Identity $currentGroup.Name -ResultSize Unlimited

  # Loop through each group member
  $groupMembers | ForEach-Object {

    # Create a new object with member information
    $memberInfo = New-Object PSObject -Property @{
      GroupName = $currentGroup.DisplayName
      Member = $_.Name
      EmailAddress = $_.PrimarySMTPAddress
      RecipientType = $_.RecipientType
    }
    # Add member info to the results array
    $results += $memberInfo
  }
  # Increment processed count
  $processedCount++
}
# Export results to CSV file
$results | Export-Csv -Path "DistributionGroupMembers.csv" -NoTypeInformation

Here is the explanation for the scripts working.

Initialize $results array to store member info.
Use Get-DistributionGroup to keep groups in $distributionGroups.
Counts groups and track progress while looping through each group.
With the Write-Progress user receives real time updates
Apply Get-DistributionGroupMember to retrieve members.
Processes each member and Adds its details to $results.
Continue till all members in every group are stored in $results then push out a CSV file.

Only user with elevated privileges or admins can handle the script. Speed is directly dependent on the environment size. Additionally lack of filters mean admins have to add them from their end. A script free way is to use another microsoft application that also has the word Power in it.

Use Power Automate Flows to Extract Distribution List Members

Microsoft Power Automate is yet another mechanism that both users and admins have access to. You a major plus point of flow creation lies in the ability to prescedule the task, and set it so that it returns the latest Distributed group data after regular intervals. Visit Power Automate portal click on New Flow and follow the series of steps given below.

Step 1. Add a time interval to choose how often the flow runs (daily, weekly, monthly, etc).

Step 2. Create two variables. One to hold group members and other to hold group owners.

Step 3. Use the “List groups” action to pull in all of your group info.

Step 4. Use an “Apply to each” loop to iterate over the list of group members.

Step 5. With the “Append to array” action add every member to the group member array. (Configure the array to accommodate additional information about the member in the array, such as their UPN, user ID, and group ID.)

Step 6. Send HTTP request action to get the group owner request. After that, parse the resultant JSON response and extract the owner information.

Even with a structured format and the ability to share flow across the organization many users abandon the PowerAutomate method. This is because the time it takes to manually create a flow exceeds any benefits from the automation. Moreover if a list has more than 100 members, you have to add partitions other wise the data will truncate. Another less known method involves the use of publicly accessible API let’s see how we can use it to accomplish our task.

Get Distribution Group List via Microsoft Graph Explorer

Go to https://developer.microsoft.com/en-us/graph/graph-explorer
Login with your Microsoft account (Use admin account to Acess organization wide data).
Scroll till you see the Groups tab on the Left side pane.
There, use the GET all groups I belong to (direct or indirect membership) with count query.
From the Result copy the Group ID whose member list you need.
Then GET direct members of a group with count.
You will receive all the members of a distribution list in Office 365 in JSON format.
Copy and Save the text with a .json extension.
Use any appropriate conversion mechanism to change the data in desired format.

You can store this data and put the Office 365 emails in an external hard drive together, for better safe keeping.

However, this method has some limitations as well. Sometimes the Group Membership count omits the members and displays the owners as members.

Distribution Group membership data won’t be visible without a valid Group ID. Moreover, due to the unfamiliar UI and an odd mix of scripts many struggle to get the required data. Dont worry as we have couple of more methods that you can try.

Export Distribution List to Excel From Office 365 with Outlook Web

Users no longer get direct access to Distribution list inside Outlook Web. This happened after microsoft updated the

Open Outlook Web app
Click on the settings cog at the top right corner
Go to General Setting tab
Select Distribution groups.
Click on the “this portal” link.
Inside https://admin.exchange.microsoft.com/
Pick the Group and toggle Members tab.
Select > copy > paste member names into a Excel Workbook.

Shortcut Method to Export Distribution List to Excel Office 365

Visit: https://myaccount.microsoft.com/groups/groups-i-own
Sign in after prompt.
Click on Group > Open Members tab > View the Distribution List

However the absence of a direct export mechanism means admins/account owner has to rely on manual copy pasting to complete the process.

Conclusion

This blog post consisted of a multitude of ways with wich admins can export distribution list to Excel from any Office 365 account linked to their organization. We explained how membership report generation can be done via EAC, PowerShell, Power Automate, Graph API, etc.