Solved

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

Posted on 2015-02-22
16
414 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 22

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 22

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 22

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 22

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 22

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 22

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 22

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
Find out what you should include to make the best professional email signature for your organization.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

896 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

11 Experts available now in Live!

Get 1:1 Help Now