Outlook VBA CreateObject fails during Application_Startup()

Question

I wrote a macro in Outlook that opens an Excel file, then runs another macro inside the Excel spreadsheet. The Excel macro in turn saves some charts as PDF files, and opens up Outlook to email them as attachments.

When I run this macro with Outlook already open, it works fine. However, if I assign the macro to execute in the Application_Startup event upon startup, I get an error in the Excel macro that says ActiveX cannot create the desired object. It appears on the first line of this code:

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

I did Dim both variables as Objects. Sorry for the novice question, but I have been unable to find the explanation with several hours of searching.

Why does this only occur when the macro runs during startup? Does this have something to do with Excel or Outlook not being fully loaded at the time the macro attempts to run?

Answer 1

First thing I'd try is to re-register the server component by running Outlook from the command line with the /RegServer switch.

Secondly, I would check the Launch/Activation settings for Microsoft Outlook component using the dcomcnfg windows utility. (Right-click Start, Run > dcomcnfg, expand Component Services, expand My Computer, expand DCOM Config, location Microsoft Outlook in list, right-click > Properties, Security tab.

Here is a link to a Microsoft support page which may be relevant to your problem...

You receive run-time error 429 when you automate Office applications

If you can provide an error number and version of Office, and whether your machine is part of an AD Domain, it may be easier to get to the source of your problem!

Answer 2

I had a similar problem not long ago.

You are better off starting this whole process from the excel end, and not open excel from outlook.

So open excel, save the charts to pdf AND THEN open outlook from your excel macro to send the email. No outlook macros involved.