# Export members of distribution list and its nested groups

**URL:** <https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631>\
**Category:** PowerShell Help\
**Created:** [February 13, 2023, 5:11am UTC](https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631 "2023-02-13T05:11:08Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vlan1](https://avatars.discourse-cdn.com/v4/letter/v/f4b2a3/32.png) [@Vlan1](https://forums.powershell.org/u/Vlan1)\
**Post date:** [February 13, 2023, 5:11am UTC](https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631/1 "2023-02-13T05:11:08Z")

</div>

Hello, i am learning powershell. I am trying to create a powershell script that can **export the members of an online DL to an excel file**. It should **also check for nested groups** , and if there are, then it should list the members of that nested group in a new sheet named as that group. If it can also have the header as the nested group name, it will be good but is not necessary.

Got a draft but its not working, obviously my reason for being here 🙂

I am connected to Exchange online while executing this script.

can the pros please take a look and help me out?

```auto
$OutputFile = "DL-Members_$(Get-Date -Format ddMMMyyyy_hhmmss).xlsx"
$GroupName = "Test-DistributionList@contoso.com"

$Members = Get-DistributionGroupMember -Identity $GroupName -RecipientTypeDetails UserMailbox, MailUniversalDistributionGroup, DynamicDistributionGroup, SecurityDistributionGroup

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Add()
$Sheet = $Workbook.Sheets.Item(1)
$Sheet.Name = "Distribution Group Members"
$Sheet.Cells.Item(1,1) = "Display Name"
$Sheet.Cells.Item(1,2) = "Primary Smtp Address"
$Sheet.Cells.Item(1,3) = "Recipient Type"

$Row = 2
foreach ($Member in $Members)
{
    $Sheet.Cells.Item($Row,1) = $Member.DisplayName
    $Sheet.Cells.Item($Row,2) = $Member.PrimarySmtpAddress
    $Sheet.Cells.Item($Row,3) = $Member.RecipientTypeDetails
    if ($Member.RecipientTypeDetails -eq "MailUniversalDistributionGroup" -or $Member.RecipientTypeDetails -eq "DynamicDistributionGroup" -or $Member.RecipientTypeDetails -eq "SecurityDistributionGroup")
    {
        $NestedMembers = Get-DistributionGroupMember -Identity $Member.PrimarySmtpAddress,RecipientTypeDetails UserMailbox, MailUniversalDistributionGroup, DynamicDistributionGroup, SecurityDistributionGroup
        $NestedSheet = $Workbook.Sheets.Add()
        $NestedSheet.Name = $Member.DisplayName
        $NestedSheet.Cells.Item(1,1) = "Display Name"
        $NestedSheet.Cells.Item(1,2) = "Primary Smtp Address"
        $NestedSheet.Cells.Item(1,3) = "Recipient Type"
        $NestedRow = 2
        foreach ($NestedMember in $NestedMembers)
        {
            $NestedSheet.Cells.Item($NestedRow,1) = $NestedMember.DisplayName
            $NestedSheet.Cells.Item($NestedRow,2) = $NestedMember.PrimarySmtpAddress
            $NestedSheet.Cells.Item($NestedRow,3) = $NestedMember.RecipientTypeDetails
            $NestedRow++
        }
    }
    $Row++
}

$Workbook.SaveAs($OutputFile)
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null

Write-Host "Members of distribution group '$GroupName' have been exported to $OutputFile" -foregroundcolor Green

```

The error I get is:

```auto
Get-DistributionGroupMember : A positional parameter cannot be found that accepts argument
 'System.Object[]'. At C:\Scripts\TBT - 10feb - export_dl_&_nested_members-v2.ps1:24 char:26 + ...
 edMembers = Get-DistributionGroupMember -Identity $Member.PrimarySmtp ... + 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : 
InvalidArgument: (:) [Get-DistributionGroupMember], ParameterBindingException + 
FullyQualifiedErrorId : PositionalParameterNotFound,Get-DistributionGroupMember

```

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [February 13, 2023, 5:48am UTC](https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631/2 "2023-02-13T05:48:33Z")

</div>

Vlan1,  
Welcome to the forum. 👋🏽

I’d recommend to use the great module from Doug Finke

> **[ImportExcel 7.8.4](https://www.powershellgallery.com/packages/ImportExcel/7.8.4)**
>
> PowerShell module to import/export Excel spreadsheets, without Excel.
> Check out the How To Videos https://www.youtube.com/watch?v=U3Ne\_yX4tYo&list=PL5uoqS92stXioZw-u-ze\_NtvSo0k0K0kq

instead of the Excel com object. That could make your life muich easier. This way you don’t even need an installed Excel.

Here you can learn how to use from Doug itself:

[![](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/6/6a8e01b7b7bf1d7ebad8f63c436bb8a64cf467ce.jpeg "ImportExcel PowerShell + Excel = Better Together! with Doug Finke") ](https://www.youtube.com/watch?v=0bR-8b6OMTk)

---

<div class="post-metadata">

**Author:** ![Vlan1](https://avatars.discourse-cdn.com/v4/letter/v/f4b2a3/32.png) [@Vlan1](https://forums.powershell.org/u/Vlan1)\
**Post date:** [February 13, 2023, 3:38pm UTC](https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631/3 "2023-02-13T15:38:47Z")

</div>

I tried to install it but powershell says it needs ‘Nuget’ to proceed and I can’t install it. most likely downloads are blocked as per organization’s policy.

 ![ps](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/2X/6/6be4701b975aa7dbb591b6a112a7e42dc0677bd3.png)

Also I understand that if it would have been installed, the excel com object would be replaced by the commands of this excel module. i.e. the worksheet and sheets will be created with the new commands, but the logic of powershell checking the DL and then identifying nested groups to make new sheets with the names of those groups remain the same.

can someone validate if that is correct? if not, how can the code be modified to make the script work? it’d be a great help.

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [February 13, 2023, 3:46pm UTC](https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631/4 "2023-02-13T15:46:42Z")

</div>

> [@Vlan1](#):
>
> I tried to install it but powershell says it needs ‘Nuget’ to proceed and I can’t install it. most likely downloads are blocked as per organization’s policy.

If it’s something you need for your work you should talk about with the responsible departement. 🤷🏽‍♂️

> [@Vlan1](#):
>
> Also I understand that if it would have been installed, the excel com object would be replaced by the commands of this excel module. i.e. the worksheet and sheets will be created with the new commands, but the logic of powershell checking the DL and then identifying nested groups to make new sheets with the names of those groups remain the same.

Yes. That’s the idea. 👍🏽

---

<div class="post-metadata">

**Author:** ![Vlan1](https://avatars.discourse-cdn.com/v4/letter/v/f4b2a3/32.png) [@Vlan1](https://forums.powershell.org/u/Vlan1)\
**Post date:** [February 13, 2023, 4:54pm UTC](https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631/5 "2023-02-13T16:54:37Z")

</div>

Sorry, may be i was not clear. I meant to validate the code. Talking to someone to allow the package installation is going to take time as its a big organization and its not as simple as talking to someone at their desk, even for a temporary access.

Is there no way to get help on the code that I already have? without the excel module.

---

<div class="post-metadata">

**Author:** ![krzydoug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/krzydoug/32/1008_2.png) [@krzydoug](https://forums.powershell.org/u/krzydoug)\
**Post date:** [February 13, 2023, 5:17pm UTC](https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631/6 "2023-02-13T17:17:23Z")

</div>

The error has nothing to do with excel. It’s clearly stated it is with Get-DistributionGroupMember.

Compare your initial Get-DistributionGroupMember command to the one in your loop

```auto
$Members = Get-DistributionGroupMember -Identity $GroupName -RecipientTypeDetails UserMailbox, MailUniversalDistributionGroup, DynamicDistributionGroup, SecurityDistributionGroup

```

vs

```auto
$NestedMembers = Get-DistributionGroupMember -Identity $Member.PrimarySmtpAddress,RecipientTypeDetails UserMailbox, MailUniversalDistributionGroup, DynamicDistributionGroup, SecurityDistributionGroup

```

It appears you chopped a bit off in the middle.

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [February 13, 2023, 5:52pm UTC](https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631/7 "2023-02-13T17:52:22Z")

</div>

Besides the very helpful tip from Doug and since you’re a PowerShell beginner I’d recommend to start with code you understand or at least reduce the complexity of the code you’re dealing with to a level you’re comfortable with.

You may strip all the Excel stuff from you code and try to export the parts you initially wanted to have in different sheets to individual CSV files. 😉

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:15pm UTC](https://forums.powershell.org/t/export-members-of-distribution-list-and-its-nested-groups/21631/8 "2024-05-16T20:15:10Z")

</div>