Macro to work with attachments?

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

Rob Lerner

Is the following possible, and if so would someone help me with the code?

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
 
This should do what you need. I couldn't figure out how to run the macro in
the spreadsheet though. I thought I knew how to do this once before
<shrug>...

Dim objNS As Outlook.NameSpace
Dim objUnprocessed As Outlook.MAPIFolder, objProcessed As
Outlook.MAPIFolder
Dim objInbox As Outlook.MAPIFolder
Dim objMessages As Outlook.Items, objMessage As Object
Dim objAtts As Outlook.Attachments, objAtt As Outlook.Attachment
Dim strPath As String, varRet As Variant, intX As Integer
Dim objWorkbook As Excel.Workbook, objExcel As Excel.Application

Set objNS = Application.GetNamespace("MAPI")
Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
Set objUnprocessed = objInbox.Folders.Item("Unprocessed")
Set objProcessed = objUnprocessed.Folders.Item("Processed")
Set objExcel = New Excel.Application
Set objMessages = objUnprocessed.Items

For intX = objMessages.Count To 1 Step -1
Set objMessage = objMessages.Item(intX)
Set objAtts = objMessage.Attachments
For Each objAtt In objAtts
If Right(objAtt.FileName, "3") = "xls" Then
strPath = "C:\Temp\" & objAtt.DisplayName
objAtt.SaveAsFile strPath
varRet = Shell("excel.exe """ & strPath & "")
Set objWorkbook = objExcel.Workbooks.Open(strPath)
'objExcel.Run objAtt.DisplayName & "!PlayThis" 'I can't get
this to work...
Set objWorkbook = Nothing
objMessage.UnRead = False
objMessage.Move objProcessed
End If
Next
Next
 
Wow, Eric, thanks for the help. I think I understand most of the code, but
I'm getting a compile error on...

Dim objWorkbook As Excel.Workbook, objExcel As Excel.Application

Did you not get that? Do I need a special reference library activated?

-Robert
 
Eric, I think I had to activate a reference to the Excel 10 object library.
After doing so, I doesn't give me a compile error. I'll try the code again.

Thanks,
Robert



Rob Lerner said:
Wow, Eric, thanks for the help. I think I understand most of the code, but
I'm getting a compile error on...

Dim objWorkbook As Excel.Workbook, objExcel As Excel.Application

Did you not get that? Do I need a special reference library activated?

-Robert


Eric Legault said:
This should do what you need. I couldn't figure out how to run the
Click to expand...
macro
Click to expand...
 
Good stuff. You can always declare late-bound variables (i.e. objExcel as
Object), and then use Set objExcel = CreateObject("Excel.Application") so
that you are not bound to a particular type library. This way is better for
distributing code that needs to work with multiple versions.
 
Back
Top