Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Check if outlook running in VBA

This is a discussion on Check if outlook running in VBA within the Excel Questions forums, part of the Question Forums category; Hi, I am trying to establish if Outlook 2000 is currently running in VBA , I have tried the following ...

  1. #1
    Board Regular
    Join Date
    Sep 2008
    Location
    Derby UK
    Posts
    56

    Default Check if outlook running in VBA

    Hi,
    I am trying to establish if Outlook 2000 is currently running in VBA , I have tried the following code but this only works when the user is actually positioned in "Inbox - Microsoft Outlook" etc. At this point I don't really care where/what the user has open in Outlook but merely to check if outlook is running.
    I have tried below but does not work robustly enough.
    I have also tried AppActivate("Microsoft Outlook")
    I have also tried IsRunning("Outlook.Application") which does not even compile.
    Code:
     
    Dim OutlookErr, OutlookBox
    On Error GoTo OutlookIsNotRunning
    AppActivate ("outlook")
    GoTo now_send_email
    
    OutlookIsNotRunning:
    
    OutlookErr = "Outlook is either not open or busy with another task." & vbCrLf & vbCrLf
    OutlookErr = OutlookErr & "Please Open Outlook, Close any draft emails," & vbCrLf & vbCrLf
    OutlookErr = OutlookErr & "the Global Address List or other activities and try again."
    OutlookBox = MsgBox(OutlookErr, vbCritical, "Unable to access Outlook to send email")
    Exit Sub
    now_send_email: 

    Thanks in advance for your help!


  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,661

    Default Re: Check if outlook running in VBA

    Something like:

    Code:
    Dim olApp As Object
     
    On Error Resume Next
     
    Set olApp = GetObject(,"Outlook.Application")
     
    On Error Goto 0
     
    If Not olApp Is Nothing Then
      'Outlook is open!
    Else
      'Outlook not open
    End if
    Richard Schollar

    Using xl2013

  3. #3
    Board Regular
    Join Date
    Sep 2008
    Location
    Derby UK
    Posts
    56

    Default Re: Check if outlook running in VBA

    Hi Thanks for the reply.

    Can anyone advise what objects I can examine from the GetObject ??

    as I wish to use AppActivate("Outlook.Application")

    Apparently the above will only work if the user has the inbox selected otherwise I was hope to supply AppActivate with the exact folder which the user has selected on so that the AppActivate works sucessfully.

    Note : I have tried using AppActivate as above but if the user is not on the inbox then the AppActivate does not work.

    Set myOlApp = GetObject(, "Outlook.Application")

    AppActivate(myOlApp)

    So is the above valid ? if not then what other objects can I extract from the GetObject item.

    Thanks in advance.

    I was hoping for maybe ? just maybe AppActivate(

  4. #4
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    21,385

    Default Re: Check if outlook running in VBA

    You could use:
    Code:
    Sub ActivateOL()
       'Error 429 occurs with GetObject if Outlook is not running.
       On Error Resume Next
       Set objOutlook = GetObject(, "Outlook.Application")
       
       If Err.Number = 429 Then 'Outlook is NOT running.
          MsgBox "Outlook is not running"
       Else
          AppActivate objOutlook.ActiveExplorer.Caption
       End If
    End Sub
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  5. #5
    Board Regular
    Join Date
    Sep 2008
    Location
    Derby UK
    Posts
    56

    Default Re: Check if outlook running in VBA

    Thanks for the reply.

    I am in a little tricy bind.

    Code:
     
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString, Managername
    Dim OutlookErr, OutlookBox
    Dim myOlApp             As Object
    Dim olMailItem
    Dim myMail              As Object
    
     
    On Error Resume Next
    Set myOlApp = GetObject(, "Outlook.Application")
     
    On Error GoTo 0
     
    If Not myOlApp Is Nothing Then
        GoTo outlook_available_continue                 'Outlook is open!
    Else
        'Outlook NOT open
        OutlookErr = "Outlook is not open - Please open outlook and re-try."
        OutlookBox = MsgBox(OutlookErr, vbCritical,    Exit Sub
    End If
     
    outlook_available_continue:
     
        Set myOlApp = CreateObject("Outlook.Application")
        Set myMail = myOlApp.CreateItem(olMailItem)
              
        With myMail
            .Display
            .To = ("test@rolls-royce.com") 
            .Subject = "WITH ATTACHMENTS emp_request"
            .Body = "WITH ATTACHMENTS emp_request"
            .Attachments.Add ThisWorkbook.FullName
        End With
        
        AppActivate (????) 
        ‘???? Theres 2 opts here I could use myMail or myOlApp
        
        ‘ONLY IF AUTO SEND NEEDED SendKeys ("^~")
     
        Set myOlApp = Nothing
        Set myMail = Nothing
        Set olMailItem = Nothing
            
        MsgBox "Request Sent Successfully..."
    End Sub

    The problem is that the msgbox at the end advising the user that their email has been sent comes directly after the code that sends so consequently the macro display he msgbox leaving the newly create email somewhere in the taskbar which evades the user. The user who may have dozens of windows open then moves on thinking the email has been sent. It is not always viable to sendkeys after in case the users need to add recipients or signature.

    Basically the problem is that I want the focus on the NEWLY CREATED email even if the msgbox after the createobject tries to grab focus.

    What should I use for the AppActivate as using AppActivate (myOlApp.ActiveExplorer.Caption) simply sets focus to outlook and not the newly createdd email.

    Any suggestions. Thanks in advance.

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    21,385

    Default Re: Check if outlook running in VBA

    Try changing ActiveExplorer to ActiveWindow, or use myMail.GetInspector.Caption instead.
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  7. #7
    Board Regular
    Join Date
    Sep 2008
    Location
    Derby UK
    Posts
    56

    Default Re: Check if outlook running in VBA

    Thanks for that helped loads.

    Just one thing finally. Both the AppActivate (myMail) and AppActivate (myMail.GetInspector.Caption) worked great thanks.

    The only problem is that after the email is sent I would expect the active window to be the last one used before the send but it randonly goes to an explorer or some other window other than Excel.

    I have tried to use AppActivate ("Microsoft Excel")
    but this only stop the sendkeys from sending the email maybe due to timing issues. It seems placing any kind of activate after the sendkeys hinders the sendkeys.

    What a Dilemma !!!!! any suggestions.

    Code:
        
        With myMail
            .Display
            .To = ("Kuldip.mond@rolls-royce.com") ' "U9502056"
            .Subject = "WITH ATTACHMENTS emp_request_kuldip"
            .Body = "WITH ATTACHMENTS emp_request_kuldip"
            .Attachments.Add ThisWorkbook.FullName
        End With
        
        AppActivate (myMail)
        'AppActivate (myMail.GetInspector.Caption)
        
        SendKeys ("^~")
        
        'IF SENDKEYS ***NOT*** USED TO AUTO SEND EMAIL - COMENT the following code line,
        'as you want the focus to remain on the NEWLY Created email, not Excel to display MxgBox
        AppActivate ("Microsoft Excel")
    Thanks again in advance

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    21,385

    Default Re: Check if outlook running in VBA

    If you want the mail sent automatically, don't use Display, use Send. You will probably have the Outlook security model to contend with though, in which case you can look at the Redemption library or an app like Click Yes. Sendkeys is pretty much never a good solution to a problem in my opinion for exactly this reason - you just don't have enough control over which window has the focus.
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

  9. #9
    Board Regular
    Join Date
    Sep 2008
    Location
    Derby UK
    Posts
    56

    Default Re: Check if outlook running in VBA

    Thanks a million.

    I agree, the whole reason for using SendKeys was to stop the annoying Outlook security messages as they tend to be frustrating to users so the I'd rather let the users go back to excel (if not already focus) than getting outlook security trip them up all the time.

    Where can I find info on the Redemption library and app like Click Yes do you have a solution should I proceed down this relentless path... which I highly doubt but if it is a bullet proof solution then it might be worth while. Maybe ...

    Still work great as it is with the AppActivate as above. Thanks again.

  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    21,385

    Default Re: Check if outlook running in VBA

    Info on Redemption can be found here. Not free for commercial use, but it works well.
    ClickYes here - I have no personal experience of it, but lots of people I know recommend it.
    You can program round it in C using Extended MAPI (which is basically what the Redemption library does for you) but that is beyond me.
    Rory
    Microsoft MVP - Excel
    Posting guidelines | Forum rules | FAQs | HTML Maker

    Please use CODE tags when posting code. Either select the code and then press the # button or type them manually:
    [CODE] Your code goes here [/CODE]

    Excel matters

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com