Home
Populate missing email address for Sharepoint users who are guests
Office 365 (SharePoint) Monday, 23 November 2020 by paul

There is an issue with SharePoint Guest users who are invited that already present in Exchange Online GAL as a contact. The email address does not get synchronised to the SharePoint user profile from Azure AD. This is a known issue but there is no fix. A workaround is to run the following Microsoft support PowerShell scripts to populate the missing email information.

Main script - Update-ExternalUsersWithNoUPAEmail.ps1

# ==========================================================================================================#
# This script uses Azure AD owerShell annd SPO CSOM to report on or update the WorkEmail Address in the UPA #
# ==========================================================================================================#

# This script requires the pre-installation of the Azure AD PowerShell tools: https://docs.microsoft.com/en-us/office365/enterprise/powershell/connect-to-office-365-powershell 
# This script requires the pre-installation of the SPO Client Components SDK: https://www.microsoft.com/en-gb/download/details.aspx?id=42038 

# Edit this line to point to your tenant SPO Admincenter URL
$SPOTenantAdminUrl="https://yourtenant-admin.sharepoint.com";

# Output file path - edit to point to where the outpuut files should be saved
$outputPath = "C:\temp\GuestUserEmailChangesRequired " + (get-date -format "dd MMM yyyy HH-mm-ss") + ".csv"

# This option should be set to $true in order to apply/sync the changes. When set to $false no changes to data will be made (the script will be Read Only)
$Sync=$true;

# Limits the number of fixes per running of the script.  $Sync needs to be to $true for this to have an effect.  Default is 10. 
$maxFixes = 1000

# This option should be set to $true in order to sync an email address when there are multiple entries in the Alternative Email Address field
$SyncMulti=$false;

# This option should be set to decided which position of Alternative Email Address should be sync'd when multiple values exist.  Zero (0) is the first item, one (1) would be the second value ect.
$SyncMultiPos = 0

# Choose this option output all Guest Users without email Addresses to the screen. Set the value to $true to show or $false to hide.
$FullResultsToScreen = $false

# Choose this option output all Guest Users that need to be synced to the screen. Set the value to $true to show or $false to hide.
$SyncNeededResultsToScreen = $true


# These credential properties can be left as empty and you will then be asked for credentials when you run the script.  It can be useful to add the admin account upn.
$SPOAdminUser = "";
$SPOAdminPassword = "";

Write-Host ""
Write-Host "###################"
Write-Host "Starting the script"
Write-Host "###################"
Write-Host ""

#http://blogs.msdn.com/b/powershell/archive/2007/06/19/get-scriptdirectory.aspx
function Get-ScriptDirectory
{
 $Invocation = (Get-Variable MyInvocation -Scope 1).Value
 if ($Invocation.MyCommand.Path)
 {
    Split-Path $Invocation.MyCommand.Path
 }
 else
 {
    (Get-Item -Path ".\" -Verbose).FullName
 }
}

$path = Join-Path (Get-ScriptDirectory) CSOM_Helper.ps1
. $path

# Get the user credentials
$cred = Get-Credential -Message "Enter username/password for $SPOTenantAdminUrl." -UserName $SPOAdminUser
$credentials = New-Object System.Management.Automation.PSCredential($cred.UserName, $cred.password)

$connectError = @()
Connect-MsolService -Credential $credentials -ErrorVariable connectError -ErrorAction SilentlyContinue
 if ($connectError.count -ne 0 ) {
    Write-Error "Error connecting to MSOLService - $connectError"   
    return
}

$numberNeedsSync = 0
$numberSynced = 0
$searchResults = @()
$searchResultsNeedsSync = @()

$externalusers =  Get-MsolUser -all | Where {$_.UserType -eq 'Guest' -and -not($_.ProxyAddresses -like "*")}         # This finds the Guest Users that do not have a Primary SMTP Address
Write-Host ""
Write-Host "Found $($externalusers.Count) External users in Azure AD that do not have a entry in the ProxyAddresses Property." -ForegroundColor Green

Write-Host ""
Write-Host "'maxFixes' is set to $($maxFixes) so only the first $($maxFixes) Guest Users missing a WorkEmail in the UPA will be checked." -ForegroundColor Green


# Check that there are enough users to process up to the maxFixes value
if ($externalusers.Count -le $maxFixes)
{
    $maxFixes = $externalusers.Count
    Write-Host ""
    Write-Host "The number of External Users found is less than the 'maxFixes' value - setting the 'maxFixes' value to " $($externalusers.Count) -ForegroundColor Yellow
}


#  Just adding some output
if ($Sync -eq $false -and $FullResultsToScreen)
    {
        Write-Host ""
        Write-Host "Here are all the current values for Guest user in Azure AD and the UPA inc status:" -ForegroundColor Green
    }
    elseif($Sync -eq $false -and $SyncNeededResultsToScreen)
    {
        Write-Host ""
        Write-Host "Here are the current values for Guest users that need to sync the WorkEmail address:" -ForegroundColor Green
    }

# Set up the loop for maxFixes

$i = 0
$j = 0
$finished = $false

# Loop while there are external users left to process and $maxFixes is not reached
While ($i -le $externalusers.Count -and $finished -eq $false)   
{
    if ($j -le $maxFixes - 1)
    
    { 
        $user = $externalusers[$i]
    
        # Creates the results object
        $extUserDetails = New-Object PSObject
        $extUserDetails | Add-Member -MemberType NoteProperty -Name UPN -Value $($user.UserPrincipalName) -Force
        $extUserDetails | Add-Member -MemberType NoteProperty -Name AAD_ProxyAddresses -Value $($user.ProxyAddresses) -Force
        $extUserDetails | Add-Member -MemberType NoteProperty -Name AAD_AlternateEmailAddresses -Value $($user.AlternateEmailAddresses) -Force


        # Uses CSOM to get the WorkEmail value from the User Profile
        
        $ctx = (Get-context $SPOTenantAdminUrl -username $credentials.UserName -password $credentials.Password)
        $peopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($ctx)
        $accountName = 'i:0#.f|membership|' + $user.UserPrincipalName
        $UPAProfile = $peopleManager.GetPropertiesFor($accountName)
        $UPAProfile.Context.Load($UPAProfile)
        $UPAProfile.Context.ExecuteQuery()
    

        # Checks if a Profile for the user exists in the UPA
        if ($UPAProfile.ServerObjectIsNull)
        {
            # No Profile exists in UPA
            Write-Warning "No profile exists in UPA for user $accountName"
            $extUserDetails | Add-Member -MemberType NoteProperty -Name AAD_AlternateEmailAddresses -Value $null -Force
            $extUserDetails | Add-Member -MemberType NoteProperty -Name UPA_WorkEmail -Value $null -Force
            $extUserDetails | Add-Member -MemberType NoteProperty -Name NeedsSynced -Value $null -Force
        }
        else
        {
            $emailPropertyName = "WorkEmail"
            $extUserDetails | Add-Member -MemberType NoteProperty -Name UPA_WorkEmail -Value $($UPAProfile.UserProfileProperties[$emailPropertyName]) -Force
            $UPAEmailNeedsSynced = $false
            if (($user.ProxyAddresses.Count -eq 0) -and $($UPAProfile.UserProfileProperties[$emailPropertyName]).Contains('#EXT#') -or ([System.String]::IsNullOrEmpty($($UPAProfile.UserProfileProperties[$emailPropertyName]))))
            {
                # Proxy Address is blank (most likely due to another object in Azure AD with same email) AND Email is not set in UPA
                $UPAEmailNeedsSynced = $true
                $j++
            }
            $extUserDetails | Add-Member -MemberType NoteProperty -Name NeedsSynced -Value $UPAEmailNeedsSynced -Force
        
            # Checks if $Sync is set to true and if so it will update the value in the UPA
            if ($UPAEmailNeedsSynced -and $Sync)
            {
                if ($user.AlternateEmailAddresses.Count -eq 1)
                {
                    if (-not [System.String]::IsNullOrEmpty($($user.AlternateEmailAddresses[0])))
                    {
                        $peopleManager.SetSingleValueProfileProperty($accountName,$emailPropertyName,$user.AlternateEmailAddresses[0])
                        $peopleManager.Context.ExecuteQuery()
                        Write-Host ""
                        Write-Host "Successfully set WorkEmail in UPA for user $accountName to $($user.AlternateEmailAddresses[0])" -ForegroundColor Cyan
                        Write-Host ""
                        $extUserDetails | Add-Member -MemberType NoteProperty -Name NeedsSynced -Value "Has been Synced" -Force
                        $extUserDetails | Add-Member -MemberType NoteProperty -Name UPA_WorkEmail -Value $($user.AlternateEmailAddresses[0]) -Force
                        $numberSynced++
                    }
                    else
                    {
                        Write-Warning "AlternateEmailAddresses is not set for user $($user.UserPrincipalName) in Azure AD. Please reinvite the external user or set manually using Set-MSOLUser -AlternateEmailAddresses"
                    }
                
                }
                Elseif ($user.AlternateEmailAddresses.Count -gt 1 -and $SyncMulti)
                {
                    if (-not [System.String]::IsNullOrEmpty($($user.AlternateEmailAddresses[$SyncMultiPos])))
                    {
                        $peopleManager.SetSingleValueProfileProperty($accountName,$emailPropertyName,$user.AlternateEmailAddresses[$SyncMultiPos])
                        $peopleManager.Context.ExecuteQuery()
                        Write-Host "Multiple Alternative Email Addresses found for user $accountName. Successfully set WorkEmail in UPA to $($user.AlternateEmailAddresses[$SyncMultiPos])"
                        $numberSynced++
                    }
                    else
                    {
                        Write-Warning "AlternateEmailAddresses is not set for user $($user.UserPrincipalName) in Azure AD. Please reinvite the external user or set manually using Set-MSOLUser -AlternateEmailAddresses"
                    }
                }
                else
                {
                    Write-Host ""
                    Write-Warning "Found multiple AlternateEmailAddresses set in Azure AD for user $($user.UserPrincipalName). Email will not be synced (Decision needed on which email address to Sync)."
                    Write-Host -NoNewline "Email addresses are: "
                    for ($k=0; $k -le $user.AlternateEmailAddresses.Count - 1; $k++) 
                    {
                        Write-Host -NoNewline $user.AlternateEmailAddresses[$k] "  "
                    }
                    Write-Host ""
                    $numberNeedsSync ++
                }
            }
        }
        if ($Sync -eq $false -and $UPAEmailNeedsSynced)  # Add results only if $sync is false and Sync is Needed
        {
            $searchResultsNeedsSync += $extUserDetails
            $numberNeedsSync ++
        }

        # Add to current results set
        $searchResults += $extUserDetails

        if ($Sync -eq $false -and $FullResultsToScreen)
        {
            # Writes details to the screen
            $extUserDetails
        }

        if ($Sync -eq $false -and $SyncNeededResultsToScreen)
        {
            if ($UPAEmailNeedsSynced)
            {
                # Writes details to the screen
                $extUserDetails
            }
        }

        if ($j -eq $maxFixes)
        {
            $finished = $true
        }
    }
    $i++
}

$searchResults | Export-Csv -Path $outputPath -NoTypeInformation -Append

if(!$sync)
{
    Write-Host ""
    Write-Host "The number of Guest users in this batch that require a sync of the WorkEmail address is: " $numberNeedsSync -ForegroundColor Green
    Write-Host ""
}

if($sync)
{
    Write-Host ""
    Write-Host "The number of Guest users in this batch that were synced is: " $numberSynced -ForegroundColor Green
    Write-Host ""
}
Write-Host "Output file has been written to " $outputPath
Write-Host ""
Write-Host "Script completed executing at " $(Get-Date)-ForegroundColor Green
Write-Host ""

Called by previous script - CSOM_Helper.ps1

#Definition of the function that gets the list of site collections in the tenant using CSOM



function Load-AzureADModule
{
    [email protected]()
    Write-Host "Importing module MSOnline"
    Import-module MSOnline -ErrorAction SilentlyContinue -ErrorVariable moduleerror
    if ($moduleerror.count -ne 0 ) {
	    Write-Host "Attempting to import MSOnline module from default install location 'C:\Windows\System32\WindowsPowerShell\v1.0\Modules\MSOnline'"
	    Import-module "C:\Windows\System32\WindowsPowerShell\v1.0\Modules\MSOnline"	-ErrorAction SilentlyContinue -ErrorVariable moduleerror
	    if ($moduleerror.count -ne 0 ) {
		    Write-Error $moduleerror
		    return;
	    }
    }
}

function Load-SPOModule
{
    [email protected]()
    Write-Host "Importing module Microsoft.Online.SharePoint.PowerShell"
    Import-module Microsoft.Online.SharePoint.PowerShell  -ErrorAction SilentlyContinue -ErrorVariable moduleerror
    if ($moduleerror.count -ne 0 ) {
	    Write-Host "Attempting to import Microsoft.Online.SharePoint.PowerShell module from default install location 'C:\Program Files\SharePoint Online Management Shell\'"
	    Import-module "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell"	-ErrorAction SilentlyContinue -ErrorVariable moduleerror
	    if ($moduleerror.count -ne 0 ) {
		    Write-Error $moduleerror
		    return;
	    }
    }
}

function Load-CSOMModule
{
    $loadInfo1 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
    if ($loadInfo1 -eq $null) {
        Write-Warning "SharePoint Online client components and/or powershell 3.0 are missing."
        write-host "Please download SPO management shell and client components and re-run the script:`r`n`r`nhttp://www.microsoft.com/en-us/download/details.aspx?id=35588`r`nhttp://www.microsoft.com/en-in/download/details.aspx?id=42038"
        return;
    }
    $loadInfo2 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
    if ($loadInfo2 -eq $null) {
        Write-Warning "SharePoint Online client components and/or powershell 3.0 are missing."
        write-host "Please download SPO management shell and client components and re-run the script:`r`n`r`nhttp://www.microsoft.com/en-us/download/details.aspx?id=35588`r`nhttp://www.microsoft.com/en-in/download/details.aspx?id=42038"
        return;
    }
    
    #$loadInfo3 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Online.SharePoint.Client.Tenant")
    #if ($loadInfo3 -eq $null) {
    #    Write-Warning "SharePoint Online client components and/or powershell 3.0 are missing."
    #    write-host "Please download SPO management shell and client components and re-run the script:`r`n`r`nhttp://www.microsoft.com/en-us/download/details.aspx?id=35588`r`nhttp://www.microsoft.com/en-in/download/details.aspx?id=42038"
    #    return;
    #}
    $loadInfo4 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.UserProfiles")
    if ($loadInfo4 -eq $null) {
        Write-Warning "SharePoint Online client components and/or powershell 3.0 are missing."
        write-host "Please download SPO management shell and client components and re-run the script:`r`n`r`nhttp://www.microsoft.com/en-us/download/details.aspx?id=35588`r`nhttp://www.microsoft.com/en-in/download/details.aspx?id=42038"
        return;
    }
}


function Get-SPOTenantSiteCollections
{
    param ($sSiteUrl,$sUserName,$sPassword)
    try
    {    
        Write-Host "----------------------------------------------------------------------------"  -foregroundcolor Green
        Write-Host "Getting the Tenant Site Collections" -foregroundcolor Green
        Write-Host "----------------------------------------------------------------------------"  -foregroundcolor Green
     
        #REQUIREMENTS - # SharePoint 2013 Client Components SDK - http://www.microsoft.com/en-us/download/details.aspx?id=35585
        $loadInfo1 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
        $loadInfo2 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")
        $loadInfo3 = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Online.SharePoint.Client.Tenant")
        #$loadInfo4 = [System.Reflection.Assembly]::LoadFile("C:\Program Files\SharePoint Client Components\16.0\Assemblies\Microsoft.Online.SharePoint.Client.Tenant.dll")

        #SPO Client Object Model Context
        $spoCtx = New-Object Microsoft.SharePoint.Client.ClientContext($sSiteUrl) 
        $spoCredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($sUserName, $sPassword)  
        $spoCtx.Credentials = $spoCredentials
        $spoTenant= New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($spoCtx)
        $spoTenantSiteCollections=$spoTenant.GetSiteProperties(0,$true)
        $spoCtx.Load($spoTenantSiteCollections)
        $spoCtx.ExecuteQuery()
        
        return $spoTenantSiteCollections
        #We need to iterate through the $spoTenantSiteCollections object to get the information of each individual Site Collection
        #foreach($spoSiteCollection in $spoTenantSiteCollections){
            
        #    Write-Host "Url: " $spoSiteCollection.Url " - Template: " $spoSiteCollection.Template " - Owner: "  $spoSiteCollection.Owner
        #}
        $spoCtx.Dispose()
    }
    catch [System.Exception]
    {
        write-host -f red $_.Exception.ToString()   
    }    
}


#region Globals
$global:allwebs = @()
#endregion

#region Functions
function Get-Context
{
	[CmdletBinding()]
	param
	(
		[Parameter(Mandatory = $true)]
		[string]$siteurl,

        [Parameter(Mandatory=$false)]
        [string]$username = "",

        [Parameter(Mandatory=$false)]
        $password
	)
	
	[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
	[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")

    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"
    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
    #Add-Type -Path "C:\Program Files\WindowsPowerShell\Modules\SharePointPnPPowerShellOnline\3.26.2010.0\Microsoft.SharePoint.Client.dll"
    #Add-Type -Path "C:\Program Files\WindowsPowerShell\Modules\SharePointPnPPowerShellOnline\3.26.2010.0\Microsoft.SharePoint.Client.UserProfiles.dll"
    #Add-Type -Path "C:\Program Files\WindowsPowerShell\Modules\SharePointPnPPowerShellOnline\3.26.2010.0\Microsoft.SharePoint.Client.Runtime.dll"

	$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteurl)

    if (([System.String]::IsNullOrEmpty($username)) -or ([System.String]::IsNullOrEmpty($password)))
    {
        
        $cred = Get-Credential -Message "Enter username/password for $siteurl." -UserName $username
        $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.UserName, $cred.password)
    }
    else
    {
        if ($password.GetType() -eq [System.Security.SecureString])
        {
            $password_secure = $password
        }
        else
        {
            $password_secure = ConvertTo-SecureString $password -AsPlainText -Force
        }
        $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password_secure)
        #$credentials = New-Object System.Management.Automation.PSCredential($username, $password_secure)
    }

	$ctx.Credentials = $credentials
	$ctx.RequestTimeOut = 5000*60*10
	return $ctx
}
function Get-siteFromContext
{
	[CmdletBinding()]
	param
	(
		[Parameter(Mandatory=$True,ValueFromPipeline=$True,ValueFromPipelinebyPropertyName=$True)]
		$ctx
	)
	
	$site = $ctx.site
	$result = $ctx.load($site)
	$result = $ctx.executequery()
	return $site
}
function Get-allSubwebs
{
	[CmdletBinding()]
	param
	(
        [Parameter(Mandatory = $true)]
		$parentweb
	)

    $nextlevelWebs = @()	

	$subwebs = $parentweb.webs
    $result = $parentweb.Context.load($subwebs)
	$result = $parentweb.Context.load($parentweb)
	$result = $parentweb.Context.executequery()
    $nextlevelWebs += $parentweb;
	if ($subwebs.Count -gt 0)
	{
		foreach ($subweb in $subwebs)
		{
			$nextlevelWebs += Get-allwebsFromRootWeb -ctx $ctx -thisweb $subweb    
		}
	}
    return $nextlevelWebs;
}

function Get-rootweb
{
	[CmdletBinding()]
	param
	(
		$site
	)
	
	$rootweb = $site.rootweb
	$result = $site.Context.load($rootweb)
	$result = $site.Context.executequery()
	return $rootweb
}

function Get-allListsInWeb
{
	[CmdletBinding()]
	param
	(
        [Parameter(Mandatory = $true)]
        $web
	)
	
	$lists = $web.lists
	$result = $web.Context.load($lists)
	$result = $web.Context.executequery()
	return $lists
}

function Get-allLibraries
{
	[CmdletBinding()]
	param
	(
        [Parameter(Mandatory = $true)]
        $web
	)
	
	$doclibsinfo = [Microsoft.SharePoint.Client.Web]::GetDocumentLibraries($web.Context,$web.Url)
	$result = $web.Context.ExecuteQuery()
	return $doclibsinfo
}

function Get-List
{
	[CmdletBinding()]
	param
	(
        [Parameter(Mandatory = $true)]
		$web,
        [Parameter(Mandatory = $true)]
        $listUrl
	)
	
	$list = $web.GetList($listurl)
	$result = $web.Context.load($list)
	$result = $web.Context.executequery()
	return $list
}

function Get-ListByTitle
{
	[CmdletBinding()]
	param
	(
        [Parameter(Mandatory = $true)]
		$web,
        [Parameter(Mandatory = $true)]
        $listTitle
	)
	
	$list = $web.Lists.GetByTitle($listTitle)
	$result = $web.Context.load($list)
	$result = $web.Context.executequery()
	return $list
}

function Get-allContentTypesInList
{
	[CmdletBinding()]
	param
	(
        [Parameter(Mandatory = $true)]
		$list
	)
	
	$ctypes = $list.contenttypes
	$result = $list.Context.load($ctypes)
	$result = $list.Context.executequery()
	return $ctypes
}

function Get-listFieldById
{
	[CmdletBinding()]
	param
	(
		$list,
		$fieldId
		
	)
	
	$field = $null
	$field = $list.fields.GetById($fieldId)
	$list.Context.load($field)
	$list.Context.executequery()
	return $field
}

#endregion

Prerequisite is to have SharePoint Client Components installed: https://www.microsoft.com/en-us/download/details.aspx?id=51679

After running the script the SharePoint users should be updated.

Thanks to Microsoft's Nigel Swain and Joel Santos for writing the scripts.


Add Comment
No Comments.