0

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
Community
  • 1
  • 1
Nik Burns
  • 3,363
  • 2
  • 29
  • 37
  • 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 Dec 01 '14 at 17:49
  • Maybe some more info about the error(s) might be in logs? ... http://www.sevenforums.com/tutorials/226084-event-viewer-open-use-windows-7-a.html – Daniel Dušek Dec 01 '14 at 19:37
  • 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 Dec 02 '14 at 00:20
  • MS page for this function - http://msdn.microsoft.com/en-us/library/office/ff865637(v=office.14).aspx – Shauno_88 Dec 02 '14 at 00:21
  • thanks, variables also tested using As Outlook.MailItem etc still same error 287 as per above. template is showing correctly. this appears to be a object issue. or maybe an issue with permissions within outlook or excel? Outlook is no longer showing the 'an application would like to access your outlook' message. (the one with 1 min, 5 mins or 10 min options) thanks – Nik Burns Dec 02 '14 at 13:33
  • What do you get if you try and `MsgBox` out the `Range("oftLocation").Value`? Curious what is stored in that value. – Chrismas007 Dec 02 '14 at 21:22
  • Also, on a whim, try changing `Range(blah).Value` to `Range(blah).Text` which will force a string response. – Chrismas007 Dec 02 '14 at 21:29
  • Last thing to try: `...Template(Chr(34) & Range(blah).Value/.Text & Chr(34))` – Chrismas007 Dec 02 '14 at 21:33
  • 2
    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 Dec 03 '14 at 04:25
  • Do you have any anti-virus screwing with your access to the mail? What is your Trust Center setting for Programmatic Access set to? Error 287 seems to point to user cancelling the programmatic access prompt. So I am really curious about my other questions. – Matt Dec 03 '14 at 04:36
  • You will have to give us enough information to reproduce the problem, else we can't help. See this for guidance: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) For starters, on what line do you get the error? – Jean-François Corbett Dec 03 '14 at 13:39
  • my trust centre appears to be locked down. I have full macro access, but my programmatic access to VBA object model is greyed out. Could this be causing an issue? I understood this was to stop access via VBA to the modules in the editor? – Nik Burns Dec 03 '14 at 14:13
  • 1
    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 Dec 03 '14 at 14:40
  • Error # 287 was generated by VBAProject Error Line: 0 Application-defined or object-defined error C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\1033\VbLR6.chm 1000095 – Nik Burns Dec 03 '14 at 14:44
  • the above is the full error description. hopefully this helps? – Nik Burns Dec 03 '14 at 14:44
  • hi @matt, i'm trying to investigate my trust centre settings. programmatic access to objects is not visible. my pc is locked down in the office. I receive the 287 error from creating email from CreateItem(olMailItem) so I know its not the template. thanks – Nik Burns Dec 03 '14 at 16:45
  • Agreed. It's not the template or the code. Solution I feel is with the Trust Center process or something like Antivirus that would stop something like this from working. – Matt Dec 03 '14 at 17:28

2 Answers2

2

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.

Matt
  • 45,022
  • 8
  • 78
  • 119
  • thanks Matt, I think you are correct. I'm hoping to be able to run some additional tests today. – Nik Burns Dec 04 '14 at 08:17
  • thanks Matt, whilst I still have the issues, I think you have demonstrated that the code is sound, therefore my problem exists outside of the VBA editor :-) – Nik Burns Dec 07 '14 at 15:40
-3

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)
e76d587d9
  • 994
  • 5
  • 14
  • thanks Rhy, I have Outlook open already when testing. I agree that the object may be the issue. But the code worked perfectly in 2003. so bit confused.com – Nik Burns Dec 01 '14 at 14:09
  • If Outlook is not running then Outlook is opened. In trying to generate and error this behavior was observed. Should be not need to open Outlook programmically outside of that. – Matt Dec 04 '14 at 00:53