Office 365 Report Using Excel COM Interface with PowerShell

April 12, 2018 Brad Wyatt Comments 2 comments

Table of Contents

I set out to explore a way to generate a nice, multi-sheet, Office 365 report that I could give to end-user’s or Office Manager’s using PowerShell. Unfortunately, I read about the Excel COM interface before reading about the Excel PowerShell module created by Doug Finke. If you are starting from scratch, I would recommend reading up on the PowerShell Module instead of going the COM route as it’s a easier to use that the Excel COM Interface. Regardless, I finished my PowerShell script and wanted to share it for others to use.

Pre-requisites

Not a lot of pre-reqs for this script and I designed it that way. At first it was reliant on an Excel Workbook that was pre-formatted how I liked, and it would populate the data specifically for that workbook only. However, I decided to have PowerShell format the Excel Workbook from scratch as it ran. So for this script to run you only need 1 thing: (I think it goes without saying, but you will need PowerShell as well as an Office 365 account as well)

Excel

Report Goals

For my report, I wanted to make sure it could do at least the following:

Make a separate WorkSheet for each report
Each WorkSheet will have a nice, clean heading
Report the following:
1. Licensed Mailboxes
  1. Users with licenses
  2. Display Name
  3. Friendly License Name
    1. If no friendly name found, just use the AccountSkuID
  4. Primary E-Mail Addresses
  5. Alias E-Mail Addresses
2. Groups
  1. Group Name
  2. Group Type
    1. Distribution
    2. Office 365 Group
    3. Security Group
    4. etc…
  3. Group E-Mail Address (if its got one!)
3. Shared Mailboxes
  1. Shared Mailbox Name
  2. E-Mail Address
4. Contacts
  1. Name
  2. E-Mail Address
5. Public Folders
  1. Name
  2. If It’s Mail-Enabled
  3. E-Mail Address
6. Domains
  1. Domain Name
  2. If its fully verified
  3. If its default
    1. If it’s default, highlight it
Make the report good looking, and easy to read
Show the data in alphabetical order
Auto-Format Excel rows and columns

Report Formatting

Below you can see some of the different tabs of the report from when I ran it on my test Office 365 tenant.

Public Folder report, shows Name, E-Mail and if it’s Mail-Enabled

Licensed Users, Friendly License name, primary E-mail and all alias addresses

Each report will be contained in it’s own Excel WorkSheet. Cells A1:C1 will contain the report name, and row 2 will contain the report headers. As we see pictured, the header cells and the number cells are formatted with a blue background and a white lettering to let it stand out from the report results. For the Licensed Users report it will take the license SKU (or SKU’s if you have more than 1 license) and convert the SKU to a friendly license name using a Hash Table. If it cannot find a match it will fail back on the AccountSkuID. So if it could not find the Enterprise Plan E1 license in the Hash Table it will list “EXCHANGESTANDARD”.

PowerShell will create the entire Excel Workbook, all report WorkSheets and worksheet formatting from scratch as it goes from report to report. The video below shows me running the script. PowerShell calls the Excel COM object by running:

$excel = New-Object -ComObject Excel.Application

And then it will show the Excel program by running:

$excel.Visible = $true

As data starts filling into the report it will continuously auto-size the rows and columns so all the data fits nicely into each cell. The report data background color will alternate from white to light grey. As it finished up with the report it will automatically switch to the next worksheet. This will help you watch what it’s working on if you choose to have the Excel application open while it runs.

Pictured below we can see it changing to the Licensed Mailbox worksheet. Cell 1,1 becomes the report title where it then will change to font size 26, font changes to Cambria, changing font colors and alignment and then merging cells a1,c1.

Once it’s finished with the report title it will begin with the report headers. The first one it makes is the “#” which will label the rows in the report. The headers will all be formatted the same way.

Report Data

Licensed Mailboxes

The “Licensed Mailboxes” report will show you the following information

User Account Name
Friendly Office 365 license name
Primary E-mail address
All alias E-Mail addresses

The license mailbox report will look up each users license which is stored in a format like “{bwya77:FLOW_FREE, bwya77:EXCHANGESTANDARD}”. It will go through each license and look up the friendly name and display that.

For the users Alias E-Mail Addresses it will display all of them if there are multiple entries and also remove the primary from the object by looking for the value that matches “SMTP:” which is used only for the primary address.

Groups

The “Groups” report will show the following information

Group Name
Group Type
E-Mail Address

The Group report will report on all groups. You can distinguish the groups by their type. In my report I have mostly distribution groups, but I also have one mail-enabled security group.

Shared Mailboxes

The “Shared Mailbox” report will show the following information

Shared Mailbox Name
Primary E-Mail Address

Contacts

The Contacts report will show the following information

Contact Name
Primary E-Mail Address

Public Folders

The “Public Folders” report will show you the following information

Public Folder Name
Primary E-Mail Address
Mail-Enabled

The only report that takes a second to run is Public Folders. This is because it’s running 2 commands in the background. Get-PublicFolder and Get-MailPublicFolder. Get-PublicFolder will list ALL Public Folders, mail-enabled or not. So to list Mail-Enabled Public Folders and regular Public Folders I am comparing both objects and removing the Mail-Enabled Public Folders from the regular Public Folder object.

Domains

The “Domains” report will show you the following information

Domain Name
Default status
Verification status

The Script

The script is listed below and is copy-ready. There are no variables or anything you need to change or tweak (unless you want to tweak the reports look or data). As long as you have met the pre-requisites you can just copy this and run it immediately.

I do plan and learning more into the Excel PowerShell module and hopefully will be able to generate multi-tenant reports, and even charts based on each data set. If you are looking for more great information on the Excel COM interface I suggest taking a taking a look at this write-up.

$Sku = @{
	"O365_BUSINESS_ESSENTIALS"			     = "Office 365 Business Essentials"
	"O365_BUSINESS_PREMIUM"				     = "Office 365 Business Premium"
	"DESKLESSPACK"						     = "Office 365 (Plan K1)"
	"DESKLESSWOFFPACK"					     = "Office 365 (Plan K2)"
	"LITEPACK"							     = "Office 365 (Plan P1)"
	"EXCHANGESTANDARD"					     = "Office 365 Exchange Online Only"
	"STANDARDPACK"						     = "Enterprise Plan E1"
	"STANDARDWOFFPACK"					     = "Office 365 (Plan E2)"
	"ENTERPRISEPACK"						 = "Enterprise Plan E3"
	"ENTERPRISEPACKLRG"					     = "Enterprise Plan E3"
	"ENTERPRISEWITHSCAL"					 = "Enterprise Plan E4"
	"STANDARDPACK_STUDENT"				     = "Office 365 (Plan A1) for Students"
	"STANDARDWOFFPACKPACK_STUDENT"		     = "Office 365 (Plan A2) for Students"
	"ENTERPRISEPACK_STUDENT"				 = "Office 365 (Plan A3) for Students"
	"ENTERPRISEWITHSCAL_STUDENT"			 = "Office 365 (Plan A4) for Students"
	"STANDARDPACK_FACULTY"				     = "Office 365 (Plan A1) for Faculty"
	"STANDARDWOFFPACKPACK_FACULTY"		     = "Office 365 (Plan A2) for Faculty"
	"ENTERPRISEPACK_FACULTY"				 = "Office 365 (Plan A3) for Faculty"
	"ENTERPRISEWITHSCAL_FACULTY"			 = "Office 365 (Plan A4) for Faculty"
	"ENTERPRISEPACK_B_PILOT"				 = "Office 365 (Enterprise Preview)"
	"STANDARD_B_PILOT"					     = "Office 365 (Small Business Preview)"
	"VISIOCLIENT"						     = "Visio Pro Online"
	"POWER_BI_ADDON"						 = "Office 365 Power BI Addon"
	"POWER_BI_INDIVIDUAL_USE"			     = "Power BI Individual User"
	"POWER_BI_STANDALONE"				     = "Power BI Stand Alone"
	"POWER_BI_STANDARD"					     = "Power-BI Standard"
	"PROJECTESSENTIALS"					     = "Project Lite"
	"PROJECTCLIENT"						     = "Project Professional"
	"PROJECTONLINE_PLAN_1"				     = "Project Online"
	"PROJECTONLINE_PLAN_2"				     = "Project Online and PRO"
	"ProjectPremium"						 = "Project Online Premium"
	"ECAL_SERVICES"						     = "ECAL"
	"EMS"								     = "Enterprise Mobility Suite"
	"RIGHTSMANAGEMENT_ADHOC"				 = "Windows Azure Rights Management"
	"MCOMEETADV"							 = "PSTN conferencing"
	"SHAREPOINTSTORAGE"					     = "SharePoint storage"
	"PLANNERSTANDALONE"					     = "Planner Standalone"
	"CRMIUR"								 = "CMRIUR"
	"BI_AZURE_P1"						     = "Power BI Reporting and Analytics"
	"INTUNE_A"							     = "Windows Intune Plan A"
	"PROJECTWORKMANAGEMENT"				     = "Office 365 Planner Preview"
	"ATP_ENTERPRISE"						 = "Exchange Online Advanced Threat Protection"
	"EQUIVIO_ANALYTICS"					     = "Office 365 Advanced eDiscovery"
	"AAD_BASIC"							     = "Azure Active Directory Basic"
	"RMS_S_ENTERPRISE"					     = "Azure Active Directory Rights Management"
	"AAD_PREMIUM"						     = "Azure Active Directory Premium"
	"MFA_PREMIUM"						     = "Azure Multi-Factor Authentication"
	"STANDARDPACK_GOV"					     = "Microsoft Office 365 (Plan G1) for Government"
	"STANDARDWOFFPACK_GOV"				     = "Microsoft Office 365 (Plan G2) for Government"
	"ENTERPRISEPACK_GOV"					 = "Microsoft Office 365 (Plan G3) for Government"
	"ENTERPRISEWITHSCAL_GOV"				 = "Microsoft Office 365 (Plan G4) for Government"
	"DESKLESSPACK_GOV"					     = "Microsoft Office 365 (Plan K1) for Government"
	"ESKLESSWOFFPACK_GOV"				     = "Microsoft Office 365 (Plan K2) for Government"
	"EXCHANGESTANDARD_GOV"				     = "Microsoft Office 365 Exchange Online (Plan 1) only for Government"
	"EXCHANGEENTERPRISE_GOV"				 = "Microsoft Office 365 Exchange Online (Plan 2) only for Government"
	"SHAREPOINTDESKLESS_GOV"				 = "SharePoint Online Kiosk"
	"EXCHANGE_S_DESKLESS_GOV"			     = "Exchange Kiosk"
	"RMS_S_ENTERPRISE_GOV"				     = "Windows Azure Active Directory Rights Management"
	"OFFICESUBSCRIPTION_GOV"				 = "Office ProPlus"
	"MCOSTANDARD_GOV"					     = "Lync Plan 2G"
	"SHAREPOINTWAC_GOV"					     = "Office Online for Government"
	"SHAREPOINTENTERPRISE_GOV"			     = "SharePoint Plan 2G"
	"EXCHANGE_S_ENTERPRISE_GOV"			     = "Exchange Plan 2G"
	"EXCHANGE_S_ARCHIVE_ADDON_GOV"		     = "Exchange Online Archiving"
	"EXCHANGE_S_DESKLESS"				     = "Exchange Online Kiosk"
	"SHAREPOINTDESKLESS"					 = "SharePoint Online Kiosk"
	"SHAREPOINTWAC"						     = "Office Online"
	"YAMMER_ENTERPRISE"					     = "Yammer for the Starship Enterprise"
	"EXCHANGE_L_STANDARD"				     = "Exchange Online (Plan 1)"
	"MCOLITE"							     = "Lync Online (Plan 1)"
	"SHAREPOINTLITE"						 = "SharePoint Online (Plan 1)"
	"OFFICE_PRO_PLUS_SUBSCRIPTION_SMBIZ"	 = "Office ProPlus"
	"EXCHANGE_S_STANDARD_MIDMARKET"		     = "Exchange Online (Plan 1)"
	"MCOSTANDARD_MIDMARKET"				     = "Lync Online (Plan 1)"
	"SHAREPOINTENTERPRISE_MIDMARKET"		 = "SharePoint Online (Plan 1)"
	"OFFICESUBSCRIPTION"					 = "Office ProPlus"
	"YAMMER_MIDSIZE"						 = "Yammer"
	"DYN365_ENTERPRISE_PLAN1"			     = "Dynamics 365 Customer Engagement Plan Enterprise Edition"
	"ENTERPRISEPREMIUM_NOPSTNCONF"		     = "Enterprise E5 (without Audio Conferencing)"
	"ENTERPRISEPREMIUM"					     = "Enterprise E5 (with Audio Conferencing)"
	"MCOSTANDARD"						     = "Skype for Business Online Standalone Plan 2"
	"PROJECT_MADEIRA_PREVIEW_IW_SKU"		 = "Dynamics 365 for Financials for IWs"
	"STANDARDWOFFPACK_IW_STUDENT"		     = "Office 365 Education for Students"
	"STANDARDWOFFPACK_IW_FACULTY"		     = "Office 365 Education for Faculty"
	"EOP_ENTERPRISE_FACULTY"				 = "Exchange Online Protection for Faculty"
	"EXCHANGESTANDARD_STUDENT"			     = "Exchange Online (Plan 1) for Students"
	"OFFICESUBSCRIPTION_STUDENT"			 = "Office ProPlus Student Benefit"
	"STANDARDWOFFPACK_FACULTY"			     = "Office 365 Education E1 for Faculty"
	"STANDARDWOFFPACK_STUDENT"			     = "Microsoft Office 365 (Plan A2) for Students"
	"DYN365_FINANCIALS_BUSINESS_SKU"		 = "Dynamics 365 for Financials Business Edition"
	"DYN365_FINANCIALS_TEAM_MEMBERS_SKU"	 = "Dynamics 365 for Team Members Business Edition"
	"FLOW_FREEs"							 = "Microsoft Flow Free"
	"POWER_BI_PRO"						     = "Power BI Pro"
	"O365_BUSINESS"						     = "Office 365 Business"
	"DYN365_ENTERPRISE_SALES"			     = "Dynamics Office 365 Enterprise Sales"
	"RIGHTSMANAGEMENT"					     = "Rights Management"
	"PROJECTPROFESSIONAL"				     = "Project Professional"
	"VISIOONLINE_PLAN1"					     = "Visio Online Plan 1"
	"EXCHANGEENTERPRISE"					 = "Exchange Online Plan 2"
	"DYN365_ENTERPRISE_P1_IW"			     = "Dynamics 365 P1 Trial for Information Workers"
	"DYN365_ENTERPRISE_TEAM_MEMBERS"		 = "Dynamics 365 For Team Members Enterprise Edition"
	"CRMSTANDARD"						     = "Microsoft Dynamics CRM Online Professional"
	"EXCHANGEARCHIVE_ADDON"				     = "Exchange Online Archiving For Exchange Online"
	"EXCHANGEDESKLESS"					     = "Exchange Online Kiosk"
	"SPZA_IW"							     = "App Connect"
}

$credential = Get-Credential -Message "Please enter your Office 365 credentials"
Import-Module MsOnline
Connect-MsolService -Credential $credential
$exchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://outlook.office365.com/powershell-liveid/" -Credential $credential -Authentication "Basic" -AllowRedirection
Import-PSSession $exchangeSession -AllowClobber


$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true

#Create the workbook
$workbook = $excel.Workbooks.Add()
$InitalWorksheet = $workbook.Worksheets.Item(1)
$InitalWorksheet.Name = "Domains"
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Public Folders"
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Contacts"
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Shared Mailboxes"
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Groups"
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Licensed Mailboxes"



$Sheet_LicensedMailboxes = $Workbook.Worksheets.Item("Licensed Mailboxes")
$Sheet_Group = $Workbook.Worksheets.Item("Groups")
$Sheet_SharedMailboxes = $Workbook.Worksheets.Item("Shared Mailboxes")
$Sheet_Contacts = $Workbook.Worksheets.Item("Contacts")
$Sheet_PublicFolders = $Workbook.Worksheets.Item("Public Folders")
$Sheet_Domains = $Workbook.Worksheets.Item("Domains")



#Licensed Mailbox Worksheet

$Sheet_LicensedMailboxes.Activate()
$Sheet_LicensedMailboxes.ActiveSheet

$row = 1
$Column = 1
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Licensed Users'


$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Size = 26
#$Sheet_LicensedMailboxes.Cells.Item($row,$column).Font.Bold=$True
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Color = 8210719

$range = $Sheet_LicensedMailboxes.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26

$range = $Sheet_LicensedMailboxes.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

#Create a Title for the first worksheet and adjust the font
$row = 2
$Column = 1
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = '#'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1


$row = 2
$Column = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'User Account'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 3
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Office 365 Licenses'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 4
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Primary E-Mail Address'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 5
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Alias E-Mail Addresses'
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_LicensedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1


$Users = Get-Msoluser | Where-Object { $_.IsLicensed -eq $True } | Sort-Object DisplayName
$Sheet_LicensedMailboxes_Y = 3
$RowNumber = 0
$LicenseName = @()
Foreach ($User in $Users)
{
	$Row = $Sheet_LicensedMailboxes_Y++
	$DisplayName = ($User).DisplayName
	$RowNumber++
	$EmailAddresses = @()
	
	$primaryemailaddress = ($User | Select-Object -ExpandProperty proxyaddresses | Where-Object { $_ -cmatch '^SMTP' }).Trim("SMTP:")
	$EmailAddresses = @(($User | Select-Object -ExpandProperty proxyaddresses | Where-Object { $_ -cnotmatch "^SMTP:$primaryemailaddress" }) -replace "smtp:")
	
	
	#Lookup for friendly license name
	$Licenses = (($User).Licenses).AccountSkuID
	If (($Licenses).Count -gt 1)
	{
		Foreach ($License in $Licenses)
		{
			$LicenseItem = $License -split ":" | Select-Object -Last 1
			$TextLic = $Sku.Item("$LicenseItem")
			If (!($TextLic))
			{
				$fallback_Licenses = $LicenseItem
				$LicenseName += $fallback_Licenses
			}
			Else
			{
				$LicenseName += $TextLic
			}
		}
	}
	Else
	{
		$LicenseItem = $Licenses -split ":" | Select-Object -Last 1
		$TextLic = $Sku.Item("$LicenseItem")
		If (!($TextLic))
		{
			$LicenseName = $LicenseItem
		}
		Else
		{
			$LicenseName = $TextLic
		}
	}
	
	$LicenseName = ($LicenseName | Out-String).TrimEnd().Trim()
	$AllEmailAddresses = ($EmailAddresses | Out-String).Trim().TrimEnd()
	
	
	$Sheet_LicensedMailboxes.Cells.Item($Row, 1) = $RowNumber
	$Sheet_LicensedMailboxes.Cells.Item($row, 1).Font.Bold = $True
	$Sheet_LicensedMailboxes.Cells.Item($row, 1).Interior.ColorIndex = 55
	$Sheet_LicensedMailboxes.Cells.Item($row, 1).HorizontalAlignment = -4108
	$Sheet_LicensedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4108
	$Sheet_LicensedMailboxes.Cells.Item($row, 1).Font.Name = "Cambria"
	$Sheet_LicensedMailboxes.Cells.Item($row, 1).Font.ColorIndex = 2
	$Sheet_LicensedMailboxes.Cells.Item($row, 1).Font.ThemeFont = 1
	
	$Even = $RowNumber % 2
	If ($Even -eq 0)
	{
		$Sheet_LicensedMailboxes.Cells.Item($Row, 2) = $DisplayName
		$Sheet_LicensedMailboxes.Cells.Item($row, 2).Interior.ColorIndex = 15
		$Sheet_LicensedMailboxes.Cells.Item($Row, 2).VerticalAlignment = -4108
		$Sheet_LicensedMailboxes.Cells.Item($Row, 3) = $LicenseName
		$Sheet_LicensedMailboxes.Cells.Item($row, 3).Interior.ColorIndex = 15
		$Sheet_LicensedMailboxes.Cells.Item($Row, 4) = $primaryemailaddress
		$Sheet_LicensedMailboxes.Cells.Item($row, 4).Interior.ColorIndex = 15
		$Sheet_LicensedMailboxes.Cells.Item($Row, 5) = $AllEmailAddresses
		$Sheet_LicensedMailboxes.Cells.Item($row, 5).Interior.ColorIndex = 15
		
		$LicenseName = @()
	}
	Else
	{
		
		$Sheet_LicensedMailboxes.Cells.Item($Row, 2) = $DisplayName
		$Sheet_LicensedMailboxes.Cells.Item($Row, 2).VerticalAlignment = -4108
		$Sheet_LicensedMailboxes.Cells.Item($row, 2).Interior.ColorIndex = 2
		$Sheet_LicensedMailboxes.Cells.Item($Row, 3) = $LicenseName
		$Sheet_LicensedMailboxes.Cells.Item($row, 3).Interior.ColorIndex = 2
		$Sheet_LicensedMailboxes.Cells.Item($Row, 4) = $primaryemailaddress
		$Sheet_LicensedMailboxes.Cells.Item($row, 4).Interior.ColorIndex = 2
		$Sheet_LicensedMailboxes.Cells.Item($Row, 5) = $AllEmailAddresses
		$Sheet_LicensedMailboxes.Cells.Item($row, 5).Interior.ColorIndex = 2
		$LicenseName = @()
		
	}
	
	$Sheet_LicensedMailboxes.Columns.AutoFit() | Out-Null
	$Sheet_LicensedMailboxes.Rows.AutoFit() | Out-Null
}

#Groups Worksheet

$Sheet_Group.Activate()
$Sheet_Group.ActiveSheet

#Create a Title for the first worksheet and adjust the font

$row = 1
$Column = 1
$Sheet_Group.Cells.Item($row, $column) = 'Groups'


$Sheet_Group.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Group.Cells.Item($row,$column).Font.Bold=$True
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Group.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $column).Font.Color = 8210719

$range = $Sheet_Group.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26

$range = $Sheet_Group.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

$row = 2
$Column = 1
$Sheet_Group.Cells.Item($row, $column) = '#'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1


$row = 2
$Column = 2
$Sheet_Group.Cells.Item($row, $column) = 'Group Name'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1


$row = 2
$Column = 3
$Sheet_Group.Cells.Item($row, $column) = 'Group Type'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1


$row = 2
$Column = 4
$Sheet_Group.Cells.Item($row, $column) = 'E-Mail Address'
$Sheet_Group.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Group.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Group.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Group.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Group.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Group.Cells.Item($row, $column).Font.ThemeFont = 1


$Groups = Get-MsolGroup | Sort-Object DisplayName
$Sheet_Groups_Y = 3
$RowNumber = 0
Foreach ($Group in $Groups)
{
	$RowNumber++
	$Row = $Sheet_Groups_Y++
	$DisplayName = ($Group).DisplayName
	$GroupType = ($Group).GroupType
	$GroupEmail = ($Group).EmailAddress
	
	$GroupTypeString = $GroupType | Out-String
	$GroupTypeStringTrimEnd = $GroupTypeString.TrimEnd()
	
	$Sheet_Group.Cells.Item($Row, 1) = $RowNumber
	$Sheet_Group.Cells.Item($row, 1).Font.Bold = $True
	$Sheet_Group.Cells.Item($row, 1).Interior.ColorIndex = 55
	$Sheet_Group.Cells.Item($row, 1).HorizontalAlignment = -4108
	$Sheet_Group.Cells.Item($row, 1).VerticalAlignment = -4108
	$Sheet_Group.Cells.Item($row, 1).Font.Name = "Cambria"
	$Sheet_Group.Cells.Item($row, 1).Font.ColorIndex = 2
	$Sheet_Group.Cells.Item($row, 1).Font.ThemeFont = 1
	
	
	$Even = $RowNumber % 2
	If ($Even -eq 0)
	{
		
		$Sheet_Group.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_Group.Cells.Item($Row, 2) = $DisplayName
		$Sheet_Group.Cells.Item($row, 2).Interior.ColorIndex = 15
		$Sheet_Group.Cells.Item($Row, 3) = $GroupTypeStringTrimEnd
		$Sheet_Group.Cells.Item($row, 3).Interior.ColorIndex = 15
		$Sheet_Group.Cells.Item($Row, 4) = $GroupEmail
		$Sheet_Group.Cells.Item($row, 4).Interior.ColorIndex = 15
		
	}
	Else
	{
		$Sheet_Group.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_Group.Cells.Item($Row, 2) = $DisplayName
		$Sheet_Group.Cells.Item($row, 2).Interior.ColorIndex = 2
		$Sheet_Group.Cells.Item($Row, 3) = $GroupTypeStringTrimEnd
		$Sheet_Group.Cells.Item($row, 3).Interior.ColorIndex = 2
		$Sheet_Group.Cells.Item($Row, 4) = $GroupEmail
		$Sheet_Group.Cells.Item($row, 4).Interior.ColorIndex = 2
	}
	
	
	$Sheet_Group.Columns.AutoFit() | Out-Null
	$Sheet_Group.Rows.AutoFit() | Out-Null
}


#Shared Mailboxes Sheet

$Sheet_SharedMailboxes.Activate()
$Sheet_SharedMailboxes.ActiveSheet

#Create a Title for the first worksheet and adjust the font

$row = 1
$Column = 1
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Shared Mailboxes'


$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Size = 26
#$Sheet_SharedMailboxes.Cells.Item($row,$column).Font.Bold=$True
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Color = 8210719

$range = $Sheet_SharedMailboxes.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26

$range = $Sheet_SharedMailboxes.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

$row = 2
$Column = 1
$Sheet_SharedMailboxes.Cells.Item($row, $column) = '#'
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Shared Mailbox Name'
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 3
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Primary E-Mail Address'
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_SharedMailboxes.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_SharedMailboxes.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, $column).Font.ThemeFont = 1


$SharedMailboxes = Get-mailbox -RecipientTypeDetails sharedmailbox -Resultsize unlimited | Sort-Object Name
$Sheet_SharedMailboxes_Y = 3
$RowNumber = 0
Foreach ($SharedMailbox in $SharedMailboxes)
{
	$RowNumber++
	$Row = $Sheet_SharedMailboxes_Y++
	$DisplayName = ($SharedMailbox).Name
	$PrimarySmtpAddress = ($SharedMailbox).PrimarySmtpAddress
	
	$Sheet_SharedMailboxes.Cells.Item($Row, 1) = $RowNumber
	$Sheet_SharedMailboxes.Cells.Item($row, 1).Font.Bold = $True
	$Sheet_SharedMailboxes.Cells.Item($row, 1).Interior.ColorIndex = 55
	$Sheet_SharedMailboxes.Cells.Item($row, 1).HorizontalAlignment = -4108
	$Sheet_SharedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4108
	$Sheet_SharedMailboxes.Cells.Item($row, 1).Font.Name = "Cambria"
	$Sheet_SharedMailboxes.Cells.Item($row, 1).Font.ColorIndex = 2
	$Sheet_SharedMailboxes.Cells.Item($row, 1).Font.ThemeFont = 1
	
	$Even = $RowNumber % 2
	If ($Even -eq 0)
	{
		
		
		$Sheet_SharedMailboxes.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_SharedMailboxes.Cells.Item($Row, 2) = $DisplayName
		$Sheet_SharedMailboxes.Cells.Item($row, 2).Interior.ColorIndex = 15
		$Sheet_SharedMailboxes.Cells.Item($Row, 3) = $PrimarySmtpAddress
		$Sheet_SharedMailboxes.Cells.Item($row, 3).Interior.ColorIndex = 15
	}
	Else
	{
		$Sheet_SharedMailboxes.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_SharedMailboxes.Cells.Item($Row, 2) = $DisplayName
		$Sheet_SharedMailboxes.Cells.Item($row, 2).Interior.ColorIndex = 2
		$Sheet_SharedMailboxes.Cells.Item($Row, 3) = $PrimarySmtpAddress
		$Sheet_SharedMailboxes.Cells.Item($row, 3).Interior.ColorIndex = 2
	}
	
	$Sheet_SharedMailboxes.Columns.AutoFit() | Out-Null
	$Sheet_SharedMailboxes.Rows.AutoFit() | Out-Null
}


#Contact Sheet

$Sheet_Contacts.Activate()
$Sheet_Contacts.ActiveSheet

#Create a Title for the first worksheet and adjust the font

$row = 1
$Column = 1
$Sheet_Contacts.Cells.Item($row, $column) = 'Contacts'


$Sheet_Contacts.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Contacts.Cells.Item($row,$column).Font.Bold=$True
$Sheet_Contacts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Contacts.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Contacts.Cells.Item($row, $column).Font.Color = 8210719

$range = $Sheet_Contacts.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26

$range = $Sheet_Contacts.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

$row = 2
$Column = 1
$Sheet_Contacts.Cells.Item($row, $column) = '#'
$Sheet_Contacts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Contacts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Contacts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Contacts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Contacts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 2
$Sheet_Contacts.Cells.Item($row, $column) = 'Contact Name'
$Sheet_Contacts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Contacts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Contacts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Contacts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Contacts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 3
$Sheet_Contacts.Cells.Item($row, $column) = 'Primary E-Mail Address'
$Sheet_Contacts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Contacts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Contacts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Contacts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Contacts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Contacts.Cells.Item($row, $column).Font.ThemeFont = 1

$Contacts = Get-Contact | Sort-Object DisplayName
$Sheet_Contacts_Y = 3
$RowNumber = 0
Foreach ($Contact in $Contacts)
{
	$RowNumber++
	$Row = $Sheet_Contacts_Y++
	$DisplayName = ($Contact).DisplayName
	$ContactEmail = ($Contact).WindowsEmailAddress
	
	$Sheet_Contacts.Cells.Item($Row, 1) = $RowNumber
	$Sheet_Contacts.Cells.Item($row, 1).Font.Bold = $True
	$Sheet_Contacts.Cells.Item($row, 1).Interior.ColorIndex = 55
	$Sheet_Contacts.Cells.Item($row, 1).HorizontalAlignment = -4108
	$Sheet_Contacts.Cells.Item($row, 1).VerticalAlignment = -4108
	$Sheet_Contacts.Cells.Item($row, 1).Font.Name = "Cambria"
	$Sheet_Contacts.Cells.Item($row, 1).Font.ColorIndex = 2
	$Sheet_Contacts.Cells.Item($row, 1).Font.ThemeFont = 1
	
	$Even = $RowNumber % 2
	If ($Even -eq 0)
	{
		
		$Sheet_Contacts.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_Contacts.Cells.Item($Row, 2) = $DisplayName
		$Sheet_Contacts.Cells.Item($row, 2).Interior.ColorIndex = 15
		$Sheet_COntacts.Cells.Item($Row, 3) = $ContactEmail
		$Sheet_Contacts.Cells.Item($row, 3).Interior.ColorIndex = 15
	}
	Else
	{
		$Sheet_Contacts.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_Contacts.Cells.Item($Row, 2) = $DisplayName
		$Sheet_Contacts.Cells.Item($row, 2).Interior.ColorIndex = 2
		$Sheet_COntacts.Cells.Item($Row, 3) = $ContactEmail
		$Sheet_Contacts.Cells.Item($row, 3).Interior.ColorIndex = 2
	}
	
	$Sheet_Contacts.Columns.AutoFit() | Out-Null
	$Sheet_Contacts.Rows.AutoFit() | Out-Null
}



#Public Folder Sheet

$Sheet_PublicFolders.Activate()
$Sheet_PublicFolders.ActiveSheet

#Create a Title for the first worksheet and adjust the font

$row = 1
$Column = 1
$Sheet_PublicFolders.Cells.Item($row, $column) = 'Public Folders'


$Sheet_PublicFolders.Cells.Item($row, $column).Font.Size = 26
#$Sheet_PublicFolders.Cells.Item($row,$column).Font.Bold=$True
$Sheet_PublicFolders.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_PublicFolders.Cells.Item($row, $column).Font.Color = 8210719

$range = $Sheet_Contacts.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26

$range = $Sheet_PublicFolders.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

$row = 2
$Column = 1
$Sheet_PublicFolders.Cells.Item($row, $column) = '#'
$Sheet_PublicFolders.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_PublicFolders.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_PublicFolders.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_PublicFolders.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ThemeFont = 1


$row = 2
$Column = 2
$Sheet_PublicFolders.Cells.Item($row, $column) = 'Public Folder Name'
$Sheet_PublicFolders.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_PublicFolders.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_PublicFolders.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_PublicFolders.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 3
$Sheet_PublicFolders.Cells.Item($row, $column) = 'Primary E-Mail Address'
$Sheet_PublicFolders.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_PublicFolders.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_PublicFolders.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_PublicFolders.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 4
$Sheet_PublicFolders.Cells.Item($row, $column) = 'Mail Enabled'
$Sheet_PublicFolders.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_PublicFolders.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_PublicFolders.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_PublicFolders.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_PublicFolders.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_PublicFolders_Y = 3
$RowNumber = 0


$MEPFS = Get-MailPublicFolder | Sort-Object Name
$MailEnabledPFs = $MEPFS | Select-Object -ExpandProperty Name
$PF = Get-PublicFolder -recurse | Where-Object { $_.Name -notlike "IPM_SUBTREE" } | Sort-Object Name | Select-Object -ExpandProperty Name

$NonMailEnabledPFs = $PF | Where-Object { $MailEnabledPFs -notcontains $_ }
Foreach ($NonMailEnabledPF in $NonMailEnabledPFs)
{
	$Row = $Sheet_PublicFolders_Y++
	$RowNumber++
	
	$Sheet_PublicFolders.Cells.Item($Row, 1) = $RowNumber
	$Sheet_PublicFolders.Cells.Item($row, 1).Font.Bold = $True
	$Sheet_PublicFolders.Cells.Item($row, 1).Interior.ColorIndex = 55
	$Sheet_PublicFolders.Cells.Item($row, 1).HorizontalAlignment = -4108
	$Sheet_PublicFolders.Cells.Item($row, 1).VerticalAlignment = -4108
	$Sheet_PublicFolders.Cells.Item($row, 1).Font.Name = "Cambria"
	$Sheet_PublicFolders.Cells.Item($row, 1).Font.ColorIndex = 2
	$Sheet_PublicFolders.Cells.Item($row, 1).Font.ThemeFont = 1
	
	$Even = $RowNumber % 2
	If ($Even -eq 0)
	{
		$Sheet_PublicFolders.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_PublicFolders.Cells.Item($Row, 2) = $NonMailEnabledPF
		$Sheet_PublicFolders.Cells.Item($row, 2).Interior.ColorIndex = 15
		$Sheet_PublicFolders.Cells.Item($Row, 3) = "N/A"
		$Sheet_PublicFolders.Cells.Item($row, 3).Interior.ColorIndex = 15
		$Sheet_PublicFolders.Cells.Item($Row, 4) = "Not Mail-Enabled"
		$Sheet_PublicFolders.Cells.Item($row, 4).Interior.ColorIndex = 15
	}
	Else
	{
		$Sheet_PublicFolders.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_PublicFolders.Cells.Item($Row, 2) = $NonMailEnabledPF
		$Sheet_PublicFolders.Cells.Item($row, 2).Interior.ColorIndex = 2
		$Sheet_PublicFolders.Cells.Item($Row, 3) = "N/A"
		$Sheet_PublicFolders.Cells.Item($row, 3).Interior.ColorIndex = 2
		$Sheet_PublicFolders.Cells.Item($Row, 4) = "Not Mail-Enabled"
		$Sheet_PublicFolders.Cells.Item($row, 4).Interior.ColorIndex = 2
	}
	
	$Sheet_PublicFolders.Columns.AutoFit() | Out-Null
	$Sheet_PublicFolders.Rows.AutoFit() | Out-Null
	
}
Foreach ($MEPF in $MEPFS)
{
	$Row = $Sheet_PublicFolders_Y++
	$RowNumber++
	
	$MailEnabledPFMail = $MEPF | Select-Object -ExpandProperty WindowsEmailAddress
	$MailEnabledPF = $MEPF | Select-Object -ExpandProperty Name
	
	$Sheet_PublicFolders.Cells.Item($Row, 1) = $RowNumber
	$Sheet_PublicFolders.Cells.Item($row, 1).Font.Bold = $True
	$Sheet_PublicFolders.Cells.Item($row, 1).Interior.ColorIndex = 55
	$Sheet_PublicFolders.Cells.Item($row, 1).HorizontalAlignment = -4108
	$Sheet_PublicFolders.Cells.Item($row, 1).VerticalAlignment = -4108
	$Sheet_PublicFolders.Cells.Item($row, 1).Font.Name = "Cambria"
	$Sheet_PublicFolders.Cells.Item($row, 1).Font.ColorIndex = 2
	$Sheet_PublicFolders.Cells.Item($row, 1).Font.ThemeFont = 1
	
	
	$Even = $RowNumber % 2
	If ($Even -eq 0)
	{
		$Sheet_PublicFolders.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_PublicFolders.Cells.Item($Row, 2) = $MailEnabledPF
		$Sheet_PublicFolders.Cells.Item($row, 2).Interior.ColorIndex = 15
		$Sheet_PublicFolders.Cells.Item($Row, 3) = $MailEnabledPFMail
		$Sheet_PublicFolders.Cells.Item($row, 3).Interior.ColorIndex = 15
		$Sheet_PublicFolders.Cells.Item($Row, 4) = "Mail-Enabled"
		$Sheet_PublicFolders.Cells.Item($row, 4).Interior.ColorIndex = 15
	}
	Else
	{
		$Sheet_PublicFolders.Cells.Item($Row, 1).Font.Bold = $True
		$Sheet_PublicFolders.Cells.Item($Row, 2) = $MailEnabledPF
		$Sheet_PublicFolders.Cells.Item($row, 2).Interior.ColorIndex = 2
		$Sheet_PublicFolders.Cells.Item($Row, 3) = $MailEnabledPFMail
		$Sheet_PublicFolders.Cells.Item($row, 3).Interior.ColorIndex = 2
		$Sheet_PublicFolders.Cells.Item($Row, 4) = "Mail-Enabled"
		$Sheet_PublicFolders.Cells.Item($row, 4).Interior.ColorIndex = 2
	}
	
	$Sheet_PublicFolders.Columns.AutoFit() | Out-Null
	$Sheet_PublicFolders.Rows.AutoFit() | Out-Null
	
}



#Domain Sheet
$Sheet_Domains.Activate()
$Sheet_Domains.ActiveSheet

#Create a Title for the first worksheet and adjust the font

$row = 1
$Column = 1
$Sheet_Domains.Cells.Item($row, $column) = 'Domains'


$Sheet_Domains.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Group.Cells.Item($row,$column).Font.Bold=$True
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $column).Font.Color = 8210719

$range = $Sheet_Domains.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26

$range = $Sheet_Domains.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160

$row = 2
$Column = 1
$Sheet_Domains.Cells.Item($row, $column) = '#'
$Sheet_Domains.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1


$row = 2
$Column = 2
$Sheet_Domains.Cells.Item($row, $column) = 'Domain Name'
$Sheet_Domains.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 3
$Sheet_Domains.Cells.Item($row, $column) = 'Default'
$Sheet_Domains.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1

$row = 2
$Column = 4
$Sheet_Domains.Cells.Item($row, $column) = 'Verified'
$Sheet_Domains.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Domains.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Domains.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Domains.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Domains.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Domains.Cells.Item($row, $column).Font.ThemeFont = 1


$Domains = Get-MsolDomain | Sort-Object Name
$Sheet_Domains_Y = 3
$RowNumber = 0
Foreach ($Domain in $Domains)
{
	$Row = $Sheet_Domains_Y++
	$RowNumber++
	$DisplayName = ($Domain).Name
	$DefaultStatus = ($Domain).IsDefault
	$Verified = (($Domain).Status | Out-String).TrimEnd()
	
	If ($DefaultStatus -eq $True)
	{
		$Sheet_Domains.Cells.Item($Row, 1) = $RowNumber
		$Sheet_Domains.Cells.Item($row, 1).Font.Bold = $True
		$Sheet_Domains.Cells.Item($row, 1).Interior.ColorIndex = 55
		$Sheet_Domains.Cells.Item($row, 1).HorizontalAlignment = -4108
		$Sheet_Domains.Cells.Item($row, 1).VerticalAlignment = -4108
		$Sheet_Domains.Cells.Item($row, 1).Font.Name = "Cambria"
		$Sheet_Domains.Cells.Item($row, 1).Font.ColorIndex = 2
		$Sheet_Domains.Cells.Item($row, 1).Font.ThemeFont = 1
		
		$Sheet_Domains.Cells.Item($Row, 2) = $DisplayName
		$Sheet_Domains.Cells.Item($Row, 3) = $DefaultStatus
		$Sheet_Domains.Cells.Item($Row, 4) = $Verified
		
		$Sheet_Domains.Cells.Item($Row, 2).Font.Bold = $True
		$Sheet_Domains.Cells.Item($Row, 3).Font.Bold = $True
		$Sheet_Domains.Cells.Item($row, 2).Interior.ColorIndex = 6
		$Sheet_Domains.Cells.Item($row, 3).Interior.ColorIndex = 6
		$Sheet_Domains.Cells.Item($row, 4).Interior.ColorIndex = 6
	}
	Else
	{
		
		$Sheet_Domains.Cells.Item($Row, 1) = $RowNumber
		$Sheet_Domains.Cells.Item($row, 1).Font.Bold = $True
		$Sheet_Domains.Cells.Item($row, 1).Interior.ColorIndex = 55
		$Sheet_Domains.Cells.Item($row, 1).HorizontalAlignment = -4108
		$Sheet_Domains.Cells.Item($row, 1).VerticalAlignment = -4108
		$Sheet_Domains.Cells.Item($row, 1).Font.Name = "Cambria"
		$Sheet_Domains.Cells.Item($row, 1).Font.ColorIndex = 2
		$Sheet_Domains.Cells.Item($row, 1).Font.ThemeFont = 1
		
		
		$Even = $RowNumber % 2
		If ($Even -eq 0)
		{
			$Sheet_Domains.Cells.Item($Row, 2) = $DisplayName
			$Sheet_Domains.Cells.Item($row, 2).Interior.ColorIndex = 15
			$Sheet_Domains.Cells.Item($Row, 3) = $DefaultStatus
			$Sheet_Domains.Cells.Item($row, 3).Interior.ColorIndex = 15
			$Sheet_Domains.Cells.Item($Row, 4) = $Verified
			$Sheet_Domains.Cells.Item($row, 4).Interior.ColorIndex = 15
		}
		Else
		{
			$Sheet_Domains.Cells.Item($Row, 2) = $DisplayName
			$Sheet_Domains.Cells.Item($row, 2).Interior.ColorIndex = 2
			$Sheet_Domains.Cells.Item($Row, 3) = $DefaultStatus
			$Sheet_Domains.Cells.Item($row, 3).Interior.ColorIndex = 2
			$Sheet_Domains.Cells.Item($Row, 4) = $Verified
			$Sheet_Domains.Cells.Item($row, 4).Interior.ColorIndex = 2
		}
		
		$Sheet_Domains.Cells.Item($Row, 2).Font.Bold = $False
		$Sheet_Domains.Cells.Item($Row, 3).Font.Bold = $False
	}
	
	$Sheet_Domains.Columns.AutoFit() | Out-Null
	$Sheet_Domains.Rows.AutoFit() | Out-Null
}

Brad Wyatt

My name is Bradley Wyatt; I am a 4x Microsoft Most Valuable Professional in Cloud and Datacenter Management. I have given talks at many different conferences, user groups, and companies throughout the United States ranging from PowerShell to DevOps Security best practices and am the 2022 North American Outstanding Contribution to the Microsoft Community winner.

2 thoughts on “Office 365 Report Using Excel COM Interface with PowerShell”

Paturi says:

August 30, 2018 at 3:32 am

This is awesome. But just wanted to check is it possible to have one row for each license assigned to the user, instead of merging.

Paturi says:

August 30, 2018 at 9:45 pm

How can I get each license in separate row instead of joining them in one row.

The Lazy Administrator

Finding ways to do the most work with the least effort possible