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

Have you tried defining the variables you are creating As Outlook.Application and As Outlook.MailItem? Also, have you tried defining the location reference for where the Range("oftLocation").Value is coming from? Trying to determine how the user is defining that location... (I know stupid little things but thought it worth a shot before proceeding) — Chrismas007
Maybe some more info about the error(s) might be in logs? ... sevenforums.com/tutorials/… — Daniel Dušek
I agree with Christmas007, define the variables as he mentions, ensure the cell "oftLocation" is correct in terms of content and format i.e. directory and file referenced correctly "C:\Test\Test.oft", then I would just try to have the item display MyOlItem.Display. If this isn't working what is the error message? — Shauno_88
His code is sound and does work. Just tested with Outlook and Excel 2010. The only errors i've been able to recreate are 432,53 if the path is not valid and 1004 if the range reference is wrong. This code works.... cant recreate the error. Even tested with spaces anywhere i could think of. Wonder if there are special characters hiding in that range — Matt
Trust Center is locked down as you need to have admin rights to change it. Run Outlook as admin and you will see more options. Also you will see interaction with AV on that screen. The greyed out itself is not the issue. Also trying this on another system with the same environment, if that is an option, might be valuable. Code is not the issue.... i'm sure of it. Dont forget to use @ if you are trying to get someones attention. — Matt

Matt Matt · Accepted Answer · 2014-12-03T18:07:21

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.

Warning Message

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.

Using Excel VBA to create email in Outlook 2010 from template

2 Answers