?
Solved

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

Posted on 2013-11-12
13
Medium Priority
?
289 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

862 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