locked
Problem with outlook RRS feed

  • Question

  • Hello,

    I have been working with with VBA and Outlook. I have written a VBA procedure that every time an email comes in the macro verify the name of the person, then look for a match in a excel worksheet and finally move that email to the appropriate folder in a PST file. this was working fine until a few week ago. Now the macro sometimes works and most of the times it doesn't work. The error it keeps trowing me is a 91 - object or with block not set. Could you please help me with this problem?

    This is the VBA procedure:

    Private WithEvents Items As Outlook.Items 

    Private Sub Application_Startup()
      Dim olApp As Outlook.Application
      Dim objNS As Outlook.NameSpace
      Set olApp = Outlook.Application
      Set objNS = olApp.GetNamespace("MAPI")
      ' default local Inbox
      Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
    End Sub

    Private Sub Items_ItemAdd(ByVal item As Object)

      'On Error GoTo ErrorHandler

    'Verifying it is an email item

      Dim Msg As Outlook.MailItem
      If TypeName(item) = "MailItem" Then
        Set Msg = item

    'Variables for names of folders, contacts.
    Dim nombres() As String
    Dim carpeta() As String
    Dim cuenta As Integer

    cuenta = 1

    ReDim nombres(1 To cuenta)
    ReDim carpeta(1 To cuenta)

    'Creating the new object application
    Dim oXL As Object
    Dim oxlWorkbook As Object

    'setting the workbook that has the list of contacts and the appropriate folder

    Set oXL = CreateObject("Excel.Application")
    Set oxlWorkbook = oXL.Workbooks.Open("C:\Users\a0224597\Videos\lista de persona.xlsx")
    oXL.Visible = True

    'Getting the list of the names of contacts and names of folders
    oxlWorkbook.Worksheets("Sheet1").Range("A2").Select
    oXL.Application.ScreenUpdating = False


    Do While Not IsEmpty(ActiveCell)
        nombres(cuenta) = oXL.application.ActiveCell.Value  'It says that active cell is Nothing. like when you forget to set an object and try to use it.
        carpeta(cuenta) = oXL.application. ActiveCell.Offset(0, 1).Value
        cuenta = cuenta + 1
        ReDim Preserve nombres(1 To cuenta)
        ReDim Preserve carpeta(1 To cuenta)
        oXL.application.ActiveCell.Offset(1, 0).Select
    Loop

    oxlWorkbook.Close savechanges:=False

    oXL.Application.ScreenUpdating = True
    oXL.Application.Quit
    Set oXL = Nothing
    Set oxlWorkbook = Nothing

    Dim objSourceFolder As Outlook.MAPIFolder
    Dim objDestFolder As Outlook.Folder
    Dim objVariant As Variant

    '
    Set objSourceFolder = Session.GetDefaultFolder(olFolderInbox)

    'this where we look for a match to know where this email belongs to 
    For intCount = objSourceFolder.Items.Count To 1 Step -1

        Set objVariant = objSourceFolder.Items.item(intCount)

        If objVariant.Class = olMail Then
            For i = 1 To cuenta
                
                If objVariant.Sender = nombres(i) Then
    '                MsgBox objVariant.Subject
                    
                    Set objDestFolder = Session.Folders("31 diciembre 2013").Folders(carpeta(i))
                    objVariant.Move objDestFolder
                    Set objDestFolder = Nothing
                    Exit For
                End If
            Next i
        End If

    Next intCount


      End If
    ProgramExit:
      Exit Sub
    ErrorHandler:
      MsgBox Err.Number & " - " & Err.Description
      oxlWorkbook.Close savechanges:=False 
        oXL.Application.ScreenUpdating = True
        oXL.Application.Quit
        Set oXL = Nothing
        Set oxlWorkbook = Nothing
      Resume ProgramExit
    End Sub

                             






    • Edited by bib22jason Thursday, March 20, 2014 9:43 PM
    Thursday, March 20, 2014 2:40 PM

Answers

  • Since you are accessing a method/property for another application, you need to make sure you reference it properly, try

    nombres(cuenta) = oXL.ActiveCell.Value

    ActiveCell's return type is a Cell, so only when you use ActiveCell.Value it will return the value.


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered


    Thursday, March 20, 2014 4:26 PM

All replies

  • For starters, the above is not a Macro, but a VBA procedure. Then , can you please highlight at what line the error is occurring?

    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Thursday, March 20, 2014 2:45 PM
  • Thanks for clarifying me about the macro, vba procedure. 

    I have edited the original post.

    I think the problem is when i set the workbook. and another thing sometimes Excel keeps running in the background, even if the VBA procedure runs without problems.

    Thursday, March 20, 2014 2:59 PM
  • You have already created the object in one line by using Early binding.

    Dim oXL As New Excel.Application
    Dim oxlWorkbook As Excel.Workbook

    What you should be using is,

    Dim oXL As Object
    Dim oxlWorkbook As Object




    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered

    Thursday, March 20, 2014 3:46 PM
  • Ok, let me try this.

    I have tried your solution and now it pops up another error. Well, it is the same error but in a different part of the code. i have highlighted where i'm getting the new error. 

    • Edited by bib22jason Thursday, March 20, 2014 4:10 PM
    Thursday, March 20, 2014 4:05 PM
  • Since you are accessing a method/property for another application, you need to make sure you reference it properly, try

    nombres(cuenta) = oXL.ActiveCell.Value

    ActiveCell's return type is a Cell, so only when you use ActiveCell.Value it will return the value.


    Happy to help ! When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answered


    Thursday, March 20, 2014 4:26 PM
  • Sorry, i was in a meeting.

    I have tried again the solution you offered me and the outcome is the same. i tried to attach a picture to get a better comprehension about the problem, but it says i cannot use images. but anyway  this is the error that i get after the change you suggested me.

    ActiveCell.Value = <Object variable or With block variable not set>

    i have already edited the original post in order to update the changes in the code.

    Best regards

    I have found the solution to this problem. As you told me, im refering to methods or properties of another application. So problem was in other lines.  the solution was to put the oXL.application.ActiveCell. in each line a use excel properties or methods. 

    Just as im writting this lines, i have seen your editeed answer and has the exact same solution. Thank you very much sir. i have edited the original post in order to leave the code that works properly.

    Best regards




    • Edited by bib22jason Thursday, March 20, 2014 9:42 PM
    Thursday, March 20, 2014 6:29 PM