Note: If you're copying and pasting from this blog entry, it's worth pasting into notepad so that you can rejoin any lines before pasting into PowerShell. I only give you one command at a time, so it should all be on one line.
The ProcedureStart PowerShell (in Administrator Mode) and connect to Office 365
(and press Y
$UserCredential = Get-Credential
Enter your email address and password.
If you're using 2 Factor Authentication...You'll need to open the Microsoft Exchange Online Powershell Module which should be on your desktop if you've followed the instructions (see this post).
Enter the following command (changing the email address to be your own)
Connect-EXOPSSession -UserPrincipalName email@example.com -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Authentication Basic -AllowRedirection
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
I got a lot error text at this point (WARNING: Proxy creation has been skipped for the following command -- followed by a large list of yellow commands). I think you can get around this by entering a session instead of importing it but my PowerShell experience doesn't really stretch that far -- and the command works in any case, so there's no need. (yet).
Make a note of your group's email address: firstname.lastname@example.org
and the path to a CSV file where you want to save the output. C:\temp\MemberList.csv
You'll want to change both of those bits in the next command.
Get-DistributionGroupMember -Identity "email@example.com" | Export-csv C:\temp\MemberList.csv -NoTypeInformation
Cleaning up afterwards
Exit the session...
and then remove it.
and then exit the PowerShell window.
Looking at the Output
The output of your command will be a CSV file that you can open in Excel. At first glance, it's probably not going to look very nice. It's got columns from A-DH but they're mostly empty. Here's how to clean it up, obviously the columns are subject to change whenever Microsoft feels like it but it's probably worth creating a Macro for.
- Remove Columns A-C
- Keep Column D and E (Identity and Alias)
- Remove Columns F-G
- Keep Column H (City)
- Remove Column I
- Keep Column J-L (Company, Country and Postcode)
- Remove Columns M-AP
- Keep Column AQ (First Name)
- Remove Columns AR-AS
- Keep Column AT (Last Name)
- Remove Columns AU-BC
- Keep Column BD (Phone)
- Remove Columns BE-BF
- Keep Column BG (Email)
- Remove Columns BH-BL
- Keep Column BM (State)
- Remove Column BN
- Keep Column BO (Position Title)
- Remove Columns BP-CX
- Optionally keep Columns CY-CZ (Create and Modify Dates)
- Remove Columns DA-DH
The result should be a usable spreadsheet.