Office 365: Export Distribution List Members to CSV using PowerShell

Requirement: Export Office 365 distribution list members to CSV.

How to Export Distribution Group Members to CSV in Office 365?

Office 365 provides a range of tools and features to help organizations manage their email communication and collaboration needs. Distribution Groups, also known as Distribution Lists, are ideal for sending emails to multiple recipients without adding their email addresses individually every time. Typically, it is used to broadcast information to a group of employees, like employees in the sales department or all employees at your company.

As an administrator, you may need to export the members of a distribution group to a CSV file for various purposes, such as reporting, backup, restore, or auditing. Although you can expand any distribution list to get its members in Outlook, That may not give you the members list in the desired format. In this article, we will discuss how to export the members of an Office 365 distribution group to a CSV file using PowerShell.

Export Distribution Groups from Microsoft 365 Admin Center

You can export distribution group details to CSV in Office 365 by following these steps:

1. Log in to the Office 365 Admin Center using your administrator credentials.
2. Navigate to Teams & Groups >> Active teams & groups.
3. Click on the “Distribution list” tab.
4. From the Toolbar, click on “Export groups in this list”.

   

The exports a CSV file with distribution group attributes like Group ID, Name, Alias, Group Primary SMTP Address, Group Alias, Members count, allowed senders, etc. to and will be saved in the Downloads folder, containing all the details of the distribution groups. You can also use the Exchange Admin Center to do the same.

But wait a minute! Where is the member information in the exported file? Well, the Admin Center export doesn’t provide members of each group, But it just gives the group information.

How do you export members of a distribution list using Outlook?

You can export members of a distribution list with the help of Outlook and Microsoft Excel. Here is how:

1. Open Outlook and start a new message.
2. In the “To” field, enter the name of the distribution list you want to export >> Click on the “Check Names” button to verify the name of the distribution list.
3. Click on the “Plus” button to expand the distribution list >> Copy all the users in the “To” list to the clipboard.
4. Now, Open Microsoft Excel. Click on the Paste button Dropdown >> Choose “Use text import Wizard…”
5. Select the Delimited option >> Click on “Next” >> Set the Delimiter as “Semicolon” and finish the wizard. This will paste each member’s name and email in each column.
6. Select all the columns and copy the data >> Open a new Sheet >> Click on the “Paste” button dropdown >> Choose “Paste Special”.
7. In the Paste Special dialog box, select the “Transpose” checkbox and click on “OK”.
8. That’s all! Now, You can save this Excel as CSV through File >> Save as >> select CSV in Save as the File type.

PowerShell to Export distribution list members to CSV

Let’s see how to export distribution group members to CSV using PowerShell in Office 365. This PowerShell script Connects to Exchange Online, retrieves the distribution group members, selects their display names and primary SMTP addresses, Recipient Type, and exports them to a CSV file in the specified path. Keep in mind that you will need to have the Exchange Online PowerShell module installed and have permission to access Exchange Online in order to use the Get-DistributionGroupMember cmdlet. For more information on working with Exchange Online using PowerShell, refer to the post: How to Connect to Exchange Online using PowerShell?

Run the following command to export distribution group members:

#Parameters
$DistributionList = "SalesManagers@Crescent.com" # Group-Name or Group Email
$CSVFilePath = "C:\Temp\DL-Members.csv"

Try {
    #Connect to Exchange Online
    Connect-ExchangeOnline -ShowBanner:$False

    #Get Distribution List Members and Exports to CSV
    Get-DistributionGroupMember -Identity $DistributionList -ResultSize Unlimited | Select Name, PrimarySMTPAddress, RecipientType | Export-Csv $CSVFilePath -NoTypeInformation
}
Catch {
    write-host -f Red "Error:" $_.Exception.Message
}

This will retrieve the distribution group members with the name “SalesManagers@Crescent.com” and export the results to a CSV file called “DL-Members.csv” in the C:\temp directory. The -NoTypeInformation parameter in the Export-CSV cmdlet excludes the type information from the CSV file.

Export All Distribution Lists and Members using PowerShell

To export all distribution groups and their members to a CSV file, use the following PowerShell script:

#Parameters
$CSVFilePath = "C:\Temp\DL-Members.csv"

Try {
    #Connect to Exchange Online
    Connect-ExchangeOnline -ShowBanner:$False

    #Get all Distribution Lists
    $Result=@()    
    $DistributionGroups = Get-DistributionGroup -ResultSize Unlimited
    $GroupsCount = $DistributionGroups.Count
    $Counter = 1
    $DistributionGroups | ForEach-Object {
        Write-Progress -Activity "Processing Distribution List: $($_.DisplayName)" -Status "$Counter out of $GroupsCount completed" -PercentComplete (($Counter/$GroupsCount)*100)
        $Group = $_
        Get-DistributionGroupMember -Identity $Group.Name -ResultSize Unlimited | ForEach-Object {
            $member = $_
            $Result += New-Object PSObject -property @{
            GroupName = $Group.Name
            GroupEmail = $Group.PrimarySmtpAddress
            Member = $Member.Name
            EmailAddress = $Member.PrimarySMTPAddress
            RecipientType= $Member.RecipientType
            }
        }
    $Counter++
    }
    #Get Distribution List Members and Exports to CSV
    $Result | Export-CSV $CSVFilePath -NoTypeInformation -Encoding UTF8
}
Catch {
    write-host -f Red "Error:" $_.Exception.Message
}

Finally, you can verify that the distribution group members have been successfully exported to the CSV file. Open the exported CSV file using Microsoft Excel or any other spreadsheet program. The file should contain columns such as Distribution Group Name, Group Email, Member Name, Email, etc. Optionally, you can include other properties like group type, group owner, etc.

Export Distribution Group Owners to CSV using PowerShell

How about exporting owners of a Distribution list to a CSV file?

#Parameters
$DistributionList = "Marketing@Crescent.com" # Group-Name or Group Email
$CSVFilePath = "C:\Temp\DL-Owners.csv"

Try {
    #Connect to Exchange Online
    #Connect-ExchangeOnline -ShowBanner:$False

    #Get Distribution List Owners and Exports to CSV
    $GroupAdmins = Get-DistributionGroup $DistributionList | Select -ExpandProperty ManagedBy

    $GroupOwners = @()
    ForEach ($Admin in $GroupAdmins)
    {
        $MailBox = Get-Mailbox -Identity $Admin
        $GroupOwners += $MailBox | Select Name, DisplayName, PrimarySmtpAddress
     }

    #Export to CSV
    $GroupOwners
    $GroupOwners | Export-CSV $CSVFilePath -NoTypeInformation
}
Catch {
    write-host -f Red "Error:" $_.Exception.Message
}

Conclusion

In summary, this article explained how to export distribution group members to CSV using PowerShell cmdlets in Office 365. PowerShell provides a powerful tool for automating administrative tasks in Office 365, and exporting distribution group members to CSV is just one of the many tasks you can automate using PowerShell. Follow these steps to export distribution group members to a CSV file for reporting or auditing purposes.