Macro doesn't run on new machine

[Guest]

Hi There

I wrote the following macro and this runs fine without any problems on one
machine. However I need it to run on another machine in my office but when I
try I get the following error message:


Run-time error '-2147319779 (8002801d)':

Automation error
Library not registered


Macro:

Sub auto_open()

Windows("EIS Job Log test.xls").Activate
Range("B2").Select



Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim MoveToFldr As MAPIFolder
Dim olMi As MailItem
Dim olAtt As Attachment
Dim MyPath As String
Dim i As Long

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
Set MoveToFldr = Fldr.Folders("eisreq")
MyPath = "I:\EIS\Forms\EIS Requests\"
dattim = Format(Date, "yyyymmdd") & " " & "Time-" & Format(Time, "hhmmss")


For i = Fldr.Items.Count To 1 Step -1
Range("A1").Select


rowlength = Selection.CurrentRegion.Rows.Count
Set olMi = Fldr.Items(i)
If InStr(1, olMi.Subject, "EIS_REQUEST") > 0 Then
For Each olAtt In olMi.Attachments
If olAtt.Filename = "EIS Request.xls" Then
olAtt.SaveAsFile MyPath & Fldr.Items.Count & " " &
olMi.SenderName & " " & "Date-" & dattim & ".xls"
open1 = MyPath & Fldr.Items.Count & " " &
olMi.SenderName & " " & "Date-" & dattim & ".xls"
filenm = Fldr.Items.Count & " " & olMi.SenderName & "
" & "Date-" & dattim & ".xls"
End If

Next olAtt
olMi.save
olMi.Move MoveToFldr
Workbooks.Open Filename:=open1

'copies and pastes data from eis request
Range("IR4:IV4").Select
Selection.Copy
Windows("EIS Job Log test.xls").Activate
Range("A1").Select
For x = 1 To rowlength
If ActiveCell.Cells <> "" Then
Cells(ActiveCell.Row + 1, 1).Select
End If
Next x
Selection.PasteSpecial paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

'copies and pastes filename
Range("E1").Select
For x = 1 To rowlength
If ActiveCell.Cells <> "" Then
Cells(ActiveCell.Row + 1, 6).Select
End If
Next x
ActiveCell = filenm

Windows(filenm).Activate
ActiveWorkbook.Close False

Windows("EIS Job Log test.xls").Activate
ActiveWorkbook.save
ActiveWorkbook.Close False
End If
Next i

Set olAtt = Nothing
Set olMi = Nothing
Set Fldr = Nothing
Set MoveToFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing

End Sub

Both machines have the following ticked in the reference table.

Visual Basic for Applications
Microsoft Excel 9.0 Object Library
OLE Automation
Microsoft Office Object Library
Microsoft Outlook Object Libray

Can anyone help?

I would be extremely gratetful

Thanks in advance

Jamie

 

[Guest]

You need to create a reference to the Outlook Library. Click on Tools,
References from your IDE.

 

[Guest]

Hi Galimi

Thanks for your response. I thought I had done this already. When I click
references it shows that all these are ticked:

Visual Basic for Applications
Microsoft Excel 9.0 Object Library
OLE Automation
Microsoft Office Object Library
Microsoft Outlook Object Libray

Do I need to do something else?

Jamie

 

[Guest]

Hi Jamie, your routine was a bit of a puzzle to me, but I was able to
duplicate your error and resolve it. I experienced a similar error when I
purposely referenced the wrong path that stores the EIS requests Spreadsheet
attachments. Apparently, Your routine saves attachments to "I:\EIS\Forms\EIS
Requests\" ... The new computer you are using may not use "I" as the drive
that maps to the EIS Request folder..or it may not even be able to access
this shared location. If the folder "EIS\Forms\EIS Requests" is not on the
"I" drive, you will receive this error.

Suggestion
1) Change MyPath = "I:\EIS\Forms\EIS Requests\"
To => MyPath ="\\YourServerLocation\EIS\Forms\EIS Requests\
or temporarily create a folder on the C-drive ("C:\EIS\Forms\EIS Requests\")
and make "MyPath" reference that path to see if you still receive the same
error.
if so, h

2) Make sure Outlook has been Activated, set up and in use on the new machine.
I noticed your routine also requires a subfolder "EisReq" to exist within
the inbox of Microsoft Outlook, an error will occur if this sub-folder does
not exist.

p.s. You have a nice routine. I have a small suggestion. You could easily
develop a "Button" and place it directly on your spreadsheet to activates the
process whenever desired. One click of a button could scan inbox for
requests, move all requests to the EISReq folder, save all attachments to
designated folder and add new requests to EIS Request Log spreadsheet. I
created a sample spreadsheet if you want to see it.

Let me know if you have any questions.

Reggie.