This morning I woke up to a query whether we could do a bulk addition of users (with email addresses) to a distribution list. The team got a list of users to add and the name of the distribution group as part of the request. The administrators would then copy-paste the email column to a new CSV file, place the file on the desktop and run a one-liner.

Import-Csv "C:\Users\USER88398\Desktop\book1.csv" | ForEach-Object { Add-DistributionGroupMember "DL-DistributionGroup070818" -Member $_.Email }

Sure, basic PowerShell, but does the job of adding users to a distribution list from a CSV file—something most administrators are comfortable with. Provided everything works fine.

The issue with this was, this environment has more than one domain, and the team would get errors when adding emails. Each CSV file contained thousands of entries. This complicated the issue; the team had to extract a list of email addresses that did get added, and compare that list with the master list they received, perform a VLOOKUP and find out the missing emails. The team would then perhaps copy the missed addresses and add them to the DL using the Exchange GUI.

Is there an easier way to find the email addresses that did not get added? There sure is. Let us take a look.

Separate problematic emails

Say we want to perform a bulk addition of users to a distribution list using an input file, and we want to separate the email addresses that did not get added. This is a simple case of using try – catch. The failure point here is the Add-DistributionGroupMember "DL-DistributionGroup070818" -Member $_.Email part.

Error messages in PowerShell are rather long, and let’s face it: most Level 1 administrators don’t read the text. The team did not seem concerned about what the errors were, merely that there were errors, and they cared about getting the failed email addresses. Let us enclose the Add-DistributionGroupMember part in a try – catch block (and replace errors with warnings, because errors contain a lot of information that we do not care about in this case):

try {
  Add-DistributionGroupMember "DL-DistributionGroup070818" -Member $PSItem.Email -ErrorAction Stop
}
catch {
  Write-Warning "$PSItem"
}

I replace $_ with $PSItem to make it more readable (and I find the latter easier to type). Either would work.

Redirect to file

If you combined this with what you had, you would get your basic script for bulk addition with error handling.

Import-Csv "C:\Users\USER88398\Desktop\book1.csv" | ForEach-Object {
  try {
    Add-DistributionGroupMember "DL-DistributionGroup070818" -Member $PSItem.Email -ErrorAction Stop
  }
  catch {
    Write-Warning "$PSItem.Email"
  }
}

This would list out the failed email addresses in amber on the console. We can further improve this:

# Create an empty Errors.txt before you proceed

Import-Csv "$env:USERPROFILE\Desktop\book1.csv" | ForEach-Object { try { Add-DistributionGroupMember "DL-DistributionGroup070818" -Member $PSItem.Email -ErrorAction Stop } catch { Write-Warning "$PSItem.Email" 3>> "$env:USERPROFILE\Desktop\Errors.txt" } }

I changed the path to be less user-specific and removed all the line feeds to make it a single-liner. Also, I have now redirected the error text to a separate file (3>> "$env:USERPROFILE\Desktop\Errors.txt). (Read my post on [Understanding Streams in PowerShell][understanding-streams-in-powershell] to know more.)

This is a quick-and-dirty solution to give you a file called Errors.txt containing the email addresses that did not get added to the group. But this solution is far from, well, being a solution. This approach is error-prone. But pretty much solved the team’s current need.

As a usable script

If I were to improve this, I would write a function to perform this action (also available in my GitHub repository):

#Requires -Version 5

function main {
    Add-BulkUsersToDl -DistributionGroupName 'My-Cool-Dl' -CsvPath '\\path\to\file.csv' 6> '\\path\to\error.txt'
}

function Add-BulkUsersToDl {
    param (
        # The name of the distribution group
        [Parameter(Mandatory = $true, Position = 0)]
        [string]
        $DistributionGroupName,

        # Path to the CSV file
        [Parameter(Mandatory = $true, Position = 1)]
        [string]
        $CsvPath
    )

    begin {
        try {
            $Members = (Import-Csv $CsvPath -ErrorAction Stop).Email | Sort-Object -Unique
        }
        catch {
            Write-Error "Could not import the CSV; please check if the CSV exists at the path, check it has 'Email' as the email column header, and try again."
        }

        try {
            Get-DistributionGroup $DistributionGroupName -ErrorAction Stop | Out-Null
        }
        catch {
            Write-Error "$DistributionGroupName invalid"
            break
        }
    }

    process {
        foreach ($Member in $Members) {
            try {
                Add-DistributionGroupMember $DistributionGroupName -Member $Member -ErrorAction Stop
            }
            catch {
                Write-Information $Member
            }
        }
    }
}

main

You would notice an unfamiliar-to-most cmdlet, Write-Information and a 6> redirect. This is a new stream introduced in PowerShell 5.0. My post on Streams talks about this. The reason I do this is to separate errors from the emails that fail to add for one reason or another. If you care about what specific errors you want to capture, you could use conditional catch statements.

If you have an older version of PowerShell (call $PsVersionTable to check), you may use another stream. If you use Verbose, remember to add -Verbose and change the stream number to 4 like so:

Add-BulkUsersToDl -DistributionGroupName 'My-Cool-Dl' -CsvPath '\\path\to\file.csv' -Verbose 4> '\\path\to\error.txt'

Also, coming to the process, the rest is still manual. Essentially, the team gets a list of users with their SAM account names, email addresses, names and what not from Human Resources. The team identifies one of the sources of error to be the email address not matching the one in the user’s Exchange object. Since we have the SAM account name, we can reduce this sort of error. How?

Something for a later post.