Solved

Problems running macros due to migration from Windows XP Excel 2007 to Windows 7 Enterprise Excel 2010

Posted on 2013-11-12
13
257 Views
Last Modified: 2013-11-18
Hello,

I am trying to run the following code but get a compile error stating that a project or library cannot be found.

Public Sub GetAttachments_CURE()
Dim FileName As String
 Dim i As Integer
 Dim timestamp As Date
 Dim filecheck As Boolean
 Dim ASK As String
 Dim TRYBOOK As String
 Dim Txt As String
 Dim Itemcheck As Object
 
 TRYBOOK = ActiveWorkbook.Name
 
 filecheck = False
 Set Itemcheck = Nothing
 Set olkAPp = CreateObject("outlook.application")
 Set ns = olkAPp.GetNamespace("MAPI")
 Set Inbox = olkAPp.Session.Folders("Mailbox - London MO - Rates").Folders("Inbox")
 i = 0

The highlighted problem appears to be "olkAPp".

Thanks.
0
Comment
Question by:f19l
  • 8
  • 5
13 Comments
 
LVL 6

Expert Comment

by:Michael
ID: 39641740
Which line exactly is highlighted when you press the debug button?

Maybe a stupid question, but is Outlook installed?
0
 

Author Comment

by:f19l
ID: 39641770
The first line that shows the "olkAPp". This file worked last week when I was using excel 2007.
0
 
LVL 6

Expert Comment

by:Michael
ID: 39641851
Could you post the rest of code as well?
0
 

Author Comment

by:f19l
ID: 39641863
Here is the entire code

Public Sub GetAttachments_CURE()
Dim FileName As String
 Dim i As Integer
 Dim timestamp As Date
 Dim filecheck As Boolean
 Dim ASK As String
 Dim TRYBOOK As String
 Dim Txt As String
 Dim Itemcheck As Object
 
 TRYBOOK = ActiveWorkbook.Name
 
 filecheck = False
 Set Itemcheck = Nothing
 Set olkAPp = CreateObject("outlook.application")
 Set ns = olkAPp.GetNamespace("MAPI")
 Set Inbox = olkAPp.Session.Folders("Mailbox - London MO - Rates").Folders("Inbox")
 i = 0
 
 If Inbox.Items.Count = 0 Then
    MsgBox "There are no messages in the Inbox.", vbInformation, "Nothing Found"
    Exit Sub
 End If
 
 For Each Item In Inbox.Items
 
   For Each Atmt In Item.Attachments
     If Left(Atmt, 7) <> "Picture" And Left(Atmt, 5) <> "image" Then
    If LCase(Atmt.FileName) Like "*.xls*" Then
      If Atmt.FileName = "MO_London_Rates.xls" Then

        If Dir("\\EMEA\Root\Shared2\London Rates\Temp\CURE.xls") <> "" Then
            Kill "\\EMEA\Root\Shared2\London Rates\Temp\CURE.xls"
        End If
        FileName = "\\EMEA\Root\Shared2\London Rates\Temp\CURE.xls"
        Atmt.SaveAsFile FileName
        Workbooks.Open ("\\EMEA\Root\Shared2\London Rates\Temp\CURE.xls")
        If Mid(Range("A6").Value, 45, 2) & "/" & Mid(Range("A6").Value, 42, 2) & "/" & Mid(Range("A6").Value, 48, 4) <> Format(Now(), "DD/MM/YYYY") Then
            MsgBox "The CURE file contains old data!!!", vbOKOnly + vbExclamation, "OLD DATA!!!"
        End If
        Range("A6:F300").Select
        Selection.Copy
        Workbooks(TRYBOOK).Activate
        Sheets("CURE_Flat").Select
        Range("A6").Select
        ActiveSheet.Paste
        ActiveSheet.Calculate
       
        Exit Sub
      End If
    End If
   Next Atmt
'Line1:

Next Item
'goto exitSub
 
'GetAttachments_exit:
   Set Atmt = Nothing
   Set Item = Nothing
   Set ns = Nothing
'   GoTo Line1
'GetAttachments_err:
 
'   Resume GetAttachments_exit
'exitSub:
MsgBox "No CURE file present!!!", vbOKOnly + vbExclamation, "MISSING CURE DATA!!!"
End
End Sub
0
 
LVL 6

Expert Comment

by:Michael
ID: 39641981
Thanks for posting the entire code.
The error usually means you need to set a reference in the VB Editor.
However, since you're using late binding instead of early binding, this should not be necessary.

Therefore, I asked for the rest of the code to see if there was anything that may caused the error. But I ran the code on my machine and I didn't get the error.

Do you have other macros that are automating Outlook from Excel? If so, does that work or generate the error as well?

You could also try to use early binding, by replacing
Set olkAPp = CreateObject("outlook.application")
with
Dim olkAPp as Outlook.Application
Set olkAPp = New Outlook.Application


Then in the Tools menu of the VB Editor use the Reference command and set a reference to Microsoft Outlook xx.x Object Library.

Joop
0
 

Author Comment

by:f19l
ID: 39642346
I tried what you suggested but still get the same problem.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:f19l
ID: 39642354
Actually the problem then moves to the next line so the same error message appears but this time the "ns=" is highlighted.
0
 

Author Comment

by:f19l
ID: 39644039
Perhaps rather than trying to fix this particular code it maybe easier to simply build from scratch.
0
 
LVL 6

Expert Comment

by:Michael
ID: 39644535
Perhaps, but the problem is that a new macro that tries to automate Outlook from within Excel, you still need the same code to start with:
Set olkAPp = CreateObject("outlook.application")
or:
Dim olkAPp as Outlook.Application
Set olkAPp = New Outlook.Application

Maybe if you open a new question here with a more specific topic title, you attract more Outlook experts. Something like: Automating Outlook from Excel. Problems with CreateObject("Outlook.Application").

Joop
0
 

Accepted Solution

by:
f19l earned 0 total points
ID: 39644549
I believe that I found the solution, looking at the reference window I noticed that one was highlighted as missing. Once I removed it then the entire code worked correctly.
0
 
LVL 6

Expert Comment

by:Michael
ID: 39644749
Ok, great!

Do you remember which reference you've added? Could be useful for other readers with the same problem.

Joop
0
 

Author Comment

by:f19l
ID: 39644782
I did not add any reference, just removed all that were showing up as missing.
0
 

Author Closing Comment

by:f19l
ID: 39656029
I found out myself that the reason why my code was not working was that a reference was missing. Once that was excluded then it all worked.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now