none
Process an Outlook.Selection in batches? /overcoming RPC limit issue RRS feed

  • Question

  • I am trying to write a VBA script that takes selected items from an outlook folder and adds a value to a field.  The problem is that I exceed the RPC limit of 255 with many of my selections.  I want to be able to take a subset of my selection (100 or so) and send them as either a selection or some other collection type to a separate subroutine for processing.  My hope is that each time the second subroutine processes a batch of 100 items it will close all the open rpc channels before being called again to process the next set.

    For my purposes, I need to be able to select the items and not run the code on all of the items in a folder.

    I can't figure out how to instantiate a subset of a selection.  Is there any way to do this?  

    And, do you think this will resolve my problem with the RPC channels?  I have tried to set the item variable to nothing after the field value is updated in the loop, I've tried to use the to use the Marshal.ReleaseCOMObject and GC.collect (I can't tell if these are actually available for VBA).

    Thanks for any advice you have!  I've included the code I have so far below.

    Stacey

    Sub outlookSelection()
        Dim selection As Outlook.selection
        Dim Folder As Outlook.Folder
        'Dim subSelection As Outlook.Collection  ?
        Dim item As Outlook.JournalItem

        Dim remainder As Integer
        Dim divisibleby100 As Integer
        
        
        Set Folder = ActiveExplorer.CurrentFolder
        Set items = Folder.items
        Set selection = ActiveExplorer.selection
        remainder = selection.Count Mod 100
        divisibleby100 = selection.Count - remainder
        
        For i = i To divisibleby100
            For j = j To j + 99
                Set subSelection = New Outlook.Collection
                
            Next
           
           'Call processSelection(subSelection)
        Next
       'For remainder
       'Set items = to remaining selection
       'Call processSelection(items)
          
    End Sub


    Public Sub processSelection(ByVal subSelection As Outlook.items)

        Dim journal As Outlook.JournalItem
        Dim objProperty As Outlook.UserProperty
        Dim objProperties As Outlook.UserProperties
     
        
        For i = 1 To items.Count
                'For each item in items
                    Set journal = item(i)  'necessary?...maybe to access user properties
                    Set objProperties = journal.UserProperties
                    Set objProperty = objProperties.Find("Collaborator")
                
                    objProperty = ""
                    journal.Save
                    Set journal = Nothing
        Next
    End Sub

    Wednesday, June 4, 2014 9:28 PM

Answers

  • I just updated the thread with a message that the problem was resolved.  It turns out I didn't test it well enough and I spoke to soon!

    If you have any other suggestions that may help me with this issue, I'd be very grateful.

    thanks,

    stacey

    Thursday, June 5, 2014 8:41 PM

All replies

  • Marshal.ReleaseComObject and GC.Collect are features of the .Net framework. There is no need to use such things in VBA. Also there is no need to use any sub-collections. A selection object represents a single object. You just need to define outlook objects in the loop decreasing the scope of living for variables. For example, you defined the journal, objProperty and objProperties object at the method scope. I'd recommend moving declarations into the for each loop instead.
    Thursday, June 5, 2014 12:38 PM
  • Each object variable you instantiate takes up 1 RPC channel. What you need to do in VBA code is set each object to Nothing in each pass through the loops.

    For example:

    Public Sub processSelection(ByVal subSelection As Outlook.items)

        Dim journal As Outlook.JournalItem
        Dim objProperty As Outlook.UserProperty
        Dim objProperties As Outlook.UserProperties
     
        
        For i = 1 To items.Count
                'For each item in items
                    Set journal = item(i)  'necessary?...maybe to access user properties
                    Set objProperties = journal.UserProperties
                    Set objProperty = objProperties.Find("Collaborator")
                
                    objProperty = ""
                    journal.Save

                     Set objProperty = Nothing

                      Set objProperties = Nothing

                     Set journal = Nothing
        Next
    End Sub

    Null objProperty and objProperties as well as journal. Do that for each object.


    Ken Slovak MVP - Outlook

    Thursday, June 5, 2014 2:38 PM
    Moderator
  • Hi Ken,

    Thanks for your response. I actually already tried to set the objproperty and objproperties variables to nothing.  It doesn't help with the problem.  I still get the following error "your server administrator has limited the number of items you can open simulataneously"

    That is why I was hoping that putting the processing loop in a different procedure would help - based on Sue Mosher's comment in the link below.

    http://www.outlookcode.com/threads.aspx?forumid=5&messageid=26963

    Do you think this would help? And, if so, can you offer any guidance on sending a subset of items from a selection to the processing loop? Or do you have any other ideas that may help me resolve this issue?

    Thanks again.

    Stacey

    Thursday, June 5, 2014 4:53 PM
  • Hi Eugene,

    Thanks for your response. I'm probably misunderstanding your suggestion. I've tried defining the outlook objects within the loop to deal with the RPC issue.  Is that what you mean?   It did not work.  I still got the error "your server administrator has limited the number of items you can open simulataneously".   Or will reducing the scope of the variables somehow help me to process selected items in batches in a separate procedure?

    Thanks again!

    Stacey

    Thursday, June 5, 2014 5:07 PM
  • Sue's comments were based on the poster using managed code. For VBA code there's no garbage collector, no Marshal space and so on.

    In VBA code about all you can do is to set objects to Nothing to release them.

    I don't think that calling a second procedure would help much. I think this is a case where that might create additional references to the objects.

    I'd try code something like this:

    Sub outlookSelection()
         Dim selection As Outlook.selection
         Dim item As Outlook.JournalItem
        Dim objProperties As Outlook.UserProperties
        Dim objProperty As Outlook.UserProperty
       
         Dim count As Long
         Dim counter As Long
         Dim i As Long
        
         Set selection = Application.ActiveExplorer.selection
         count = selection.count
        
         For i = 1 To count
            Set item = selection.item(i)
            Set objProperties = item.UserProperties
            Set objProperty = objProperties.Find("Collaborator")
           
            If (Not (objProperty Is Nothing)) Then
                objProperty.Value = ""
                item.Save
            End If
           
            Set objProperty = Nothing
            Set objProperties = Nothing
            Set item = Nothing
         Next
          
           Set selection = Nothing
     End Sub


    Ken Slovak MVP - Outlook

    Thursday, June 5, 2014 5:18 PM
    Moderator
  • If you notice, I declared the objects outside the loop. That way there's only 1 of each declared. The they're instantiated inside the loop and nulled inside the loop also. But only 1 set of objects is declared.

    Ken Slovak MVP - Outlook

    Thursday, June 5, 2014 5:21 PM
    Moderator
  • Thanks, Ken. I tried your code and got the same error message.  I did notice that the variables were defined outside the loop and understand why. I noticed that you defined a counter variable that was unused.  Is that intended to count the open RPC connections?  Is there a way to do that?

    Should I be using something other than VBA to solve this problem?

    Thursday, June 5, 2014 5:33 PM
  • Yes, you are on the right avenue. You need to decrease the scope of living Outlook objects and then set them to nothing. Also you can try to declare a separate method (sub in VBA) where you can declare objects and then set them to nothing. Just try to call such sub in the loop each time. Hope it will help.
    Thursday, June 5, 2014 5:47 PM
  • That counter variable could be used to track say 100 loop items.

    But normally doing what that code shows works. I don't think the results would be better using managed code such as VB.NET or C#, in fact the results could be worse.

    At what point does the code start to throw an exception? How far into the loop? How many items are selected overall?

    One could try breaking the loop into a separate procedure but my gut tells me that won't help.

    A wild stab might try DoEvents to see if that helps. DoEvents cedes the thread slot and primes the Windows message pump. Something like that might add a DoEvents call after each 20 or 30 loop passes as a first try to see if it helps.


    Ken Slovak MVP - Outlook

    Thursday, June 5, 2014 5:56 PM
    Moderator
  • Hi Ken,

    It throws an exception after between  240-250  journal items have been processed.  I need to be able to process thousands at a time if the script is going to be of much use.  I'm not sure I used DoEvents correctly...see code below.  But, I still got  the same error.

    I know that you are not confident that breaking the loop into a separate procedure will help, but it seems like I have nothing to lose at this point.  Can you advise me on how to send 100 items of a selection at a time to a separate procedure?  That is still what is preventing me from trying it.

    I really appreciate your help!

    Thanks,

    Stacey

    For i = 1 To count
            Set item = selection.item(i)
            Set objProperties = item.UserProperties
            Set objProperty = objProperties.Find("Collaborator")
            
            If (Not (objProperty Is Nothing)) Then
                objProperty.Value = ""
                item.Save
            End If
            
            Set objProperty = Nothing
            Set objProperties = Nothing
            Set item = Nothing
            If i Mod 20 = 0 Then
              DoEvents
              
            End If
         Next

    Thursday, June 5, 2014 6:22 PM
  • The easiest way to do what you want to try is just to send a start index to a sub-procedure. For example, start with counter = 1. Send that to the sub-procedure as a start loop index.

    The first call would send 1 to the procedure. If you're processing 20 items at a time, the second call would send 21, next would send 41, etc. The sub procedure would start processing at whatever number you passed to it.


    Ken Slovak MVP - Outlook

    Thursday, June 5, 2014 6:39 PM
    Moderator
  • I am working on figuring out your suggestion above (I'm pretty new to programming).  But, I did discover that the problem is specific to using outlook.selection.  I have no problem using the code for over 700 entries if I process all of the current folder items.  

    Is there any way to unselect each item in a selection (or somehow redefine the selection object minus that item) at the end of each for loop? 

    Thursday, June 5, 2014 7:21 PM
  • Thanks, Eugene.  I gave this a shot but I still got the same error.  I appreciate your help!
    Thursday, June 5, 2014 7:48 PM
  • I just read that I can not programmatically select items in the explorer window (or change the current selection object) Maybe there is a different approach for accomplishing the task that you can suggest?

    Ultimately, I am trying to add a value to a user defined field "Collaborator" based on the names in the Contacts field.  I want to be able select a set of items and then change the objproperty value programmatically based on that selection. There are 25000 entries in the journal with different contacts or combination of contacts having anywhere between 1 and 3000 entries.

    It seems that I can't use restrict, find or findnext for this purpose as they don't work with the contactnames property.

    Thursday, June 5, 2014 7:57 PM
  • I just updated the thread with a message that the problem was resolved.  It turns out I didn't test it well enough and I spoke to soon!

    If you have any other suggestions that may help me with this issue, I'd be very grateful.

    thanks,

    stacey

    Thursday, June 5, 2014 8:41 PM
  • I'm not sure I understand what's going on. You had mentioned that processing Selection fails with the RPC channel error, but code works if you process the Items collection? Is that the case? If so, wouldn't the Selection collection be a subset or the complete set of the Items collection?

    What version of Outlook are you using? If it's Outlook 2010 or later you can add and remove items from the Selection, and you can clear the Selection (AddToSelection, RemoveFromSelection, ClearSelection). 


    Ken Slovak MVP - Outlook

    Friday, June 6, 2014 1:50 PM
    Moderator