Export members of distribution list and its nested groups

PowerShell Help
1

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 :slight_smile:

I am connected to Exchange online while executing this script.

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

$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:

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
2

Vlan1,
Welcome to the forum. :wave:t4:

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

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:

3

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
ps1043×338 108 KB

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.

4

If it’s something you need for your work you should talk about with the responsible departement. :man_shrugging:t4:

Yes. That’s the idea. :+1:t4:

5

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.

6

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

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

vs

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

It appears you chopped a bit off in the middle.

1 Like
7

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. :wink: