Excel Macro Run-time Error when trying to create Outlook.Application

I have a simple macro that emails users and it works when Outlook is open, but when Outlook is closed I receive an error.

Run-Time Error '76':
Path not found.

Sub Mail_small_Text_Outlook()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    On Error Resume Next
    With OutMail
        .To = "mboscaino@flhlaw.com"
       ' .CC = ""
       ' .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Open in new window

LVL 25
yo_beeDirector of Information TechnologyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
broro183Connect With a Mentor Commented:
hi yo_bee,

I have had problems with code like this in the past when Outlook (2007) is closed but I can't remember the error code so the following suggestion may or may not help (sourced from post ID 39479415. NB: I posted this in 2010 & I can't find a more uptodate version in my code files but it probably needs some modification.

replace these two lines
 Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

Open in new window


with the below code
    'sourced from:Automation of out look Mails based on the excel list
    'http://www.thecodecage.com/forumz/1054996151-post10.html
    'http://www.thecodecage.com/forumz/microsoft-outlook-forum/213688-search-new-email-text-make-bold-blue.html#post1055005238
    'RB: other constants to allow Late Binding*
    Const olFolderInBox As Long = 6
    Dim OutApp As Object
    Dim oNameSpace As Object
    Dim OutMail As Object

'...other code & then...

    'check for open Outlook session & open if necessary
    'sourced from: Microsoft Office Help - Microsoft Office Discussion - Excel VBA Programming - Access Programming
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    On Error GoTo 0

    '### may need modification
    If OutApp Is Nothing Then
        Set OutApp = CreateObject("Outlook.Application")
        Set oNameSpace = OutApp.GetNamespace("MAPI")
        oNameSpace.Logon , , True
        oNameSpace.GetDefaultFolder(olFolderInBox).Display
        'WasOutlookOpenedByCode = True
    End If

    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

Open in new window


hth
Rob
0
 
NorieVBA ExpertCommented:
Works fine for me whether Outlook is open or closed.

What error message are you getting when Outlook is closed?

By the way, since you appear to be using late-binding you should either declare the constant olMailItem or replace it in the code with it's value.

This is how you would declare it.
Constant olMailItem = 0

Open in new window

0
 
yo_beeDirector of Information TechnologyAuthor Commented:
I also tried
 Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

Open in new window


Without any change in behavior
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No change expected, as long as you haven't set Option Explicit - and that would result in a syntax error when using the undefined constant.

Are Outlook and Excel release the same? Which release?
0
 
NorieVBA ExpertCommented:
What's the error message/number?
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
1: Yes they are both 2010
2:  Run-Time Error '76':
     Path not found.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Check the path reported with
reg query HKEY_CLASSES_ROOT\CLSID\{0006F03A-0000-0000-C000-000000000046}\LocalServer32 -ve

Open in new window

in a Command Prompt. This is the key for starting "Outlook.Application" for 14 (2010), if not running.
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
I do not have the LocalServer32 Key, but I do have InprocServer32 Key
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That key is required. Try a  repair installation of Office.
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
Great answer. Thanks so much.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I really doubt the accepted answer is of any help. It does not do anything different than the original script, but with much more ado.
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
Well it worked for me so I accepted that as a resolution. All the other suggestions did not work.
I tried a repair to Office 2010 as suggested when I did not see the Reg key you were referring to and that did nothing.  

So from my standpoint that is the accepted solution to my issue.

Sorry I was not able to give you points Qlemo.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
That's really strange, I seem to miss something ... But if it works for you, so be it. (And it is not about the points.)
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
Qlemo when I ran the script as I posted I get an error if Outlook is not open.
It does try to initiate Outlook, but something fails.  If I add the Mapi with a session logon I am able to run my excel macro without having Outlook running at the time of running the macro.  This is a scheduled routine that queries a DB, exports it as a PDF then e-mails the individuals.  That is why I need to be able to e-mail from excel without having Outlook open.  

The weird thing is on my old computer it worked as it was supposed to without having to create a Session with Outlook.
0
 
broro183Commented:
hi yo_bee,

Thank you for the points. Did you need to make any adjustments to my code for it to work?
Yes, it does seem weird that it works on your old computer & not your other one but I also the same issue where some machines required the "extra" code and some didn't.
I suggest that you also run Rob Bovey's CodeCleaner over your file too. It can sometimes prevent some strange issues from occurring.

Qlemo,
I can't remember exactly what my issue was the first time I needed to use this code but it had something to do with trying to make Created instances of the Outlook application visible. The oldest online reference I can find of me posting code like this is in TheCodeCage (6/1/2012) and from a glance at the context of that post I suspect that I had been using it in one of my previous jobs (late 2009 - late 2011). I don't know the cause of the issue but the fact that my first related post was 3 years ago in a MS Office 2007 environment suggests that the oddity is not new or version dependent. The root cause (RC) is probably buried somewhere in a computer's settings but, once I had working code, I didn't continue looking for the RC.

Rob
0
 
yo_beeDirector of Information TechnologyAuthor Commented:
This is the part of the  code it used as it

    Dim OutApp As Object
    Dim oNameSpace As Object
    Dim OutMail As Object

'...other code & then...

    'check for open Outlook session & open if necessary
    'sourced from: Microsoft Office Help - Microsoft Office Discussion - Excel VBA Programming - Access Programming
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    On Error GoTo 0

    '### may need modification
    If OutApp Is Nothing Then
        Set OutApp = CreateObject("Outlook.Application")
        Set oNameSpace = OutApp.GetNamespace("MAPI")
        oNameSpace.Logon , , True
        oNameSpace.GetDefaultFolder(olFolderInBox).Display
        'WasOutlookOpenedByCode = True
    End If

    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.