When creating an Outlook email through VBA in Excel, this now results in error 287; more specifically on getting a string value from .HTMLbody. This problem now appears when we moved to the 365 version. Any thoughts?

Question

The goal is to create an email, using the contents of an Excel sheet. The email should contain the Outlook signature (text and pictures) of the user that is working with the Excel sheet at that time (that can vary). So first the signature is supposed to be captured (from HTMLbody) and next additional content is to be added, derived from the Excel sheet.

This worked before but now, with O365 it results in error 287.

This is the relevant code:

Sub mailing(van, aan, CC, BCC, subj, body, att)

Dim OApp As Object, OMail As Object, signature As String

Set OApp = CreateObject("Outlook.Application")

Set OMail = OApp.CreateItem(0)

'NOW not working therefore commented

'With OMail

'.Display

'End With

'signature = OMail.HTMLBody <-- This causes problems

'end of commented

With OMail

.SentOnBehalfOfName = van

.To = aan

.CC = CC

.BCC = BCC

.Subject = subj

.HTMLBody = body

If att = "" Then

Else

 .Attachments.Add att

End If

.Display

'NOW not working therefore commented

'.HTMLBody = body & signature

'end of commented

End With

Set OMail = Nothing

Set OApp = Nothing

End Sub

Answer 1

Thanks for reaching out! you're facing an issue with your VBA code for Outlook in Excel, particularly after upgrading to Office 365. Specifically, you’re encountering Error 287 when trying to get the signature via .HTMLBody. This error often arises due to security changes in Outlook related to programmatic access.

1. Use WordEditor for HTML Manipulation: Instead of directly manipulating the .HTMLBody property, consider using the WordEditor object. This method allows you to maintain the formatting in your email signature.
2. Test Error Handling: Implement error handling in your code to better understand if any specific line is causing the failure.
3. Update Office Applications: Ensure that your Office applications are up to date, as updates can resolve compatibility issues. Go to File > Account > Update Options > Update Now.
4. Consult Known Issues: Since you’ve moved to Office 365, review any known issues related to your version that may affect VBA scripting or Outlook functionality.

Answer 2

Thanks for the alternative approach, much appreciated!