Solved

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

Posted on 2015-02-22
16
444 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 34

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 23

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 70

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 34

Expert Comment

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

Author Comment

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

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 23

Author Comment

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

Expert Comment

by:Qlemo
ID: 40625198
That key is required. Try a  repair installation of Office.
0
 
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 23

Author Closing Comment

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

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 23

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 70

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 23

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 23

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
When you have clients or friends from around the world, it becomes a challenge to arrange a meeting or effectively manage your time. This is where Outlook's capability to show 2 time zones in one calendar comes in handy.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

737 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