Controlling Outlook from Excel Using VBA

[Trevor Shuttleworth]

Can anyone give me some code or point me at some examples of controlling
Outlook from Excel.

What I want to do is open Outlook (if it is not open) and then connect to
some .pst files.

The reason for wanting to do this is that I file all my emails in .pst files
but the environment in which I work means I have to connect to them every
time I log on and open Outlook. Once the connections are established they
remain until I log out.

I know Excel VBA reasonably well and would happily use this as the tool but
I don't know where to start. I know enough to open Outlook and send emails
and so on but I don't know enough about the object model to work this out.

No idea where to look so any web sites, example code or whatever would be
much appreciated.

Thanks

Trevor

 

[Ken Slovak - [MVP - Outlook]]

Lots of Outlook code information and samples at
http://www.slipstick.com/dev/index.htm

You can use the AddStore method of the NameSpace object to add PST
files to Outlook and RemoveStore if you want to disconnect them.
However until you kill the Outlook process those PST files will remain
locked.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Lead Author, Professional Outlook 2000 Programming, Wrox Press
Lead Author, Beginning VB 6 Application Development, Wrox Press
Attachment Options
http://www.slovaktech.com/attachmentoptions.htm
Extended Reminders
http://www.slovaktech.com/extendedreminders.htm

 

[Sue Mosher [MVP]]

In addition to what Ken said, there's no reason to involve Excel at all in this. Just put your AddStore code in the Application_StartUp event handler in the built-in ThisOutlookSession module in OUtlook VBA.
--
Sue Mosher, Outlook MVP
Outlook and Exchange solutions at http://www.slipstick.com
Author of
Microsoft Outlook Programming: Jumpstart
for Administrators, Power Users, and Developers

 

[Trevor Shuttleworth]

Sue and Ken

thank you very much for the advice and the pointers. That was a lot easier
than I thought it might be! Sorry, that sounds a little ungrateful ... I
had no idea where to start, what method to use and all the examples I have
focus on sending mails with or without attachments (mostly from Excel).

Thanks again, this will save me a lot of time on a daily basis.

For the benefit of any others (and should I ever forget how to do this),
this is the code I ended up with:

Sub AddPSTFile()
Dim objOutlook
Dim myNameSpace As NameSpace
Set objOutlook = CreateObject("Outlook.Application")
Set objNS = objOutlook.GetNamespace("MAPI")
objNS.AddStore _
"C:\Documents and Settings\...\Local Settings\Application
Data\Microsoft\Outlook\Test.pst"
Set objNS = Nothing
Set objOutlook = Nothing
End Sub

I will explore running it from the ThisOutlookSession module but others use
the computer so it may not be as simple as it sounds. My .pst files are in
my home directory on the network and only I have permission to open them so
I don't want to cause any problems when others open Outlook. But great
progress.

Best regards

Trevor


In addition to what Ken said, there's no reason to involve Excel at all in
this. Just put your AddStore code in the Application_StartUp event handler
in the built-in ThisOutlookSession module in OUtlook VBA.
--
Sue Mosher, Outlook MVP
Outlook and Exchange solutions at http://www.slipstick.com
Author of
Microsoft Outlook Programming: Jumpstart
for Administrators, Power Users, and Developers