Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
?
287 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

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 …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

618 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