Open Excel Worksheet

[Guest]

Hi There

My VBA skills are not very good and I've managed to put this code together
by taking code from a few other macros but I cant get it to work. All I want
to do is get outlook to open a specific worksheet every time a message comes
in with certain text in the subject line. When I try to run this code I get
this error message
Runtime Error '424'
object required, and it highlights the workbook.opens line.

Can anyone help? I would be extremely grateful.

Private Sub Application_NewMail()


Set myOLApp = CreateObject("Outlook.Application")
Set myNamespace = myOLApp.GetNamespace("MAPI")
Set myFolder = myNamespace.GetDefaultFolder(olFolderInbox)
Set myItem = myFolder.Items(1)
If UCase(Left(myItem.Subject, 11)) = "EIS_REQUEST" Then
ChDir "I:\EIS\FORMS"
Workbooks.Open(FileName:="I:\EIS\FORMS\EIS Job Log
test.xls").RunAutoMacros _
Which:=xlAutoOpen
End If

End Sub

Thanks

Jamie

 

[Sue Mosher [MVP-Outlook]]

Outlook doesn't know what you mean by Workbooks. You first need to instantiate an Excel Application object:

Set excel = CreateObject("Excel.Application")
Set wb = Workbooks.Open(FileName:="I:\EIS\FORMS\EIS Job Log test.xls")
wb.RunAutoMacros Which:=xlAutoOpen