none
vba macro iteration through outlook tasks not working in new object library (12 vs 15) RRS feed

  • Question

  • my colleague got her office updated to 365 which holds 2013 version. Macro that has been working in 2007 without issues do not work now. Probably some changes in object libraries 12 vs 15 that I'm not aware off.

    Here's the code below. I get Application_defined or object-defined error on line:

    Worksheets("ActiveTasks").Cells(rowNo, Column_title).Value = eItem

    Through trial and error I found that something is wrong with "eItem". I tried defining as Outlook.TaskItem, remove bind overall, refocus worksheet. All worked fine in 2007 excel, but still get object error in 2013 version.

    Sub Extract_tasks_SPP()
    
    On Error GoTo ErrHandler
    
    Dim applOutlook As Outlook.Application
    Dim nsOutlook As Outlook.Namespace
    Dim eFolder As Outlook.folder
    Dim eItems As Outlook.Items
    Dim eItem As Object
    Dim eResItems As Outlook.Items
    Dim strCriteria As String
    
    Worksheets("ActiveTasks").Range("A:B").ClearContents
    
    Set applOutlook = New Outlook.Application
    Set nsOutlook = applOutlook.GetNamespace("MAPI")
    nsOutlook.Logon
    Set Recip = nsOutlook.CreateRecipient("InboxName")
    Set SharedFolder = nsOutlook.GetSharedDefaultFolder(Recip, olFolderTasks)
    
    
    Set eFolderSPP = SharedFolder
    Set eItemsSPP = eFolderSPP.Items
    
    If eItemsSPP.Count < 1 Then
        MsgBox "No Task Items Returned"
    Exit Sub
    End If
    rowNo = 1
    Column_title = 1
    For Each eItem In eItemsSPP
        Worksheets("ActiveTasks").Cells(rowNo, Column_title).Value = eItem ' I get object defined error on this line. I believe it is eItem that is causing issue
        rowNo = rowNo + 1
    Next
    
    
    Set applOutlook = Nothing
    Set nsOutlook = Nothing
    Set eFolderSPP = Nothing
    Set eItemsSPP = Nothing
    Set eResItemsSPP = Nothing
    
    Exit Sub
    ErrHandler:
        MsgBox "Opps, something went wrong. Script will exit"
        End
    End Sub

    Any ideas what's wrong?

    Monday, August 31, 2015 1:25 PM

Answers

  • Hi trmtt,

    >> I get Application_defined or object-defined error on line
    I made a test with your code under Office 2013, and I could reproduce your issue. In my option, you could not set a mailItem object to a cell in worksheet. I am not sure what object you want from the mailItem object, I assume that you want SenderName, and then you could use the code below:

        Worksheets("ActiveTasks").Cells(rowNo, Column_title).Value = eItem.SenderName 

    For other properties you could use with mailItem, you could refer the link below:
    # MailItem Object (Outlook)
    https://msdn.microsoft.com/en-us/library/office/ff861332.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by trmtt Wednesday, September 2, 2015 11:06 AM
    Wednesday, September 2, 2015 7:48 AM

All replies

  • Hello,

    Make sure the Outlook reference was checked in the new Office version. Most probably you simply forgot to add an Outlook reference.

    The Value property of the Range class allows to set a Variant value that represents the value of the specified range. Try to use any TaskItem's property for assigning to the Value. 

    Finally, try to use the Option Explicit Statement in the code.

     

    Monday, August 31, 2015 1:32 PM
  • Hi,

    thanks for the reply. File is a copy and I checked references that all libraries were selected. Only difference I could see was version 12 vs 15 in office 365.

    Not sure about TaskItem property. As I mentioned in the post, I did try to assign Dim eItem As Outlook.TaskItem, but it gave me same error (worked in 2007).

    Monday, August 31, 2015 1:45 PM
  • Have you tried to play with the Option Explicit Statement in the code?
    Monday, August 31, 2015 3:12 PM
  • I have tried Option explicit statement. I have declared all variables but that does not make any difference - it still executes up to that line.

    I think something is wrong in this loop:

    For Each eItem In eItemsSPP

    I know that statement If eItemsSPP.Count < 1 is valid as it returns number of tasks in the email, but for some reason when I try to loop through each item and write it to the cell it gets stuck.

    Unfortunately I can'd do extensive testing as I do not have new office 365 on my pc yet.

    Tuesday, September 1, 2015 8:52 AM
  • Hi trmtt,

    >> I get Application_defined or object-defined error on line
    I made a test with your code under Office 2013, and I could reproduce your issue. In my option, you could not set a mailItem object to a cell in worksheet. I am not sure what object you want from the mailItem object, I assume that you want SenderName, and then you could use the code below:

        Worksheets("ActiveTasks").Cells(rowNo, Column_title).Value = eItem.SenderName 

    For other properties you could use with mailItem, you could refer the link below:
    # MailItem Object (Outlook)
    https://msdn.microsoft.com/en-us/library/office/ff861332.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Marked as answer by trmtt Wednesday, September 2, 2015 11:06 AM
    Wednesday, September 2, 2015 7:48 AM
  • Hi Edward,

    many thanks for reply. It just struck me that I am actually trying to get task item not a mail. I can't test solution at the moment, but your logic sounds good. Would it work if I tried to get subject?


    Worksheets("ActiveTasks").Cells(rowNo, Column_title).Value = eItem.Subject

    I think it took subject property by default in 2007 version and now it needed to be specified. I am marking your reply as an answer as I am confident that will fix the issue. 

    Wednesday, September 2, 2015 11:05 AM