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