Using Excel VBA to create email in Outlook 2010 from template

Question

I have code that worked well in Excel/Outlook 2003 on XP, I'm now running Windows 7 Excel/Outlook 2010 and receive an error:

Run-time error '287': Application-defined or object-defined error.

My code is based on this answer in: Send an email from Excel 2007 VBA using an Outlook Template & Set Variables

I'm creating my outlook object and mailitem like this:

Dim MyOlApp
Dim MyOlItem
Set MyOlApp = CreateObject("Outlook.Application")
Set MyOlItem = MyOlApp.CreateItemFromTemplate(Range("oftLocation").Value) 'user defined location

The error appears after I reference MyOlItem, the code is replacing references in the .htmlBody

Amending to .Body makes no difference, same error shows.

My references in VBA are set to:

• VBA
• Microsoft Excel 14.0 Object Library
• OLE Automation
• Microsoft Office 14.0 Object Library
• Microsoft Forms 2.0 Object Library
• Microsoft Scripting Runtime

Answer 1

This isnt an answer yet but I want to collect what is happening in the comments since I feel this is the best direction with what I have seen thus far.

I have tested the code in you have in your question and it works just fine for me using Outlook 2010 and Excel 2010.

When Excel tries to interact with Outlook the Programmatic Access settings come into play.

I beleive they are setting to a warning state if you AntiVirus is out of date. This is similar to the way that Windows detects the status of your AntiVirus. So when you access Outlook from Excel normally get a prompt asking permissiong for the action. In my case, and your under normal circumstances, you would allow the action and the code would proceed as normal.

However I have seen people catch error 287 with the logic that the user hit No to that prompt. This does not appear to be the case for you. You need to take a good look at your Trust Center settings in Outlook where Programmatic Access in concerned. There is interaction with antivirus and the Trust Center will provide some information as well. Settings there will be disabled if you are not running Outlook with administrator rights. Let me know what you come up with.

I havent been explicit up until now but I would like to draw attention to your AV as well. This is one of the things that it would try to stop. Perhaps there is a rule that fired that is preventing this.

Either way you should be able to test this in another environment to see if it maybe your system in particular. I think we both agree it is not the code specifically since I know it works elsewhere.

Answer 2

Can I suggest that you may need a copy of Outlook open and running. The use of CreateObject() may be insufficient.

To do this programmatically, amend you VBA macro as follows:

Shell "Outlook.exe" 'Opens Outlook

' Your existing code goes here

Shell "taskkill /IM Outlook.exe" 'Closes Outlook again when code is finished (optional)