Remove aliases for non-accepted domains

This describes how to fix the problem when a migration batch contains a mailbox that has an alias that isn’t in the Office 365 tenant and needs to be removed from the mailbox on-prem. If you only have a small number of mailboxes with errors then you can easily do this manually be removing the alias. If you have a large number of mailboxes simply removing the domains from the email address policy doesn’t remove the alias address from the mailboxes. You still need to run the command from the Exchange Management Shell:

Mailbox -EmailAddresses @{remove=''}

In order to script this out from Exchange On-Prem I first run an export of all mailboxes capturing all the email addresses of the mailboxes:

Login to the On-Prem Exchange server and run the Exchange Management Shell

Create a new folder called “Export” on the C:\ drive.

Run the following commands:

$mailboxes = Get-Mailbox -ResultSize Unlimited
$mailboxes | Select-Object RecipientTypeDetails,PrimarySmtpAddress -ExpandProperty emailaddresses | select RecipientTypeDetails,PrimarySmtpAddress, @{name="Type";expression={$_}} | Export-Csv C:\Export\Mailboxes-Emailaddresses.csv -NoTypeInformation

Now we need to open the Excel file and make some modifications:

Firstly we need to copy the Column C and paste it into Column E, then rename the header AliasAddress

Next we need to perform a split on Column C, select the entire column, click the Data ribbon of Excel and Text to Columns button. Choose the file type Delimited and click Next. Deselect all options except Other and enter the symbol for colon “:” and click Finish. Label the Column D “Alias”.

Add one more Column after the C column and label it Primary. Enter the following formula in the cell for D2 “=IF(ISNUMBER(FIND(“SMTP”,C2)),”Primary”, “Alternate”)” and copy the formula down through all the Rows.

Now highlight Row 1, click the Home ribbon, click Sort & Filter and select Filter.

We should now have something that looks like this:

Each Row represents an address for the mailbox and we can now remove any addresses that will remain on the mailbox. The idea is to have a file with only addresses that should be removed from mailboxes.

Filter on Column D and only show those with a value of Primary, select all rows and delete the rows, clear the filter on Column D.

Filter on Column C and select X500 and X400 and delete all rows with those values, clear the filter on Column C. We should be left with only values that have lowercase smtp.

Now we are doing to  create a Column to that will allow us to filter by domain name. Highlight Column F “AliasAddress” and click the Data ribbon and select Text to Columns. Select Delimited, click Next and this time select Other with the symbol “@” and click Finish. We now have a Column G that can be labeled “Domain” and it will have values for the domain name. Filter the Column G “Domain” to only show domains that are verified in the Office 365 tenant. You want to delete these rows from the spreadsheet.

The result is now an Excel spreadsheet that contains only smtp alias addresses for mailboxes that will be removed from Exchange On-Prem. Save the Mailboxes-Emailaddresses.csv file.

Run the following script on the On-Prem Exchange Server to remove the smtp aliases on the mailboxes:

$mailboxes = Import-Csv "c:\Export\Mailboxes-Emailaddresses.csv"
ForEach ($Mailbox in $Mailboxes){
		$User = $Mailbox.PrimarySMTPAddress
		$Alias = $Mailbox.Alias
		write-host "Removing $Alias from $User"
		Set-Mailbox "$User" -EmailAddresses @{remove="$alias"}