Solved

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

Posted on 2013-11-12
13
274 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

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.

Question has a verified solution.

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

Outlook Free & Paid Tools
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

691 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