Excel to new Outlook Contact: correct data but not reading in

Question

I’ve written the below script. Its job is to take a (well-formatted) Excel file and, row by row, create new Contacts in Outlook.

Am running Windows 11 with everything recently updated (this morning, actually).

Within PowerShell (run as Administrator), the script runs without throwing any errors. The data outputted to the screen indicate the Excel file is being read correctly.

In Outlook, new contacts are created.

However, no data at all is being read into the contacts. Would appreciate any explanation as to what I've missed. Maybe the new contacts aren't being saved correctly to retain the data?

# Create an Excel application object
$excel = New-Object -ComObject Excel.Application

# Open the workbook
$workbook = $excel.Workbooks.Open("C:\Users\DW-ECM\Scripts\test_xl.xlsx")

# Select the first worksheet
$worksheet = $workbook.Worksheets.Item(1)

# Define the range of cells that contain the data
$range = $worksheet.UsedRange

# Creates a new instance of the Outlook application object 
# using the COM (Component Object Model) interface in PowerShell
$outlook = New-Object -ComObject Outlook.Application

Write-Host "Selected workbook: $($workbook.Name)"
Write-Host "Selected worksheet: $($worksheet.Name)"

# Loop through each row in the range
for ($i = 2; $i -le $range.Rows.Count; $i++) {
    # Get the current row
    $row = $range.Rows.Item($i)
    
    # Create a new contact object
    $contact = $outlook.CreateItem(2)

    # Set the properties of the contact object
    $contact.Account = $row.Cells.Item(1).Value2
    $contact.Business2TelephoneNumber = $row.Cells.Item(2).Value2
    $contact.BusinessAddress = $row.Cells.Item(3).Value2
    $contact.CompanyName = $row.Cells.Item(4).Value2
    $contact.Department = $row.Cells.Item(5).Value2
    $contact.Email1Address = $row.Cells.Item(6).Value2
    $contact.FirstName = $row.Cells.Item(7).Value2
    $contact.JobTitle = $row.Cells.Item(8).Value2
    $contact.LastName = $row.Cells.Item(9).Value2
    $contact.MobileTelephoneNumber = $row.Cells.Item(10).Value2
    $contact.User1 = $row.Cells.Item(11).Value2
    $contact.User2 = $row.Cells.Item(12).Value2
    
    # Save the contact to Outlook
    $contact.Save()

    Write-Host "Creating contact for $($row.Cells.Item(8).Value2) $($row.Cells.Item(10).Value2)"
}

# Close the workbook and quit Excel
$workbook.Close()
$excel.Quit()

# Save and close the Outlook instance
$outlook.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($outlook)
Remove-Variable outlook

# Stop the Outlook process
Stop-Process -ProcessName OUTLOOK -Force

*Edited for clarity of what I'm asking for help with and a typo.

Answer 1

Running as an admin is a bad idea - Outlook is a singleton, and if it is already running, your script will attach to the existing instance. And COM system will refuse to marshal calls between two apps (PS and Outlook) running in different security contexts.