Solved

open outlook with excel vb only if there isn't one open

Posted on 2007-11-29
14
1,136 Views
Last Modified: 2009-12-16
I want to open outlook, but i want ti to be in an if statement saying that if one is already open then don't do anytihng. I would also like my code to colse outlook at the end only if it opened an outlook though. Thanks.

I have this so far tro open it, but it does mutliple instances

Shell "OUTLOOK.EXE"
0
Comment
Question by:tchristie33
  • 6
  • 6
  • 2
14 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
You can use something like this - the private bDoOutlookClose variable is True if you opened Outlook, False if it was already running:


Private bDoOutlookClose As Boolean

Private Function OpenOutlook() As Boolean

   'Use GetObject if an application is already running, if not, use CreateObject.

   '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.

      Err.Number = 0

      bDoOutlookClose = True

      Set objOutlook = CreateObject("Outlook.Application") 'Create a new instance of Outlook

      If Err.Number = 429 Then 'MS Outlook is not installed.

         MsgBox "MS Outlook is not installed on your computer. ", vbOKOnly + vbCritical

         OpenOutlook = False

         Exit Function

      End If

   Else

      bDoOutlookClose = False

   End If

   OpenOutlook = True

End Function

Open in new window

0
 
LVL 31

Expert Comment

by:merowinger
Comment Utility
u can also use the wmi object win32_process and query for process.count
0
 

Author Comment

by:tchristie33
Comment Utility
I want to keep it a lot simpler than that. Right now i can open and close outlook from excel vb, with this code

Sub test()
Shell "OUTLOOK.EXE"
outlook.Application.Quit
End Sub

Is there any way to see if outlook is running so that i can do something like this

Sub test()
if outlook.application = open then
msgbox it's open
Else
Shell "OUTLOOK.EXE"
outlook.Application.Quit
End if
End Sub
0
 

Author Comment

by:tchristie33
Comment Utility
or the oppisite, can i check and see if outlook is closed?
0
 
LVL 31

Expert Comment

by:merowinger
Comment Utility
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
How is that complicated? You just paste the function in, then in your code you do:

If OpenOutlook Then
   ' rest of your code
End If
If bDoOutlookClose Then objOutlook.Quit

0
 

Author Comment

by:tchristie33
Comment Utility
I am not that advnaced with code yet. And the person for whom i am creating this for wants me to be able to expain everything. I can't explain all of those commands within the function. That is why i tried the other appraoch. I just figured that there has to be a short command that can see if outlook is closed or not or if it is running. If not i understand. Thanks for your time and help
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
This is the key bit of the function:

   On Error Resume Next
   Set objOutlook = GetObject(, "Outlook.Application")
   
   If Err.Number = 429 Then 'Outlook is NOT running.

you can use that on its own just to check if Outlook is already open.
Regards,
Rory
0
 

Author Comment

by:tchristie33
Comment Utility
when i run the code below it doesn't catch the error caused by the line Set objOutlook = GetObject(, "Outlook.Application")



Sub test()
  On Error Resume Next
   Set objOutlook = GetObject(, "Outlook.Application")

   If Err.Number = 429 Then 'Outlook is NOT running.
            Shell "OUTLOOK.EXE"
    End If
'other code

outlook.Application.Quit

End Sub
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
What do you mean by "it doesn't catch the error"?
Also, note that your code is now about the same as the original code I posted in terms of complexity...
0
 

Author Comment

by:tchristie33
Comment Utility
When i run it to see if out look is open and to get the object it doesn't alwasy register that as an erro even if outlook is closed. Also the code i am working with now does not deal with nay functions which makes it easy for me to understand and explain
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
To me that would imply that you may have invisible instances of Outlook running, hence no error. Did you check the process list in Task Manager?
0
 

Author Comment

by:tchristie33
Comment Utility
when i originally run the code with no outlook open it works and opens outlook, and then when i run it again it closes it like it should. However, the problem comes when i run it once more, it does not register thtat command as being an error. I must first do some other stuf on my computer such as open and clsoe other windows and things like that, then wehn i run it again it works.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
Try this version (you may need to reboot to clear out any existing instances of Outlook):

Sub OpenOutlook()
   Dim objOutlook As Object
   'Use GetObject if an application is already running, if not, use CreateObject.
   '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.
      Err.Number = 0
      'Create a new instance of Outlook
      Set objOutlook = CreateObject("Outlook.Application")
   End If

   ' rest of your code here

   objOutlook.Quit
   Set objOutlook = Nothing
End Sub


Regards,
Rory
0

Featured Post

IT, Stop Being Called Into Every Meeting

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

728 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