Moonshot365

1.13.1

Moonshot365.psm1

function New-365Report
{
$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"
}

#Check if connected to a tenant
if (Get-Module -ListAvailable -Name AzureAD) {
}
else {
    Install-Module AzureAD -Confirm:$False -Force
}
if (Get-Module -ListAvailable -Name MsOnline) {
}
else {
    Install-Module MsOnline -Confirm:$False -Force
}
Clear-Host
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Welcome to the Office 365 Report Builder!"
Write-Host " Starting module connection checks."
Write-Host "============================================"
try
{ $var = Get-AzureADTenantDetail }

catch [Microsoft.Open.Azure.AD.CommonLibrary.AadNeedAuthenticationException]
{$credential = Get-Credential -Message "Please enter your Office 365 credentials"
Import-Module MsOnline
Connect-AzureAD -Credential $credential
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
}
$CreateUsers = ""
$CreateGroups = ""
$CreateShared = ""
$CreateResources = ""
$CreateDomains = ""
$CreateScripts = ""
$CreateAllReports = ""
$CreateContacts = ""
$MigrationTF = ""
$CheckOneDrive = ""
$CreateGroupScripts = ""
Write-Host ""
Write-Host ""
Write-Host "Connected to Azure and Exchange Online!"
Start-Sleep -Seconds 2

Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "========================================================"
Write-Host "Identify the Module Reports to generate."
Write-Host "========================================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "Generate All Reports?"
Write-Host "Including Users, Groups, Shared Mailboxes, Resource Mailboxes, Contacts, & Domains"
Write-Host ""
Write-Host "Type y for Yes, n for No"
$CreateAllReports = Read-Host "[y/n]"
If ($CreateAllReports -eq "y")
{
Write-Host ""
Write-Host "Check OneDrive Storage Usage? This will increase time when parsing Users."
Write-Host ""
Write-Host "Type y for Yes, n for No"
$CheckOneDrive = Read-Host "[y/n]"
Write-Host ""
Write-Host ""

if($CheckOneDrive -eq "y"){
  Write-Host "========================================================"
  Write-Host "Enter Tenant Name (Found in <Tenant Name>.onmicrosoft.com)"
  $TenantName = Read-Host "Name"
if (Get-Module -ListAvailable -Name Microsoft.Online.SharePoint.PowerShell) {
Write-Host "SharePoint Module Installed!"
}
else {
Write-Host "Installing SharePoint Module!"
    Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Confirm:$False -Force
}
try
{ $var = Get-SPOsite
Start-Sleep -Seconds 1
Write-Host ""
Write-Host ""
Write-Host "Connected to SharePoint Online!"
}
catch
{
  Write-Host ""
  Write-Host ""
  Write-Host "Connecting to SharePoint Online..."
  Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking
  Connect-SPOService -Url https://$TenantName-admin.sharepoint.com -credential $credential
  Write-Host ""
  Write-Host "Connected!"
  Write-Host ""
  Write-Host "========================================================"
}
}
$CreateUsers = "y"
$CreateGroups = "y"
$CreateShared = "y"
$CreateResources = "y"
$CreateDomains = "y"
$CreateScripts = ""
$CreateContacts = "y"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "Generate scripts to perform a migration to another Office 365 Tenant?"
Write-Host ""
Write-Host "Type y for Yes, n for No"
$CreateScripts = Read-Host "[y/n]"
Write-Host ""
If ($CreateScripts -eq "y")
{
  Write-Host "Please enter the @<domain>.onmicrosoft.com prefix name for destination Tenant"
  Write-Host "(ex: 'comp123' for comp123.onmicrosoft.com)"
  $MigrationOMSFT = Read-Host "Prefix Name"
  Write-Host ""
  Write-Host "Enter the full default email & username domain users should have in destination Tenant"
  Write-Host "(ex: company.com)"
  $MigrationPrimeDomain = Read-Host "Default Domain"
  $CreateGroupScripts = "y"
}
} else {
  $CreateUsers = ""
  $CreateGroups = ""
  $CreateShared = ""
  $CreateResources = ""
  $CreateDomains = ""
  $CreateScripts = ""
  $CreateContacts = ""
  $MigrationTF = ""
  $CheckOneDrive = ""
  $CreateGroupScripts = ""
  Write-Host ""
  Write-Host "========================================================"
  Write-Host "Specify Indivudal Module Reports"
  Write-Host "========================================================"
  Write-Host ""
  Write-Host "Generate report for Users?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateUsers = Read-Host "[y/n]"
  If($CreateUsers -eq "y")
  {
  Write-Host ""
  Write-Host "Check OneDrive Storage Usage? This will increase time when parsing Users."
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CheckOneDrive = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""

  if($CheckOneDrive -eq "y"){
    Write-Host "Enter Tenant Name (Found in <Tenant Name>.onmicrosoft.com)"
    $TenantName = Read-Host "Name"
  if (Get-Module -ListAvailable -Name Microsoft.Online.SharePoint.PowerShell) {
  Write-Host "SharePoint Module Installed!"
  }
  else {
  Write-Host "Installing SharePoint Module!"
      Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Confirm:$False -Force
  }
  try
  { $var = Get-SPOsite
  Start-Sleep -Seconds 1
  Write-Host ""
  Write-Host ""
  Write-Host "Connected to SharePoint Online!"
  }
  catch
  {
    Write-Host ""
    Write-Host ""
    Write-Host "Connecting to SharePoint Online..."
    Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking
    Connect-SPOService -Url https://$TenantName-admin.sharepoint.com -credential $credential
    Write-Host ""
    Write-Host "Connected!"
    Write-Host ""
    Write-Host "========================================================"
  }
  }
  }
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Groups?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateGroups = Read-Host "[y/n]"
  if($CreateGroups -eq "y"){
    Write-Host ""
    Write-Host "Create Group Migration Scripts?"
    Write-Host ""
    Write-Host "Type y for Yes, n for No"
    $CreateGroupScripts = Read-Host "[y/n]"
    Write-Host ""
    Write-Host ""
  }
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Shared Mailboxes?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateShared = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Resource Mailboxes?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateResources = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Contacts?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateContacts = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""
  Write-Host "Generate report for Domains?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateDomains = Read-Host "[y/n]"
  Write-Host ""
  Write-Host ""
  Write-Host "Generate scripts to perform a migration to another Office 365 Tenant?"
  Write-Host ""
  Write-Host "Type y for Yes, n for No"
  $CreateScripts = Read-Host "[y/n]"
  Write-Host ""
  If ($CreateScripts -eq "y")
  {
    Write-Host "Please enter the @<domain>.onmicrosoft.com prefix name for destination Tenant."
    Write-Host "(ex: company123 for company123.onmicrosoft.com)"
    $MigrationOMSFT = Read-Host "Prefix Name"
    Write-Host ""
    Write-Host "Enter the full default email & username domain users should have in destination Tenant"
    Write-Host "(ex: company.com)"
    $MigrationPrimeDomain = Read-Host "Default Domain"
  }
}
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Start-Sleep -Seconds 2
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Creating Report File. Please Wait"
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Progress -Activity 'Creating Report File!' -Status "Please wait."
Start-Sleep -Seconds 2
$TenantDisplay = Get-AzureAdTenantDetail | Select -ExpandProperty DisplayName
$Date1 = Get-Date -Format "MM-dd-yyyy HH-mm"
$SelectedReports = ""
if($CreateUsers -eq "y"){
  $SelectedReports = -join ($SelectedReports," Users -")
}
if($CreateGroups -eq "y"){
  $SelectedReports = -join ($SelectedReports," Groups -")
}
if($CreateShared -eq "y"){
  $SelectedReports = -join ($SelectedReports," Shared -")
}
if($CreateResources -eq "y"){
  $SelectedReports = -join ($SelectedReports," Resources -")
}
if($CreateContacts -eq "y"){
  $SelectedReports = -join ($SelectedReports," Contacts -")
}
if($CreateDomains -eq "y"){
  $SelectedReports = -join ($SelectedReports," Domains -")
}
if($CreateScripts -eq "y"){
  $SelectedReports = -join ($SelectedReports," Scripts -")
}
$FileName = "$TenantDisplay -$SelectedReports $Date1.xlsx"
$OutputPath = "$home\Desktop\$FileName"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
#Create the workbook
$workbook = $excel.Workbooks.Add()
$workbook.SaveAs($OutputPath)
$InitalWorksheet = $workbook.Worksheets.Item(1)
$InitalWorksheet.Name = "Generating"
#$Worksheet = $Workbook.Worksheets.Add()
#$Worksheet.Name = "Public Folders"
if($CreateScripts -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Scripts"
}
if($CreateDomains -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Domains"
}
if($CreateResources -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Resources"
}
if($CreateContacts -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Contacts"
}
if($CreateShared -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Shared Mailboxes"
}
if($CreateGroups -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Groups"
}
if($CreateUsers -eq "y"){
$Worksheet = $Workbook.Worksheets.Add()
$Worksheet.Name = "Licensed Mailboxes"
}
if($CreateUsers -eq "y"){
$Sheet_LicensedMailboxes = $Workbook.Worksheets.Item("Licensed Mailboxes")
}
if($CreateGroups -eq "y"){
$Sheet_Group = $Workbook.Worksheets.Item("Groups")
}
if($CreateShared -eq "y"){
$Sheet_SharedMailboxes = $Workbook.Worksheets.Item("Shared Mailboxes")
}
if($CreateContacts -eq "y"){
$Sheet_Contacts = $Workbook.Worksheets.Item("Contacts")
}
if($CreateDomains -eq "y"){
$Sheet_Domains = $Workbook.Worksheets.Item("Domains")
}
if($CreateResources -eq "y"){
$Sheet_Resources = $Workbook.Worksheets.Item("Resources")
}
if($CreateScripts -eq "y"){
$Sheet_Scripts = $Workbook.Worksheets.Item("Scripts")
}
$Workbook.Worksheets.Item("Generating").Delete()

#Licensed Mailbox Worksheet
if($CreateUsers -eq "y"){
$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
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 1
$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
$row = 2
$Column = 6
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Mailbox Size'
$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 = 7
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'Archive Size'
$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 = 8
$Sheet_LicensedMailboxes.Cells.Item($row, $column) = 'OneDrive Size'
$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
$UsersCount = $Users.count
$Sheet_LicensedMailboxes_Y = 3
$RowNumber = 0
$LicenseName = @()
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Users. Please Wait"
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Start-Sleep -Seconds 1
$Counter = 0
Foreach ($User in $Users)
{
$Row = $Sheet_LicensedMailboxes_Y++
$DisplayName = ($User).DisplayName
$RowNumber++
$Counter++
$EmailAddresses = @()
$primaryemailaddress = try{($User | Select-Object -ExpandProperty proxyaddresses | Where-Object { $_ -cmatch '^SMTP' }).Trim("SMTP:")}catch{}
$EmailAddresses = @(($User | Select-Object -ExpandProperty proxyaddresses | Where-Object { $_ -cnotmatch "^SMTP:$primaryemailaddress" }) -replace "smtp:")
$TotalSize = (Get-MailboxStatistics $DisplayName -ErrorAction SilentlyContinue | Select-Object -Property TotalItemSize -ErrorAction SilentlyContinue | ft -hidetableheaders | Out-String).Trim()
$ArchiveCheck = (Get-MailboxStatistics -Archive $DisplayName -ErrorAction SilentlyContinue | Select-Object -Property TotalItemSize -ErrorAction SilentlyContinue | ft -hidetableheaders | Out-String).Trim()
$SignInName = ($User).SignInName

Write-Progress -Activity 'Processing Users' -Status "User $counter of $UsersCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $UsersCount) * 100)

#Write-Host "Processing $DisplayName..."
#Write-Host "User $Counter of $UsersCount"
#Write-Host ""
#Write-Host ""
if($CheckOneDrive -eq "y")
{
if($SignInName.Contains('@'))
{
$SignInName=$SignInName.Replace('@','_')
$SignInName=$SignInName.Replace('.','_')
$SignInName=$SignInName.Replace('.','_')
$SignInName="https://$tenantname-my.sharepoint.com/personal/"+$SignInName
$OneDriveTotal = try{(Get-SPOSite -Identity $SignInName -ErrorAction SilentlyContinue | Select-Object -expandproperty StorageUsageCurrent).ToString('N0')}catch{$OneDriveTotal = "0"}
$OneDriveSize = "$OneDriveTotal MB"
}
}
#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
$Sheet_LicensedMailboxes.Cells.Item($Row, 6) = $TotalSize
$Sheet_LicensedMailboxes.Cells.Item($row, 6).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($Row, 7) = $ArchiveCheck
$Sheet_LicensedMailboxes.Cells.Item($row, 7).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($Row, 8) = $OneDriveSize
$Sheet_LicensedMailboxes.Cells.Item($row, 8).Interior.ColorIndex = 15
$Sheet_LicensedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 8).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 9).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 10).VerticalAlignment = -4160
$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
$Sheet_LicensedMailboxes.Cells.Item($Row, 6) = $TotalSize
$Sheet_LicensedMailboxes.Cells.Item($row, 6).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($Row, 7) = $ArchiveCheck
$Sheet_LicensedMailboxes.Cells.Item($row, 7).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($Row, 8) = $OneDriveSize
$Sheet_LicensedMailboxes.Cells.Item($row, 8).Interior.ColorIndex = 2
$Sheet_LicensedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 8).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 9).VerticalAlignment = -4160
$Sheet_LicensedMailboxes.Cells.Item($row, 10).VerticalAlignment = -4160
$LicenseName = @()
}
$Sheet_LicensedMailboxes.Columns.AutoFit() | Out-Null
$Sheet_LicensedMailboxes.Rows.AutoFit() | Out-Null
}
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateGroups -eq "y"){
#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
$row = 2
$Column = 5
$Sheet_Group.Cells.Item($row, $column) = 'Alias 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
$row = 2
$Column = 6
$Sheet_Group.Cells.Item($row, $column) = 'Sync Status'
$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 = 7
$Sheet_Group.Cells.Item($row, $column) = 'Members'
$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
if($CreateGroupScripts -eq "y"){
  $row = 2
  $Column = 8
  $Sheet_Group.Cells.Item($row, $column) = 'Creation Script'
  $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 = 9
  $Sheet_Group.Cells.Item($row, $column) = 'Members Script'
  $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 = 10
  $Sheet_Group.Cells.Item($row, $column) = 'Group Settings Script'
  $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
$UnifiedGroups = Get-UnifiedGroup | Sort-Object DisplayName
$UGlist = @()
foreach($UnifiedGroup in $UnifiedGroups){
  $UGlist += @($UnifiedGroup.DisplayName)
}
$GroupsCount = $Groups.count
$Sheet_Groups_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Groups. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($Group in $Groups)
{
$RowNumber++
$Counter++
$Row = $Sheet_Groups_Y++
$DisplayName = ($Group).DisplayName
Write-Progress -Activity 'Processing Groups' -Status "Group $counter of $GroupsCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $GroupsCount) * 100)
if($UGlist -contains ($Group).DisplayName){
$GroupType = "UnifiedGroup"
}else{
$GroupType = ($Group).GroupType
}

$GroupEmail = ($Group).EmailAddress
$Members = (Get-AzureADGroupMember -ObjectId $Group.ObjectID -ErrorAction SilentlyContinue | Sort-Object DisplayName | Select-Object -ExpandProperty DisplayName) -join ", "
$GetGroupDirSyncStatus = Get-AzureADGroupMember -ObjectId $Group.ObjectID -ErrorAction SilentlyContinue | Select-Object DirectorySynchronizationEnabled
if($GetGroupDirSyncStatus -eq $null){
  $GroupDirSyncStatus = "Cloud"
}else{
  $GroupDirSyncStatus = "Synced"
}
$GroupTypeString = $GroupType | Out-String
$GroupTypeStringTrimEnd = $GroupTypeString.TrimEnd()
$Aliases = @()
$Aliases = @(($Group | Select-Object -ExpandProperty ProxyAddresses | Where-Object { $_ -cnotmatch "^SMTP:$GroupEmail"}) -replace "smtp:")

#Group Script Creation
if($CreateGroupScripts -eq "y"){
  $GroupCreate = ""
  $GTypeStandard = ""
  $GroupSettingsUpdate = ""
  $GroupMemberAdd = ""
  $GroupProxyAddresses = ""
  $IndvMemberList = (Get-AzureADGroupMember -ObjectId $Group.ObjectID -ErrorAction SilentlyContinue | Sort-Object DisplayName)
  if($GroupType -eq "MailEnabledSecurity"){$GTypeStandard = "Security"}
  if($GroupType -eq "Security"){$GTypeStandard = "Security"}
  if($GroupType -eq "DistributionList"){$GTypeStandard = "Distribution"}
  if($GroupType -eq "UnifiedGroup"){$GTypeStandard = "Office 365"}
  if($GroupType -ne "Security"){
    $GroupProxyAddresses = '"' + (($Group | Select-Object -Expand ProxyAddresses) -join '","') + '"'
    Foreach ($IndvMember in $IndvMemberList)
    {
    $IndvMemberCreate = @()
    $IndvMemberCreate = @(($IndvMember | Select-Object -ExpandProperty DisplayName))
    $GroupMemberAdd = -join ($GroupMemberAdd," Add-DistributionGroupMember -Identity ""$DisplayName"" -Member ""$IndvMemberCreate"";")
    }
    if($GTypeStandard -eq "Office 365"){
      $UGMembs = '"' + ((@(Get-AzureADGroupMember -ObjectId $Group.ObjectID -ErrorAction SilentlyContinue | Select-Object -expandproperty DisplayName)) -join '","') + '"'
      $UGAliasstring = $GroupEmail | Out-String
      $UGAlias = ($UGAliasstring).split('@')[0]
      $GroupCreate = "New-UnifiedGroup -Displayname ""$DisplayName"" -Alias ""$UGAlias"""
      $GroupMemberAdd = "Add-UnifiedGroupLinks -Identity ""$DisplayName"" -LinkType Members -Links $UGMembs"
      $GroupSettingsUpdate = ""
    }else{
    $GroupSenderAuth = (Get-DistributionGroup $DisplayName).RequireSenderAuthenticationEnabled
    $GroupCreate = "New-DistributionGroup -Name ""$DisplayName"" -Type ""$GTypeStandard"""
    $GroupSettingsUpdate = "Set-DistributionGroup -Identity ""$DisplayName"" -RequireSenderAuthenticationEnabled:"+"$"+"$GroupSenderAuth -EmailAddresses $GroupProxyAddresses"
    }
  }else{
    Foreach ($IndvMember in $IndvMemberList)
    {
    $IndvMemberCreate = @()
    $IndvMemberCreate = @(($IndvMember | Select-Object -ExpandProperty DisplayName))
    $GroupMemberAdd = -join ($GroupMemberAdd," Add-DistributionGroupMember -Identity ""$DisplayName"" -Member ""$IndvMemberCreate"";")
    }
    $GroupCreate = "New-DistributionGroup -Name ""$DisplayName"" -Type ""Security"""
  }

}

$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
$Sheet_Group.Cells.Item($Row, 5) = $Aliases
$Sheet_Group.Cells.Item($row, 5).Interior.ColorIndex = 15
$Sheet_Group.Cells.Item($Row, 6) = $GroupDirSyncStatus
$Sheet_Group.Cells.Item($row, 6).Interior.ColorIndex = 15
$Sheet_Group.Cells.Item($Row, 7) = $Members
$Sheet_Group.Cells.Item($row, 7).Interior.ColorIndex = 15
if($CreateGroupScripts -eq "y")
{
  $Sheet_Group.Cells.Item($Row, 8) = $GroupCreate
  $Sheet_Group.Cells.Item($row, 8).Interior.ColorIndex = 15
  $Sheet_Group.Cells.Item($Row, 9) = $GroupMemberAdd
  $Sheet_Group.Cells.Item($row, 9).Interior.ColorIndex = 15
  $Sheet_Group.Cells.Item($Row, 10) = $GroupSettingsUpdate
  $Sheet_Group.Cells.Item($row, 10).Interior.ColorIndex = 15
}
$Sheet_Group.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 8).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 9).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 10).VerticalAlignment = -4160
$Sheet_Group.Columns.Item(7).WrapText = $True
$Sheet_Group.Rows.AutoFit() | Out-Null
$Sheet_Group.Columns.AutoFit() | Out-Null
$Sheet_Group.Columns.Item(7).columnWidth = 75
$Sheet_Group.Columns.Item(8).columnWidth = 30
$Sheet_Group.Columns.Item(9).columnWidth = 30
$Sheet_Group.Columns.Item(10).columnWidth = 30
}
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.Cells.Item($Row, 5) = $Aliases
$Sheet_Group.Cells.Item($row, 5).Interior.ColorIndex = 2
$Sheet_Group.Cells.Item($Row, 6) = $GroupDirSyncStatus
$Sheet_Group.Cells.Item($row, 6).Interior.ColorIndex = 2
$Sheet_Group.Cells.Item($Row, 7) = $Members
$Sheet_Group.Cells.Item($row, 7).Interior.ColorIndex = 2
if($CreateGroupScripts -eq "y")
{
  $Sheet_Group.Cells.Item($Row, 8) = $GroupCreate
  $Sheet_Group.Cells.Item($row, 8).Interior.ColorIndex = 2
  $Sheet_Group.Cells.Item($Row, 9) = $GroupMemberAdd
  $Sheet_Group.Cells.Item($row, 9).Interior.ColorIndex = 2
  $Sheet_Group.Cells.Item($Row, 10) = $GroupSettingsUpdate
  $Sheet_Group.Cells.Item($row, 10).Interior.ColorIndex = 2
}
$Sheet_Group.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 8).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 9).VerticalAlignment = -4160
$Sheet_Group.Cells.Item($row, 10).VerticalAlignment = -4160
$Sheet_Group.Columns.Item(7).WrapText = $True
$Sheet_Group.Rows.AutoFit() | Out-Null
$Sheet_Group.Columns.AutoFit() | Out-Null
$Sheet_Group.Columns.Item(7).columnWidth = 75
$Sheet_Group.Columns.Item(8).columnWidth = 30
$Sheet_Group.Columns.Item(9).columnWidth = 30
$Sheet_Group.Columns.Item(10).columnWidth = 30
}
}
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateShared -eq "y"){
#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
$row = 2
$Column = 4
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Alternate E-Mail Addresses'
$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 = 5
$Sheet_SharedMailboxes.Cells.Item($row, $column) = 'Mailbox Size'
$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
$SharedCount = $SharedMailboxes.count
$Sheet_SharedMailboxes_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Shared Mailboxes. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($SharedMailbox in $SharedMailboxes)
{
$RowNumber++
$Counter++
$Row = $Sheet_SharedMailboxes_Y++
$DisplayName = ($SharedMailbox).Name
$PrimarySmtpAddress = ($SharedMailbox).PrimarySmtpAddress
$TotalSize = (Get-MailboxStatistics $DisplayName -ErrorAction SilentlyContinue | Select-Object -Property TotalItemSize -ErrorAction SilentlyContinue | ft -hidetableheaders | Out-String).Trim()
$SecondarySmtpAddresses = @()
$SecondarySmtpAddresses = @(($SharedMailbox | Select-Object -ExpandProperty EmailAddresses | Where-Object { $_ -cnotmatch "^SMTP:$PrimarySmtpAddress" }) -replace "smtp:")


Write-Progress -Activity 'Processing Shared Mailboxes' -Status "Mailbox $counter of $SharedCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $SharedCount) * 100)

$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
$Sheet_SharedMailboxes.Cells.Item($Row, 4) = $SecondarySmtpAddresses
$Sheet_SharedMailboxes.Cells.Item($row, 4).Interior.ColorIndex = 15
$Sheet_SharedMailboxes.Cells.Item($Row, 5) = $TotalSize
$Sheet_SharedMailboxes.Cells.Item($row, 5).Interior.ColorIndex = 15
$Sheet_SharedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 7).VerticalAlignment = -4160

}
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.Cells.Item($Row, 4) = $SecondarySmtpAddresses
$Sheet_SharedMailboxes.Cells.Item($row, 4).Interior.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($Row, 5) = $TotalSize
$Sheet_SharedMailboxes.Cells.Item($row, 5).Interior.ColorIndex = 2
$Sheet_SharedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 7).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_SharedMailboxes.Cells.Item($row, 5).VerticalAlignment = -4160

}
$Sheet_SharedMailboxes.Columns.AutoFit() | Out-Null
$Sheet_SharedMailboxes.Rows.AutoFit() | Out-Null
}
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateContacts -eq "y"){
#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
$ContactsCount = $Contacts.count
$Sheet_Contacts_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Contacts. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($Contact in $Contacts)
{
$RowNumber++
$Counter++
$Row = $Sheet_Contacts_Y++
$DisplayName = ($Contact).DisplayName
$ContactEmail = ($Contact).WindowsEmailAddress
Write-Progress -Activity 'Processing Contacts' -Status "Contact $counter of $ContactsCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $ContactsCount) * 100)

$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
}
Start-Sleep -Seconds 2
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateResources -eq "y"){
#Resources Sheet
$Sheet_Resources.Activate()
$Sheet_Resources.ActiveSheet
#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1
$Sheet_Resources.Cells.Item($row, $column) = 'Resource Mailboxes'
$Sheet_Resources.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Resources.Cells.Item($row,$column).Font.Bold=$True
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_Resources.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_Resources.Range("a1", "c1")
$range.Merge() | Out-Null
$range.VerticalAlignment = -4160
$row = 2
$Column = 1
$Sheet_Resources.Cells.Item($row, $column) = '#'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_Resources.Cells.Item($row, $column) = 'Resource Name'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_Resources.Cells.Item($row, $column) = 'Primary E-Mail Address'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 4
$Sheet_Resources.Cells.Item($row, $column) = 'Alternate Addresses'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 5
$Sheet_Resources.Cells.Item($row, $column) = 'Details'
$Sheet_Resources.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, $column).Font.ThemeFont = 1
$Resources = Get-Mailbox -ResultSize Unlimited -Filter '(RecipientTypeDetails -eq "RoomMailBox")' | Sort-Object Name
$ResourcesCount = $Resources.count
$Sheet_Resources_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "=================================================="
Write-Host "Processing Resource & Room Mailboxes. Please Wait."
Write-Host "=================================================="
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($Room in $Resources)
{
$RowNumber++
$Counter++
$Row = $Sheet_Resources_Y++
$DisplayName = ($Room).Name
$PrimarySmtpAddress = ($Room).PrimarySmtpAddress
$TotalSize = (Get-MailboxStatistics $DisplayName -ErrorAction SilentlyContinue | Select-Object -Property TotalItemSize -ErrorAction SilentlyContinue | ft -hidetableheaders | Out-String).Trim()
$SecondarySmtpAddresses = @()
$SecondarySmtpAddresses = @(($Room | Select-Object -ExpandProperty EmailAddresses | Where-Object { $_ -cnotmatch "^SMTP:$PrimarySmtpAddress" }) -replace "smtp:")
Write-Progress -Activity 'Processing Resource & Room Mailboxes' -Status "Resource $counter of $ResourcesCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $ResourcesCount) * 100)

$Details = ""
$Details = (Get-CalendarProcessing $PrimarySmtpAddress | fl | Out-String).Trim()

$Sheet_Resources.Cells.Item($Row, 1) = $RowNumber
$Sheet_Resources.Cells.Item($row, 1).Font.Bold = $True
$Sheet_Resources.Cells.Item($row, 1).Interior.ColorIndex = 55
$Sheet_Resources.Cells.Item($row, 1).HorizontalAlignment = -4108
$Sheet_Resources.Cells.Item($row, 1).VerticalAlignment = -4108
$Sheet_Resources.Cells.Item($row, 1).Font.Name = "Cambria"
$Sheet_Resources.Cells.Item($row, 1).Font.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, 1).Font.ThemeFont = 1
$Even = $RowNumber % 2
If ($Even -eq 0)
{
$Sheet_Resources.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_Resources.Cells.Item($Row, 2) = $DisplayName
$Sheet_Resources.Cells.Item($row, 2).Interior.ColorIndex = 15
$Sheet_Resources.Cells.Item($Row, 3) = $PrimarySmtpAddress
$Sheet_Resources.Cells.Item($row, 3).Interior.ColorIndex = 15
$Sheet_Resources.Cells.Item($Row, 4) = $SecondarySmtpAddresses
$Sheet_Resources.Cells.Item($row, 4).Interior.ColorIndex = 15
$Sheet_Resources.Cells.Item($Row, 5) = $Details
$Sheet_Resources.Cells.Item($row, 5).Interior.ColorIndex = 15
$Sheet_Resources.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_Resources.Rows.Item($row).RowHeight = 25
}
Else
{
$Sheet_Resources.Cells.Item($Row, 1).Font.Bold = $True
$Sheet_Resources.Cells.Item($Row, 2) = $DisplayName
$Sheet_Resources.Cells.Item($row, 2).Interior.ColorIndex = 2
$Sheet_Resources.Cells.Item($Row, 3) = $PrimarySmtpAddress
$Sheet_Resources.Cells.Item($row, 3).Interior.ColorIndex = 2
$Sheet_Resources.Cells.Item($Row, 4) = $SecondarySmtpAddresses
$Sheet_Resources.Cells.Item($row, 4).Interior.ColorIndex = 2
$Sheet_Resources.Cells.Item($Row, 5) = $Details
$Sheet_Resources.Cells.Item($row, 5).Interior.ColorIndex = 2
$Sheet_Resources.Cells.Item($row, 1).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 2).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 3).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 4).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 5).VerticalAlignment = -4160
$Sheet_Resources.Cells.Item($row, 6).VerticalAlignment = -4160
$Sheet_Resources.Rows.Item($row).RowHeight = 25
}
$Sheet_Resources.Columns.AutoFit() | Out-Null
$Sheet_Resources.Columns.Item(5).columnWidth = 57
$Sheet_Resources.Columns.Item(4).columnWidth = 57
}
Start-Sleep -Seconds 2
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 2
}

if($CreateDomains -eq "y"){
#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
$Domainscount = $Domains.count
$Sheet_Domains_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Processing Domains. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Foreach ($Domain in $Domains)
{
$Row = $Sheet_Domains_Y++
$RowNumber++
$Counter++
$DisplayName = ($Domain).Name
$DefaultStatus = ($Domain).IsDefault
$Verified = (($Domain).Status | Out-String).TrimEnd()
Write-Progress -Activity 'Processing Domains' -Status "Domain $counter of $SharedCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $Domainscount) * 100)

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
}
Start-Sleep -Seconds 1
Write-Progress -Activity 'Moving to next step.' -Status "Please wait."
Start-Sleep -Seconds 1
}


if($CreateScripts -eq "y"){
#Scripts Worksheet
$Sheet_Scripts.Activate()
$Sheet_Scripts.ActiveSheet
$row = 1
$Column = 1
$Sheet_Scripts.Cells.Item($row, $column) = 'Scripts'
$Sheet_Scripts.Cells.Item($row, $column).Font.Size = 26
#$Sheet_Scripts.Cells.Item($row, $column).Font.Bold=$True
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeColor = 4
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $column).Font.Color = 8210719
$range = $Sheet_Scripts.Range("a1", "c1")
$range.Style = 'Title'
$range.Font.Size = 26
$range = $Sheet_Scripts.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_Scripts.Cells.Item($row, $column) = '#'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 2
$Sheet_Scripts.Cells.Item($row, $column) = 'User Display Name'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 3
$Sheet_Scripts.Cells.Item($row, $column) = 'Mailbox Creation Script'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 4
$Sheet_Scripts.Cells.Item($row, $column) = 'UPN And Settings Script'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 5
$Sheet_Scripts.Cells.Item($row, $column) = 'Mailbox Permissions Script'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$row = 2
$Column = 6
$Sheet_Scripts.Cells.Item($row, $column) = 'Additional Script'
$Sheet_Scripts.Cells.Item($row, $Column).Font.Bold = $True
$Sheet_Scripts.Cells.Item($row, $Column).Interior.ColorIndex = 55
$Sheet_Scripts.Cells.Item($row, $Column).HorizontalAlignment = -4108
$Sheet_Scripts.Cells.Item($row, $Column).VerticalAlignment = -4160
$Sheet_Scripts.Cells.Item($row, $column).Font.Name = "Cambria"
$Sheet_Scripts.Cells.Item($row, $column).Font.ColorIndex = 2
$Sheet_Scripts.Cells.Item($row, $column).Font.ThemeFont = 1
$Sheet_Scripts.Columns.AutoFit() | Out-Null
$Sheet_Scripts.Rows.AutoFit() | Out-Null
$Mailboxes = Get-Mailbox -ResultSize Unlimited -ErrorAction SilentlyContinue | Sort-Object Name
$MailboxesCount = $Mailboxes.Count
$Sheet_Scripts_Y = 3
$RowNumber = 0
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "============================================"
Write-Host "Generating Scripts. Please Wait..."
Write-Host "============================================"
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
$Counter = 0
Write-Progress -Activity 'Generating Scripts' -Status "Please Wait"
Start-Sleep -Seconds 2
Foreach ($Mailbox in $Mailboxes)
{
  $RowNumber++
  $Counter++
  $Row = $Sheet_Scripts_Y++
  $DisplayName = ($Mailbox).DisplayName
  Write-Progress -Activity 'Generating Scripts' -Status "User Script $counter of $MailboxesCount" -CurrentOperation "$DisplayName" -PercentComplete (($Counter / $MailboxesCount) * 100)

#Mailbox Properties
  $MailboxProp = Get-Mailbox $Displayname -ErrorAction SilentlyContinue
  $MailboxPropString = ""
    $curUPN = ""
    $MailPropPrimary  = $null
    $DeliverForward  = $null
    $LitHold  = $null
    $ForwardingAddress  = $null
    $ForwardingSmtpAddress  = $null
    $MailPropUPN  = $null
    $WinLiveID  = $null
    $MSOSID  = $null
    $ArchiveStatus  = $null
    $MailPropAlias  = $null
    $MailPropSecondaryAddresses  = $null
    $MailPropShared  = $null
    $MailPropRecipientType  = $null
    $MailPropRecipientTypeDetails  = $null
    $ResourceType  = $null
    $RoomEnabled  = $null
    $MailPropPrimary = ($MailboxProp).PrimarySmtpAddress
    $DeliverForward = ($MailboxProp).DeliverToMailboxAndForward
    $LitHold = ($MailboxProp).LitigationHoldEnabled
    $ForwardingAddress = ($MailboxProp).ForwardingAddress
    $ForwardingSmtpAddress = ($MailboxProp).ForwardingSmtpAddress
    $MailPropUPN = ($MailboxProp).UserPrincipalName
    $MailPropUPNAlias = ($MailboxProp).UserPrincipalName.split('@')[0]
    $WinLiveID = ($MailboxProp).WindowsLiveID
    $MSOSID = ($MailboxProp).MicrosoftOnlineServicesID
    $ArchiveStatus = ($MailboxProp).ArchiveStatus
    $ArchiveName = ($MailboxProp).ArchiveName
    $MailPropAlias = ($MailboxProp).Alias
    $MailPropSecondaryAddresses = '"' + ((Get-Mailbox $DisplayName | Select-Object -Expand EmailAddresses) -join '","') + '"'
    $MailPropShared = ($MailboxProp).IsShared
    $MailPropRecipientType = ($MailboxProp).RecipientType
    $MailPropRecipientTypeDetails = ($MailboxProp).RecipientTypeDetails
    $GrantSendOnBehalfTo = ($MailboxProp).GrantSendOnBehalfTo
    $ResourceType = ($MailboxProp).ResourceType
    $RoomEnabled = ($MailboxProp).RoomMailboxAccountEnabled

$MailboxPropString = "Set-Variable -Name ""curUPN"" -Value ((Get-MsolUser | Where-Object {"+"$"+"_"+".DisplayName -eq ""$DisplayName""} | Select UserPrincipalName | ft -hide | Out-String).Trim()); Set-Mailbox -Identity ""$DisplayName"""
  If (!$DeliverForward) { } else { $MailboxPropString = -join ($MailboxPropString," ","-DeliverToMailboxAndForward ",$DeliverForward) }
  If (!$LitHold) { } else { $MailboxPropString = -join ($MailboxPropString," ","-LitigationHoldEnabled ",$LitHold) }
  If (!$ForwardingAddress) { } else { $MailboxPropString = -join ($MailboxPropString," ","-ForwardingAddress ",$ForwardingAddress) }
  If (!$ForwardingSmtpAddress) { } else { $MailboxPropString = -join ($MailboxPropString," ","-ForwardingSmtpAddress ",$ForwardingSmtpAddress) }
  If (!$MailPropAlias) { } else { $MailboxPropString = -join ($MailboxPropString," ","-Alias ",$MailPropAlias) }
  If (!$MailPropSecondaryAddresses) { } else { $MailboxPropString = -join ($MailboxPropString, " ","-EmailAddresses @{Add=",$MailPropSecondaryAddresses,"}") }
  If (!$MailPropPrimary) { } else { $MailboxPropString = -join ($MailboxPropString, " ","-WindowsEmailAddress ",$MailPropPrimary) }
  If (!$MailPropShared) { } else { $MailboxPropString = -join ($MailboxPropString," ","-IsShared ",$MailPropShared) }
  If (!$ResourceType) { } else { $MailboxPropString = -join ($MailboxPropString," ","-ResourceType ",$ResourceType) }
$MailboxPropString = -join ($MailboxPropString,"; ","Set-MsolUserPrincipalName -UserPrincipalName $"+"curUPN -NewUserPrincipalName ""$MailPropPrimary""")

  #Room Mailbox Settings
  If($MailPropRecipientTypeDetails -eq "RoomMailbox")
  {
    $CalProc = Get-CalendarProcessing $Displayname -ErrorAction SilentlyContinue
    $CalAutomateProcessing = ($CalProc).AutomateProcessing
    $CalAllowConflicts = ($CalProc).AllowConflicts
    $CalBookingWindowInDays = ($CalProc).BookingWindowInDays
    $CalMaximumDurationInMinutes = ($CalProc).MaximumDurationInMinutes
    $CalAllowRecurringMeetings = ($CalProc).AllowRecurringMeetings
    $CalScheduleOnlyDuringWorkHours = ($CalProc).ScheduleOnlyDuringWorkHours
    $CalConflictPercentageAllowed = ($CalProc).ConflictPercentageAllowed
    $CalMaximumConflictInstances = ($CalProc).MaximumConflictInstances
    $CalForwardRequestsToDelegates = ($CalProc).ForwardRequestsToDelegates
    $CalDeleteAttachments = ($CalProc).DeleteAttachments
    $CalDeleteComments = ($CalProc).DeleteComments
    $CalRemovePrivateProperty = ($CalProc).RemovePrivateProperty
    $CalDeleteSubject = ($CalProc).DeleteSubject
    $CalAddOrganizerToSubject = ($CalProc).AddOrganizerToSubject
    $RoomCalProcSet = "Set-CalendarProcessing ""$DisplayName"" -AutomateProcessing $CalAutomateProcessing -AllowConflicts $CalAllowConflicts -BookingWindowInDays $CalBookingWindowInDays -MaximumDurationInMinutes $CalMaximumDurationInMinutes -AllowRecurringMeetings $CalAllowRecurringMeetings -ScheduleOnlyDuringWorkHours $CalScheduleOnlyDuringWorkHours -ConflictPercentageAllowed $CalConflictPercentageAllowed -MaximumConflictInstances $CalMaximumConflictInstances -ForwardRequestsToDelegates $CalForwardRequestsToDelegates -DeleteAttachments $CalDeleteAttachments -DeleteComments $CalDeleteComments -RemovePrivateProperty $CalRemovePrivateProperty -DeleteSubject $CalDeleteSubject -AddOrganizerToSubject $CalAddOrganizerToSubject"
    $MailboxPropString = -join ($MailboxPropString,"; ",$RoomCalProcSet)
  }

  #Mailbox Permissions
  $Permissions = Get-MailboxPermission $DisplayName -ErrorAction SilentlyContinue | Where {$_.user.tostring() -ne "NT AUTHORITY\SELF" -and $_.IsInherited -eq $False}
  $PermArray = ""
  Foreach ($Permission in $Permissions)
  {
  $AccessRightsArray = @()
  $AccessRightsArray = @(($Permission | Select-Object -ExpandProperty AccessRights))
  $PermissionUser = ($Permission | Select-Object -ExpandProperty User)
  $PermArray = -join ($PermArray," Add-MailboxPermission -Identity ""$DisplayName"" -User $PermissionUser -AccessRights $AccessRightsArray -InheritanceType All -Confirm:"+"$"+"False);")
  }
  $RecipientPermissions = Get-RecipientPermission $DisplayName -ErrorAction SilentlyContinue | Where {$_.trustee.tostring() -ne "NT AUTHORITY\SELF" -and $_.IsInherited -eq $False}
  $RecipPermArray = ""
  Foreach ($RecipPermission in $RecipientPermissions)
  {
  $RecipAccessRightsArray = @()
  $RecipAccessRightsArray = @(($RecipPermission | Select-Object -ExpandProperty AccessRights))
  $Trustee = ($RecipPermission | Select-Object -ExpandProperty Trustee)
  $PermArray = -join ($PermArray," Add-RecipientPermission ""$DisplayName"" -Trustee $PermissionUser -AccessRights $RecipAccessRightsArray -Confirm:"+"$"+"False);")
  }

#Create Mailboxes
  $CreateUser = Get-User $DisplayName
  $CreateFirstName = ($CreateUser).FirstName
  $CreateLastName = ($CreateUser).LastName
  If($MailPropRecipientTypeDetails -eq "SharedMailbox")
  {
  $MailboxCreationScript = ""
  $MailboxCreationScript =    "New-Mailbox -Alias $MailPropAlias -Name ""$displayname"" -Shared -PrimarySmtpAddress $MailPropAlias@$MigrationOMSFT.onmicrosoft.com"
  }else
  {
    if($MailPropRecipientTypeDetails -eq "RoomMailbox")
    {
      $MailboxCreationScript = ""
      $MailboxCreationScript =    "New-Mailbox -Alias $MailPropAlias -Name ""$displayname"" -Room -PrimarySmtpAddress $MailPropAlias@$MigrationOMSFT.onmicrosoft.com"
    }Else
    {
      $MailboxCreationScript = ""
      $MailboxCreationScript = "New-Mailbox -Alias $MailPropAlias -Name ""$DisplayName"" -FirstName ""$CreateFirstName"" -LastName ""$CreateLastName"" -DisplayName ""$DisplayName"" -MicrosoftOnlineServicesID $MailPropUPNAlias@$MigrationOMSFT.onmicrosoft.com -Password $($SecurePassword) -ResetPasswordOnNextLogon $($false)"
    }
  }
  $Sheet_Scripts.Cells.Item($Row, 1) = $RowNumber
  $Sheet_Scripts.Cells.Item($row, 1).Font.Bold = $True
  $Sheet_Scripts.Cells.Item($row, 1).Interior.ColorIndex = 55
  $Sheet_Scripts.Cells.Item($row, 1).HorizontalAlignment = -4108
  $Sheet_Scripts.Cells.Item($row, 1).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($row, 1).Font.Name = "Cambria"
  $Sheet_Scripts.Cells.Item($row, 1).Font.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($row, 1).Font.ThemeFont = 1
  $Even = $RowNumber % 2
  If ($Even -eq 0)
  {
  $Sheet_Scripts.Cells.Item($Row, 2) = $DisplayName
  $Sheet_Scripts.Cells.Item($row, 2).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 2).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 3) = $MailboxCreationScript
  $Sheet_Scripts.Cells.Item($row, 3).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 3).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 4) = $MailboxPropString
  $Sheet_Scripts.Cells.Item($row, 4).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 4).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 5) = $PermArray
  $Sheet_Scripts.Cells.Item($row, 5).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 5).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 6) = $AdditionalScripts
  $Sheet_Scripts.Cells.Item($row, 6).Interior.ColorIndex = 15
  $Sheet_Scripts.Cells.Item($Row, 6).VerticalAlignment = -4108
}
Else{
  $Sheet_Scripts.Cells.Item($Row, 2) = $DisplayName
  $Sheet_Scripts.Cells.Item($row, 2).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 2).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 3) = $MailboxCreationScript
  $Sheet_Scripts.Cells.Item($row, 3).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 3).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 4) = $MailboxPropString
  $Sheet_Scripts.Cells.Item($row, 4).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 4).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 5) = $PermArray
  $Sheet_Scripts.Cells.Item($row, 5).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 5).VerticalAlignment = -4108
  $Sheet_Scripts.Cells.Item($Row, 6) = $AdditionalScripts
  $Sheet_Scripts.Cells.Item($row, 6).Interior.ColorIndex = 2
  $Sheet_Scripts.Cells.Item($Row, 6).VerticalAlignment = -4108
}
}
}
Clear-Host
Write-Progress -Activity 'Finalizing Report' -Status "Please Wait"
Start-Sleep -Seconds 2


$Workbook.Sheets(1).Select()
$excel.DisplayAlerts = $False
$workbook.SaveAs($OutputPath)
$excel.DisplayAlerts = $True
Start-Sleep -Seconds 2
Clear-Host
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "==================================================================="
Write-Host "==================================================================="
Write-Host "Report Completed!"
Write-Host ""
Write-Host "Saved to Desktop as:"
Write-Host "''$FileName'"
Write-Host "==================================================================="
Write-Host "==================================================================="
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host ""
}
function Connect-Office365
{
<#
.NOTES
===========================================================================
Created on: 2/4/2019 10:42 PM
Created by: Bradley Wyatt
E-Mail: Brad@TheLazyAdministrator.com
GitHub: https://github.com/bwya77
Website: https://www.thelazyadministrator.com
Organization: Porcaro Stolarek Mete Partners; The Lazy Administrator
Filename: Connect-Office365.ps1
Version: 1.0.4
Contributors: /u/Sheppard_Ra
Changelog:
1.0.4
- Host title will add a service or services you are connected to. If unable to connect it will not display connection status until connection is valid
===========================================================================
.SYNOPSIS
Connect to Office 365 Services
.DESCRIPTION
 
.PARAMETER MFA
Description: Specifies MFA requirement to sign into Office 365 services. If set to $True it will use the Office 365 ExoPSSession Module to sign into Exchange & Compliance Center using MFA. Other modules support MFA without needing another external module
.PARAMETER Exchange
Description: Connect to Exchange Online
.PARAMETER SkypeForBusiness
Description: Connect to Skype for Business
.PARAMETER SharePoint
Description: Connect to SharePoint Online
.PARAMETER SecurityandCompliance
Description: Connect to Security and Compliance Center
.PARAMETER AzureAD
Description: Connect to Azure AD V2
.PARAMETER MSOnline
Type: Switch
Description: Connect to Azure AD V1
.PARAMETER Teams
Type: Switch
Description: Connect to Teams
.EXAMPLE
Description: Connect to SharePoint Online
C:\PS> Connect-Office365 -SharePoint
.EXAMPLE
Description: Connect to Exchange Online and Azure AD V1 (MSOnline)
C:\PS> Connect-Office365 -Service Exchange, MSOnline
.EXAMPLE
Description: Connect to Exchange Online and Azure AD V2 using Multi-Factor Authentication
C:\PS> Connect-Office365 -Service Exchange, MSOnline -MFA
.EXAMPLE
Description: Connect to Teams and Skype for Business
C:\PS> Connect-Office365 -Service Teams, SkypeForBusiness
.EXAMPLE
Description: Connect to SharePoint Online
C:\PS> Connect-Office365 -Service SharePoint -SharePointOrganizationName bwya77 -MFA
.LINK
 
#>
[OutputType()]
[CmdletBinding(DefaultParameterSetName)]
$ServicesIndex = ""
$MFAcheck = ""
Write-Host ""
Write-Host "==============================================" -ForegroundColor Yellow
Write-Host "Services to Connect To:" -ForegroundColor Yellow
Write-Host "1. AzureAD" -ForegroundColor Yellow
Write-Host "2. Exchange Online" -ForegroundColor Yellow
Write-Host "3. MSOnline (AzureAD v2)" -ForegroundColor Yellow
Write-Host "4. Teams" -ForegroundColor Yellow
Write-Host "5. SkypeForBusiness" -ForegroundColor Yellow
Write-Host "6. SharePoint" -ForegroundColor Yellow
Write-Host "7. Security and Compliance - Requires MFA" -ForegroundColor Yellow
Write-Host "==============================================" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Write-Host "Which services do you want to connect to? Type the numbers, separated by a comma." -ForegroundColor Yellow
$ServicesIndex = Read-Host "Services"
Write-Host ""
Write-Host "Connect via MFA?" -ForegroundColor Yellow
$MFAcheck = Read-Host "Yes or No"
Start-Sleep -seconds 1

$ServicesString = ""
$ConnectCall = "Connect-Office365 -Service "

if($ServicesIndex -like "*1*"){
$ServicesString = -join ($ServicesString,"AzureAD,")
}
if($ServicesIndex -like "*2*"){
$ServicesString = -join ($ServicesString,"ExchangeOnline,")
}
if($ServicesIndex -like "*3*"){
$ServicesString = -join ($ServicesString,"MSOnline,")
}
if($ServicesIndex -like "*4*"){
$ServicesString = -join ($ServicesString,"Teams,")
}
if($ServicesIndex -like "*5*"){
$ServicesString = -join ($ServicesString,"SkypeForBusiness,")
}
if($ServicesIndex -like "*7*"){
$ServicesString = -join ($ServicesString,"SecurityandCompliance,")
}
if($ServicesIndex -like "*6*"){
Write-Host ""
Write-Host "SharePoint:" -ForegroundColor Yellow
$SharePointOrganizationName = Read-Host "What is your tenant name? (<tenant name>.onmicrosoft.com)"
$ServicesString = -join ($ServicesString,"SharePoint,")
}
$ServicesString = $ServicesString.trimend(",")
$ServicesString = $ServicesString.trimstart(",")
$ServicesString = $ServicesString.trimstart(" ")
$ConnectCall = -join ($ConnectCall,$ServicesString)
$MFA = ""
if($MFAcheck -like "*yes*"){
$MFA = $True
}else{
$MFA = $False}
Start-Sleep -seconds 1
Write-Host ""
Write-Host ""
Write-Host "Calling connection script..." -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Start-Sleep -seconds 1

$Service = $ServicesString -split(',')
$getModuleSplat = @{
ListAvailable = $True
Verbose          = $False
}
If ($MFA -ne $True)
{
Write-Host "Gathering PSCredentials object for non MFA sign on"
$Credential = Get-Credential -Message "Please enter your Office 365 credentials"
}
ForEach ($Item in $Service)
{
Write-Host "Attempting connection to $Item"
Switch ($Item)
{
AzureAD {
If ($null -eq (Get-Module @getModuleSplat -Name "AzureAD"))
{
Write-Error "SkypeOnlineConnector Module is not present!"
continue
}
Else
{
If ($MFA -eq $True)
{
$Connect = Connect-AzureAD
$Connect
If ($Connect -ne $Null)
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: AzureAD"
}
Else
{
$host.ui.RawUI.WindowTitle += " - AzureAD"
}
}
}
Else
{
$Connect = Connect-AzureAD -Credential $Credential
$Connect
If ($Connect -ne $Null)
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: AzureAD"
}
Else
{
$host.ui.RawUI.WindowTitle += " - AzureAD"
}
}
}
}
continue
}
ExchangeOnline {
If ($MFA -eq $True)
{
$getChildItemSplat = @{
Path = "$Env:LOCALAPPDATA\Apps\2.0\*\CreateExoPSSession.ps1"
Recurse = $true
ErrorAction = 'SilentlyContinue'
Verbose = $false
}
$MFAExchangeModule = ((Get-ChildItem @getChildItemSplat | Select-Object -ExpandProperty Target -First 1).Replace("CreateExoPSSession.ps1", ""))
If ($null -eq $MFAExchangeModule)
{
Write-Error "The Exchange Online MFA Module was not found!
https://docs.microsoft.com/en-us/powershell/exchange/exchange-online/connect-to-exchange-online-powershell/mfa-connect-to-exchange-online-powershell?view=exchange-ps"
continue
}
Else
{
Write-Host "Importing Exchange MFA Module"
. "$MFAExchangeModule\CreateExoPSSession.ps1"
Write-Host "Connecting to Exchange Online"
Connect-EXOPSSession
If ($Null -ne (Get-PSSession | Where-Object { $_.ConfigurationName -like "*Exchange*" }))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Exchange"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Exchange"
}
}
}
}
Else
{
$newPSSessionSplat = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri      = "https://ps.outlook.com/powershell/"
Authentication    = 'Basic'
Credential          = $Credential
AllowRedirection  = $true
}
Write-Host "Connecting to Exchange Online"
$EOSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://outlook.office365.com/powershell-liveid/" -Credential $credential -Authentication "Basic" -AllowRedirection
Import-PSSession $EOSession -DisableNameChecking -AllowClobber
If ($Null -ne (Get-PSSession | Where-Object { $_.ConfigurationName -like "*Exchange*" }))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Exchange"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Exchange"
}
}
}
continue
}
MSOnline {
If ($null -eq (Get-Module @getModuleSplat -Name "MSOnline"))
{
Write-Error "MSOnline Module is not present!"
continue
}
Else
{
Write-Host "Connecting to MSOnline"
If ($MFA -eq $True)
{
Connect-MsolService
If ($Null -ne (Get-MsolCompanyInformation -ErrorAction SilentlyContinue))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: MSOnline"
}
Else
{
$host.ui.RawUI.WindowTitle += " - MSOnline"
}
}
}
Else
{
Connect-MsolService -Credential $Credential
If ($Null -ne (Get-MsolCompanyInformation -ErrorAction SilentlyContinue))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: MSOnline"
}
Else
{
$host.ui.RawUI.WindowTitle += " - MSOnline"
}
}
}
}
continue
}
SecurityAndCompliance {
If ($MFA -eq $True)
{
$getChildItemSplat = @{
Path = "$Env:LOCALAPPDATA\Apps\2.0\*\CreateExoPSSession.ps1"
Recurse = $true
ErrorAction = 'SilentlyContinue'
Verbose = $false
}
$MFAExchangeModule = ((Get-ChildItem @getChildItemSplat | Select-Object -ExpandProperty Target -First 1).Replace("CreateExoPSSession.ps1", ""))
If ($null -eq $MFAExchangeModule)
{
Write-Error "The Exchange Online MFA Module was not found!
https://docs.microsoft.com/en-us/powershell/exchange/exchange-online/connect-to-exchange-online-powershell/mfa-connect-to-exchange-online-powershell?view=exchange-ps"
continue
}
Else
{
Write-Host "Importing Exchange MFA Module (Required)"
. "$MFAExchangeModule\CreateExoPSSession.ps1"
Write-Host "Connecting to Security and Compliance Center"
Connect-IPPSSession
If ($Null -ne (Get-PSSession | Where-Object { $_.ConfigurationName -like "*Exchange*" }))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Security and Compliance Center"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Security and Compliance Center"
}
}
}
}
Else
{
$newPSSessionSplat = @{
ConfigurationName = 'Microsoft.SecurityAndCompliance'
ConnectionUri      = 'https://ps.compliance.protection.outlook.com/powershell-liveid/'
Authentication    = 'Basic'
Credential          = $Credential
AllowRedirection  = $true
}
$Session = New-PSSession @newPSSessionSplat
Write-Host "Connecting to SecurityAndCompliance"
Import-PSSession $Session -DisableNameChecking
If ($Null -ne (Get-PSSession | Where-Object { $_.ConfigurationName -like "*Exchange*" }))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Security and Compliance Center"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Security and Compliance Center"
}
}
}
continue
}
SharePoint {
If ($null -eq (Get-Module @getModuleSplat -Name Microsoft.Online.SharePoint.PowerShell))
{
Write-Error "Microsoft.Online.SharePoint.PowerShell Module is not present!"
continue
Write-Host "Installing SharePoint Module!"
Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Confirm:$False -Force
}
Else
{
If (!$SharePointOrganizationName)
{
Write-Error 'Please provide a valid SharePoint organization name with the -SharePointOrganizationName parameter.'
continue
}
$SharePointURL = "https://{0}-admin.sharepoint.com" -f $SharePointOrganizationName
Write-Host "Connecting to SharePoint at $SharePointURL"
If ($MFA -eq $True)
{
$SPOSession =  Connect-SPOService -Url https://$SharePointOrganizationName-admin.sharepoint.com -credential $credential
$SPOSession
If ($Null -ne (Get-SPOTenant))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: SharePoint Online"
}
Else
{
$host.ui.RawUI.WindowTitle += " - SharePoint Online"
}
}
}
Else
{
$SPOSession = Connect-SPOService -Url https://$SharePointOrganizationName-admin.sharepoint.com -credential $credential
$SPOSession
If ($Null -ne (Get-SPOTenant))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: SharePoint Online"
}
Else
{
$host.ui.RawUI.WindowTitle += " - SharePoint Online"
}
}
}
}
continue
}
SkypeForBusiness {
Write-Host "Connecting to SkypeForBusiness"
If ($null -eq (Get-Module @getModuleSplat -Name "SkypeOnlineConnector"))
{
Write-Error "SkypeOnlineConnector Module is not present!"
}
Else
{
# Skype for Business module
Import-Module SkypeOnlineConnector
If ($MFA -eq $True)
{
$CSSession = New-CsOnlineSession
Import-PSSession $CSSession -AllowClobber
If ($Null -ne (Get-CsOnlineDirectoryTenant))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Skype for Business"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Skype for Business"
}
}
}
Else
{
$CSSession = New-CsOnlineSession -Credential $Credential
Import-PSSession $CSSession -AllowClobber
If ($Null -ne (Get-CsOnlineDirectoryTenant))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Skype for Business"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Skype for Business"
}
}
}
}
continue
}
Teams {
If ($null -eq (Get-Module @getModuleSplat -Name "MicrosoftTeams"))
{
Write-Error "MicrosoftTeams Module is not present!"
}
Else
{
Write-Host "Connecting to Teams"
If ($MFA -eq $True)
{
$TeamsConnect = Connect-MicrosoftTeams
If ($Null -ne ($TeamsConnect))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Microsoft Teams"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Microsoft Teams"
}
}
}
Else
{
$TeamsConnect = Connect-MicrosoftTeams -Credential $Credential
If ($Null -ne ($TeamsConnect))
{
If (($host.ui.RawUI.WindowTitle) -notlike "*Connected To:*")
{
$host.ui.RawUI.WindowTitle += " - Connected To: Microsoft Teams"
}
Else
{
$host.ui.RawUI.WindowTitle += " - Microsoft Teams"
}
}
}
}
continue
}
Default { }
}
}
}
Function Get-Office365Modules
{
#Check if connected to a tenant
if (Get-Module -ListAvailable -Name AzureAD) {
Write-Host "AzureAD Module Installed!"
}
else {
Write-Host "Installing AzureAD Module!"
    Install-Module AzureAD -Confirm:$False -Force
}
if (Get-Module -ListAvailable -Name MsOnline) {
Write-Host "MsOnline Module Installed!"
}
else {
Write-Host "Installing MsOnline Module!"
    Install-Module MsOnline -Confirm:$False -Force
}
if (Get-Module -ListAvailable -Name Microsoft.Online.SharePoint.PowerShell) {
Write-Host "SharePoint Module Installed!"
}
else {
Write-Host "Installing SharePoint Module!"
    Install-Module -Name Microsoft.Online.SharePoint.PowerShell -Confirm:$False -Force
}
}
function New-RoomMailbox
{
Write-Host ""
$RoomName = Read-Host "Room Mailbox Display Name"
$RoomAlias = Read-Host "Room Alias to be used in email address(ex: conf.chiefs)"
$RoomDomain = Read-Host "Room email domain (ex: 321moonshot.com)"
$RoomAddress = "$RoomAlias@$RoomDomain"
$RoomPass = Read-Host "Room Mailbox Password"
New-Mailbox -Name "$RoomName" -Alias $RoomAlias -Room -EnableRoomMailboxAccount $true -MicrosoftOnlineServicesID $RoomAddress -RoomMailboxPassword (ConvertTo-SecureString -String '$RoomPass' -AsPlainText -Force)
Write-Host ""
}
function Get-RoomLists
{
Get-DistributionGroup -ResultSize Unlimited | Where-Object {$_.RecipientTypeDetails -eq "RoomList"} | Format-Table DisplayName,PrimarySmtpAddress -AutoSize
}
function New-RoomList
{
$NewListName = Read-Host "Display Name for New Room List"
New-DistributionGroup -Name "$NewListName" -RoomList
}
function Add-RoomListMember
{
$RoomAddress = Read-Host "Room Mailbox email address or alias"
$ListName = Read-Host "Display Name of Room List"
Add-DistributionGroupMember -Identity "$ListName" -Member "$RoomAddress"
}
function Update-Moonshot365
{
Write-Host "Checking Moonshot365 Version...."
$Installed = Get-Module -Name Moonshot365
$online = Find-Module -Name Moonshot365
$online | Where-Object {$installed.name -contains $_.name} |
                Select-Object -property Name,
            @{Name = "OnlineVersion"; Expression = {$_.Version}},
            @{Name = "InstalledVersion"; Expression = {
                    #save the name from the incoming online object
                    $name = $_.Name
                    $installed.Where( {$_.name -eq $name}).Version -join ","}
            },
            PublishedDate,
            @{Name = "UpdateNeeded"; Expression = {
                    $name = $_.Name
                    #there could me multiple versions installed
                    $installedVersions = $installed.Where( {$_.name -eq $name}).Version | Sort-Object
                    foreach ($item in $installedVersions) {
                        If ([version]$_.Version -gt [version]$item) {
                            $result = $True
                            Write-Host "Version Bad. Updating!"
                            Remove-Module Moonshot365
                            Update-Module Moonshot365
                            Write-Host "Updated!"
                            Write-Host ""
                            Write-Host ""
                            Import-Module Moonshot365
                            
                        }
                        else {
                            $result = $False
                            
                            Write-Host "Version good!"
                            Write-Host ""
                            Write-Host ""
                            
                        }
                    }
                    $result
                }
            }
}
Export-ModuleMember -Function New-365Report, Connect-Office365, Get-Office365Modules, New-RoomMailbox, Get-RoomLists, New-RoomList, Add-RoomListMember, Update-Moonshot365
$InstalledVersion = Get-Module -Name Moonshot365 | Select -expandproperty Version
$WebVersion = Find-Module -Name Moonshot365 | Select -expandproperty Version
Write-Host ""
Write-Host "---------------------------------------->"
Write-Host "" Installed Version: $InstalledVersion""
Write-Host "" Latest Version: $WebVersion""
Write-Host ""
Write-Host " Current Moonshot365 Commands: New-365Report, Connect-Office365, Get-Office365Modules, New-RoomMailbox, Get-RoomLists, New-RoomList, Add-RoomListMember, Update-Moonshot365" -ForegroundColor Yellow
Write-Host "---------------------------------------->"
Write-Host ""