Closing Outlook VBE from Excel

  • Thread starter Thread starter Stephen Lloyd
  • Start date Start date
S

Stephen Lloyd

I posted this in the Excel forum because I'm automating from Excel, however,
I thought I'd try here as well.

In Excel I instatiate outlook using the following.

On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
' If outlook was not open then instantiate outlook
If objOutlook Is Nothing Then
Set objOutlook = CreateObject("Outlook.Application")
'HandleOutlook = True
End If

In Outlook in the Application_Startup() Event I then open the VBE so that I
can call an outlook macro from excel (without any popups) using the following

Set objNameSpace = Application.GetNamespace("MAPI")
Set objExplorer = Application.Explorers.Add(objNameSpace.Folders(1),
olFolderDisplayFolderOnly)
objExplorer.CommandBars.FindControl(, 1695).Execute
objExplorer.Close

My problem is that I cannot figure out how to close Outlook's VBE from Excel

I'd like to use the GetObject method to declare the VBE app and then use the
..Quit method, but I have been unable to locate the appname for Outlooks VBE
to refernce in GetObject([path] [,appname]). Does anyone know the appname
and/or a better way to close Outlook's VBE?
 
The better way is to not have to open the VBE at all. If you place whatever
macros you want to call in the ThisOutlookSession class module, declared as
Public, you can call them directly from your code. If you have a macro
"Foobar" that's public you can then call it from your code as
objOutlook.Foobar().
 
Thank you sir. I'll go make them public.

Ken Slovak - said:
The better way is to not have to open the VBE at all. If you place whatever
macros you want to call in the ThisOutlookSession class module, declared as
Public, you can call them directly from your code. If you have a macro
"Foobar" that's public you can then call it from your code as
objOutlook.Foobar().




Stephen Lloyd said:
I posted this in the Excel forum because I'm automating from Excel,
however,
I thought I'd try here as well.

In Excel I instatiate outlook using the following.

On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
' If outlook was not open then instantiate outlook
If objOutlook Is Nothing Then
Set objOutlook = CreateObject("Outlook.Application")
'HandleOutlook = True
End If

In Outlook in the Application_Startup() Event I then open the VBE so that
I
can call an outlook macro from excel (without any popups) using the
following

Set objNameSpace = Application.GetNamespace("MAPI")
Set objExplorer = Application.Explorers.Add(objNameSpace.Folders(1),
olFolderDisplayFolderOnly)
objExplorer.CommandBars.FindControl(, 1695).Execute
objExplorer.Close

My problem is that I cannot figure out how to close Outlook's VBE from
Excel

I'd like to use the GetObject method to declare the VBE app and then use
the
.Quit method, but I have been unable to locate the appname for Outlooks
VBE
to refernce in GetObject([path] [,appname]). Does anyone know the appname
and/or a better way to close Outlook's VBE?
Click to expand...
Click to expand...
 
Back
Top