Run Outlook from Excel

  • Thread starter Thread starter Ian Elliott
  • Start date Start date
I

Ian Elliott

Thanks in advance.
I have a question about accessing an attached file in
Outlook from Excel. I have an Excel workbook macro that
opens a .rtf file,
finds some numbers, then brings those numbers into the
worksheet. The .rtf file is typically an attached file to
an email that my boss receives once a month or so. For the
macro to get at that info in the attached file, she saves
the attached file to a directory and then copies and
pastes the directory name to the worksheet (cause the
macro gets that directory and filename from there).

When she runs the macro, it looks at the certain place on
the worksheet to get file and directory info, opens the
file, finds the number, gets the number, and then puts it
in the worksheet.
To save her the hassle of saving the file to a directory,
I would like to make my macro open Outlook, and prompt the
user to click on the email, then click (double-click?) on
the attached
file. The macro will then open the file and do the search
stuff from the email (while still in her Outlook inbox).
So from Excel I would do start something like:
Dim outapp as New Outlook.application
With outapp
prompt user to select an email
prompt user to select an attachment
End With
'use Word to open attachment
'search for number
I guess my first question is how do I start Outlook from
an Excel macro?
(so the Outlook interface will be displayed to the user).

Recently, thanks to Ken I got this solution:
Dim objOL As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim objInbox As Outlook.MAPIFolder

Set objOL = CreateObject("Outlook.Application")
Set objNS = objOL.GetNamespace("MAPI")
Set objInbox = objNS.GetDefaultFolder(olFolderInbox)

objOL.Explorers.Add objInbox, olFolderDisplayNormal
but when I put this in my Excel code it runs fine, but
Outlook is not displayed (and if I click on Outlook in the
quick launcher on bottom left, it doesn't load, although
if I do it from Start>Programs>Microsoft Outlook it does)
Am I missing a line?
Thanks again.
 
I have made a little progress, my macro is now this:
Set objOL = CreateObject("Outlook.Application")
Set objNS = objOL.GetNamespace("MAPI")
Set myinbox = objNS.GetDefaultFolder(olFolderInbox)
myitems = myinbox.Display

and this displays the inbox (for a second) then goes back
to Excel. How can I stay in Outlook and perform normally
(I would like the user to click on a message, then double-
click on a file to mark the file to be read). Perhaps an
event for double-clicking an attachment?
Thanks.
 
It might be easier to go the other way. WIth a macro in Outlook (pseudocode):

1) Get the currently selected or open item (http://www.outlookcode.com/codedetail.aspx?id=50)

2) Save the .rtf file attachment to the system (Attachment.SaveAsFile)

3) Open the Excel file that contains the macro and run the macro.
--
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
 
Back
Top