Excel Outlook rule to open Excel workbook


Joined
Oct 27, 2010
Messages
2
Reaction score
0
I have searched high and low for this but have not found what I'm looking for. I have a email with a subject line of "Calendar" that comes in a few times a week and each of these emails have an excel workbook. Right now these emails are being put in a "Calendar" folder but I would like to also have a rule to automatically open the attached workbook. From my searches it seems like this will require some VBA scripting which I have absolutely no experience with. Any help would be greatly appreciated.
 
Ad

Advertisements

Joined
Oct 27, 2010
Messages
2
Reaction score
0
This is what I've attempted but alas it doesnt work.


'###############################################################################
'### Module level Declarations
'expose the items in the target folder to events
Option Explicit
Dim WithEvents TargetFolderItems As Items
'set the string constant for the path to save attachments
Const FILE_PATH As String = "C:\Drop Calendars\"

Private Sub Application_NewMail()

End Sub

'###############################################################################
'### this is the Application_Startup event code in the ThisOutlookSession module
Private Sub Application_Startup()
'some startup code to set our "event-sensitive" items collection
Dim ns As Outlook.NameSpace
'
Set ns = Application.GetNamespace("MAPI")
Set TargetFolderItems = ns.Folders.Item( _
"Personal Folders").Folders.Item("Drop Calendars").Items

End Sub

'###############################################################################
'### this is the ItemAdd event code
Sub TargetFolderItems_ItemAdd(ByVal Item As Object)
'when a new item is added to our "watched folder" we can process it
Dim olAtt As Attachment
Dim i As Integer

If Item.Attachments.Count > 0 Then
For i = 1 To Item.Attachments.Count
Set olAtt = Item.Attachments(i)
'save the attachment
olAtt.SaveAsFile FILE_PATH & olAtt.FileName

'if its an Excel file, pass the filepath to the print routine
If UCase(Right(olAtt.FileName, 3)) = "XLS" Then
PrintAtt (FILE_PATH & olAtt.FileName)
End If
Next
End If

Set olAtt = Nothing

End Sub

'###############################################################################
'### this is the Application_Quit event code in the ThisOutlookSession module
Private Sub Application_Quit()

Dim ns As Outlook.NameSpace
Set TargetFolderItems = Nothing
Set ns = Nothing

End Sub

'###############################################################################
'### print routine
Sub PrintAtt(fFullPath As String)

Dim xlApp As Excel.Application
Dim wb As Excel.Workbook

'in the background, create an instance of xl then open, print, quit
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(fFullPath)
'wb.PrintOut
'xlApp.Quit

'tidy up
Set wb = Nothing
Set xlApp = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top