Creating Outlook Mail Drafts from Excel in Office 365

1
votes

I have an existing VBA code snippet in Excel we used to create Outlook mail drafts in Office 2013. Consider the following code which worked fine and allowed the mails to be manually sent within Outlook after creation:

Dim objOLOutlook As New Outlook.Application
Dim objOLMail As Outlook.MailItem

Set objOLMail = objOLOutlook.CreateItem(olMailItem)
objOLMail.SentOnBehalfOfName = WsMtrx.Cells(2, 2).Value
objOLMail.To = .Cells(i, 2).Value
objOLMail.CC = .Cells(i, 3).Value
objOLMail.Sensitivity = WsMtrx.Cells(3, 2).Value
objOLMail.Importance = WsMtrx.Cells(4, 2).Value
objOLMail.Subject = dicHtmlText.Item(.Cells(i, 6).Value & "Subject")
objOLMail.BodyFormat = olFormatPlain
objOLMail.HTMLBody = getHtmlText(.Cells(i, 6).Value, .Cells(i, 5).Value, .Cells(i, 4).Value, .Cells(i, 10).Value, i, arrSignatur)
objOLMail.Save

Now we've switched to Office 365 (Exchange server in the Cloud) and while the same code still works to create the mails, sending the mails will result in an error:

Outlook error message

I've noticed that the Outlook properties will show the e-mail address in the "display name" field only instead of the "e-mail address" field:

Outlook properties

Whereas properly working manually created mails will show the e-mail address in the "display name" field as well as the "e-mail address" field and show "SMTP" in the "e-mail type" field:

Outlook properties

I've already tried several things including clearing the autocomplete cache to no avail. Is there anything I can do within Excel/VBA to set the e-mail address properly? Or is it rather a server side issue/setting that must be fixed?

1
excelvbaoutlookoffice365mailitem

1 Answers

1
votes

I've finally found the solution myself:

Dim recipient As Outlook.recipient

For Each recipient In objOLMail.Recipients
    recipient.Resolve
Next

objOLMail.Save

Rather simple actually. Somehow the way things are set up I now need to explicitly resolve every recipient, which wasn't necessary previously.