Outlook opening Excel?

  • Thread starter Thread starter Rob Lerner
  • Start date Start date
R

Rob Lerner

This is an Outlook VBA question, but it involves Excel. In case Excel
programmers here have experience with it, I'm posting it here. If so, can
someone help me with the following task?

I have a subfolder of the Inbox named "Unprocessed".
I have a subfolder of "Unprocessed" named "Processed".
Each mail item in "Unprocessed" SHOULD have an Excel file attached.

I want a macro to loop through each item in "Unprocessed" and do this...

1. Open the Excel file attached to the message.
2. Run a macro named "PlayThis" that exists in the Excel attachment.
(The Excel macro "PlayThis" runs and then it closes the Excel file.)
3. Return to Outlook
4. Mark the message as read.
5. Move the message to the subfolder named "Processed".
6. Do the next mail item in "Unprocessed" the same way.

Outlook 2002
Excel 2002
Windows 2000/XP

If I didn't supply enough information, please say so and I'll answer your
questions.

Thanks very much.
-Rob
 
Rob

This appears to work. You'll need to change some details, but it should get
you started.

Sub ProcessFiles()

Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim mi As MailItem
Dim Fldr As MAPIFolder
Dim i As Long

'Set a reference to the Excel object library - Tools - References

'Create an excel app
Set xlApp = New Excel.Application

'You may or may not need this
xlApp.Visible = True

'Create a folder reference to the folder one-up from your
'processed and unprocessed folders
Set Fldr = Application.GetNamespace("MAPI"). _
GetDefaultFolder(olFolderInbox).Folders("Tester")

'Loop through the mail in unprocessed
For i = Fldr.Folders("Unprocessed").Items.Count To 1 Step -1

'Save the attachment
Set mi = Fldr.Folders("Unprocessed").Items(i)
mi.Attachments.Item(1).SaveAsFile "C:\Dick\Tester\" & _
mi.Attachments.Item(1).DisplayName

'Open the attachment
Set xlWb = xlApp.Workbooks.Open("C:\Dick\Tester\" & _
mi.Attachments.Item(1).DisplayName)

'If the macro closes the file, you will get an error on
'this line, so you need the error handling
On Error Resume Next
xlApp.Run "PlayThis"
On Error GoTo 0

Set xlWb = Nothing

'Delete the file saved to the hard drive - delete this
'to retain it
Kill "C:\Dick\Tester\" & mi.Attachments.Item(1).DisplayName

'Mark the message as read and move it
mi.UnRead = False
mi.Move Fldr.Folders("Processed")

Next i

xlApp.Quit
Set xlApp = Nothing

End Sub
 
Back
Top