Solved

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

Posted on 2015-02-22
16
400 Views
Last Modified: 2016-02-11
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

0
Comment
Question by:yo_bee
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 40624279
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
 
LVL 21

Author Comment

by:yo_bee
ID: 40624367
I also tried
 Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

Open in new window


Without any change in behavior
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40624378
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
 
LVL 33

Expert Comment

by:Norie
ID: 40624415
What's the error message/number?
0
 
LVL 21

Author Comment

by:yo_bee
ID: 40624423
1: Yes they are both 2010
2:  Run-Time Error '76':
     Path not found.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40624718
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
 
LVL 21

Author Comment

by:yo_bee
ID: 40624874
I do not have the LocalServer32 Key, but I do have InprocServer32 Key
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40625198
That key is required. Try a  repair installation of Office.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 10

Accepted Solution

by:
broro183 earned 500 total points
ID: 40625248
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
 
LVL 21

Author Closing Comment

by:yo_bee
ID: 40625962
Great answer. Thanks so much.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40626354
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
 
LVL 21

Author Comment

by:yo_bee
ID: 40626384
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40626400
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
 
LVL 21

Author Comment

by:yo_bee
ID: 40626414
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
 
LVL 10

Expert Comment

by:broro183
ID: 40627858
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
 
LVL 21

Author Comment

by:yo_bee
ID: 40627977
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Resolve DNS query failed errors for Exchange
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now