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

Your PowerShell script, where two Office applications - Excel and Outlook are automated, looks good and I don't see anything strange there. Be aware, you can't run two instances of Outlook processes at the same time (unlike Excel), so if it was already run you will get a running instance. That means if you have Outlook already opened and running by a user (without administrative privileges) your code run form a command line (or any other shell) with admin privileges will not be able to connect to the running Outlook instance and contacts will not be added.

Within PowerShell (run as Administrator), the script runs without throwing any errors.

Make sure that you run all applications and the PowerShell script under the same security context to be able to add contacts in Outlook. That means Outlook should be closed (not running) when you run a script as an administrator.

Answer 2

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.