?
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
?
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

800 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