none
VBScript - Items recieved yesterday RRS feed

  • Question

  • Hi guys. I need to improve script below - to show number of emails recieved yesterday for each folder:

    FolderName  |||  ItemsOverall  |||  ItemsRecievedYesterday

    Like:

    Inbox - 60 - 12

    Folder - 20 - 2

    ...

    TOTAL - 140 - 128

    Could you give me a hand with it please?

    Option Explicit
    Dim objOutlook, objNamespace, lngTotal, objFolder, dctExcluded, aExclude, item, objFSO, objFile
    aExclude = Array("Conversation History", "Deleted Items", "Sent Items", "Sync Issues", "Calendar", "RSS Feeds", "Drafts", "Contacts", "Tasks", "Journal", "Outbox", "Quarantine","Junk E-mail")
    Set dctExcluded = CreateObject("Scripting.Dictionary")
    For Each item In aExclude
        dctExcluded.Add LCase(item),item
    Next
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile("C:\file1.htm")
    For Each objFolder In objNamespace.Folders
            Call EnumFolder(objFolder, 0)
    Next
    Wscript.Echo "Total: " & CStr(lngTotal)
    Sub EnumFolder(objParent, level)
        ' Recursive subroutine to count items in folders.
        Dim objSubFolder, tabs
        If Not dctExcluded.Exists(LCase(objParent.Name)) and objParent.Items.Count >=50 Then
                Wscript.Echo objParent.Name & " - " & objParent.Items.Count
                lngTotal = lngTotal + objParent.Items.Count
        Else
        End If
        For Each objSubFolder In objParent.Folders
            Call EnumFolder(objSubFolder, level + 1 )
        Next
    End Sub


    • Edited by Roman Kuritsyn Tuesday, June 5, 2012 4:03 AM
    • Moved by Bill_Stewart Monday, June 18, 2012 2:13 PM Move to more appropriate forum (From:The Official Scripting Guys Forum!)
    Monday, June 4, 2012 4:54 PM

Answers

  • The documents link uyou posted will not work because if has some syntax and other technnical errors.  Thisi swhat i meant about many of the examples.  Som work under many very narrow circumstances.  This one is attempting to demonstrate how to use different forms of the filter.  It, like many other examples, assumes a need for lots of quotes.  In most cases these won't work.

    The article is attempting to also demonstrate the use of a 'Restrict' filter applied to an 'Items' collection.  This gives you a custom and filtered view of the collection.

    By adding a 'Restrict' filter to teh folders that are retrieved you can get the items received 'yesterday.  Have you tried it yet?

    It should be this:

    Set restricted = objParent.Items.Restrict( sFilter)
    Wscript.Echo objParent.Name & " - " & restricted.Count
    lngTotal = lngTotal + restricted.Count

    This would give you the count of only the items revealed with the filter.

    ¯\_(ツ)_/¯



    • Edited by jrv Tuesday, June 5, 2012 6:14 PM FIxed code example
    • Marked as answer by Roman Kuritsyn Tuesday, June 19, 2012 4:03 AM
    Tuesday, June 5, 2012 4:55 PM

All replies

  • You need to look at each and every mail item and check its date.  This is a big task.  It is not two or three lines so, unless someone has a script already you are kind of on your own.


    ¯\_(ツ)_/¯


    • Edited by jrv Monday, June 4, 2012 8:55 PM
    Monday, June 4, 2012 6:18 PM
  • Please keep in mind that this is a peer-to-peer support forum, and we typically don't have the resources to handle custom programming requests. When you have exact requirements and want someone to write some customized code for you, it is customary to pay for that kind of a service.

    Bill

    Monday, June 4, 2012 8:49 PM
  • Well, could you tell me in what way should I go please?

    1) I know there is MailItem.ReceivedTime property but I don't know which parent object should I use for it. No objParent.Items.MailItem.ReceivedTime neither objFolder Items.MailItem.ReceivedTime are working. There is no such property for these objects.

    2) I know there is such parametr as yesterday.

    So could you give me some easy example with this "ReceivedTime" to see how it works?

    Tuesday, June 5, 2012 4:13 AM
  • We're all keeping it all the time. I'm waiting for guys' help and understand they can be busy a lot. And I apreciate their help as well.

    Tuesday, June 5, 2012 4:36 AM
  • Well, could you tell me in what way should I go please?

    1) I know there is MailItem.ReceivedTime property but I don't know which parent object should I use for it. No objParent.Items.MailItem.ReceivedTime neither objFolder Items.MailItem.ReceivedTime are working. There is no such property for these objects.

    2) I know there is such parametr as yesterday.

    So could you give me some easy example with this "ReceivedTime" to see how it works?

    1. http://msdn.microsoft.com/en-us/library/aa171873(v=office.11).aspx

        ReceivedTime is a preperty of a MailItem and not a folder property.

    2. There is no such property as 'YesterDay'.


    ¯\_(ツ)_/¯

    Tuesday, June 5, 2012 7:37 AM
  • Ok - I got curious.  How to leverage  a search folder in VBScript.

    I started by doing this in VBA inOutlook and, with a little help from PowerShell,   I converted it to VBScript.  It actually works very nicely.

    Public m_SearchComplete 'As Boolean

    TestSearchForMultipleFolders

    Sub TestSearchForMultipleFolders() Const olFolderInbox = 6 Const olFolderSentMail = 5 Dim Scope 'As String Dim Filter 'As String Dim MySearch 'As Outlook.Search Dim MyTable 'As Outlook.Table Dim nextRow 'As Outlook.Row Set objOutlook = WScript.CreateObject("Outlook.Application") WScript.ConnectObject objOutlook ,"NOTIFY_" Set objNamespace = objOutlook.GetNamespace("MAPI") 'Establish scope for multiple folders Scope = "'" & objNamespace.GetDefaultFolder(olFolderInbox).FolderPath & "'" '& "','" & objNamespace.GetDefaultFolder(olFolderSentMail).FolderPath & "'" 'Establish filter If objNamespace.DefaultStore.IsInstantSearchEnabled Then Filter = Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " ci_phrasematch 'Office'" Else Filter = "urn:schemas:httpmail:subject like '%popeye%'" 'Filter = "urn:schemas:httpmail:datereceived >= '06/05/2012 12:00AM' " Filter = "urn:schemas:httpmail:datereceived >= 'yesterday' AND urn:schemas:httpmail:datereceived < 'today'" End If Set MySearch = objOutlook.AdvancedSearch(Scope, Filter, True,"MySearch") While m_SearchComplete <> True WScript.Sleep 500 Wend MsgBox MySearch.Results.Count Set MyTable = MySearch.GetTable 'Do Until MyTable.EndOfTable 'Set nextRow = MyTable.GetNextRow() 'Debug.Print nextRow("Subject") 'Loop End Sub Sub NOTIFY_AdvancedSearchComplete(SearchObject) If SearchObject.Tag = "MySearch" Then m_SearchComplete = True End If End Sub

    The above code is a template the returns the count of all items with a 'datereceived' >= yesterday and < today.

    The biggest pain in working with Outlook is that most of teh filter values are either undocumented, documented incorrectly or incompletely documented in 25 diferent places.  Microsoft has been working on fixing this but it appears they are years behind the rest of us.


    ¯\_(ツ)_/¯


    • Edited by jrv Tuesday, June 5, 2012 11:04 AM
    • Marked as answer by Roman Kuritsyn Tuesday, June 5, 2012 11:51 AM
    • Unmarked as answer by Roman Kuritsyn Wednesday, June 6, 2012 10:47 AM
    Tuesday, June 5, 2012 11:01 AM
  • Finally a copy that that processes all connected 'stores' (datafiles and services).

    Public m_SearchComplete 'As Boolean
    '
    TestSearchForMultipleFolders
    '
    Sub TestSearchForMultipleFolders()
        Const olFolderInbox = 6
        Const olFolderSentMail = 5
        Dim sScope 'As String
        Dim sFilter 'As String
        Dim MySearch 'As Outlook.Search
        Dim MyTable 'As Outlook.Table
        Dim nextRow 'As Outlook.Row
    '    
        Set objOutlook = WScript.CreateObject("Outlook.Application")
        ' we need to conenct the object to receive events
        WScript.ConnectObject objOutlook ,"NOTIFY_"
        Set objNamespace = objOutlook.GetNamespace("MAPI")    
    '    
    '    
        'some useful search filters
        sFilter = "urn:schemas:httpmail:subject like '%popeye%'"
        'sFilter = "urn:schemas:httpmail:datereceived >= '06/05/2012 12:00AM' "
        sFilter = "urn:schemas:httpmail:datereceived >= 'yesterday' AND urn:schemas:httpmail:datereceived <= 'today'"
    '    
        For Each store In objNamespace.Stores
     '   
            sScope = "'" & objNamespace.GetDefaultFolder(olFolderInbox).FolderPath & "'" 
            sScope = "'\\" & store.Displayname & "'"
            Set MySearch = objOutlook.AdvancedSearch(sScope, sFilter, True,"MySearch")
    '
            m_SearchComplete = False
            While m_SearchComplete <> True
                WScript.Sleep 500
            Wend
    '        
            WScript.Echo sScope & " Total Items:" & MySearch.Results.Count
    '                
        Next
    '    
    End Sub
    '  
    Sub NOTIFY_AdvancedSearchComplete(SearchObject)
        If SearchObject.Tag = "MySearch" Then
            m_SearchComplete = True
        End If
    End Sub


    ¯\_(ツ)_/¯

    Tuesday, June 5, 2012 11:26 AM
  • Thank you a lot! I'll try to combine your example with my 1st one and post it here if the result would be good.

    Thanks again jrv for your time

    Tuesday, June 5, 2012 11:51 AM
  • One more question - could I use kind of objParent.Items.Find (urn:schemas:httpmail:datereceived)>= 'yesterday' method in 1st example above?

    Folowing this document  and this one it makes sense as Items.Find



    Tuesday, June 5, 2012 12:34 PM
  • One more question - could I use kind of objParent.Items.Find (urn:schemas:httpmail:datereceived)>= 'yesterday' method in 1st example above?

    Folowing this document  and this one it makes sense as Items.Find



    What is objParent.Items.Find?   The links you posted have nothing to do with 'Find'.  The is no such method as 'Find' in Outlook.  Seraches are done via the Outlook Application object or other object using  'AdvancedSearch'.  AdvancedSearch uses a DASL filter and a Scope. It is how teh "Search FOlders" mechanism of Outlook is implemented.  You can run an AdvancedSearch and save the Search object as  a 'SearchFolder'.


    ¯\_(ツ)_/¯

    Tuesday, June 5, 2012 4:49 PM
  • Also, I would add that questions about the details of how to program the Outlook object model are probably best asked in the Outlook for Developers forum.

    Bill

    Tuesday, June 5, 2012 4:54 PM
  • The documents link uyou posted will not work because if has some syntax and other technnical errors.  Thisi swhat i meant about many of the examples.  Som work under many very narrow circumstances.  This one is attempting to demonstrate how to use different forms of the filter.  It, like many other examples, assumes a need for lots of quotes.  In most cases these won't work.

    The article is attempting to also demonstrate the use of a 'Restrict' filter applied to an 'Items' collection.  This gives you a custom and filtered view of the collection.

    By adding a 'Restrict' filter to teh folders that are retrieved you can get the items received 'yesterday.  Have you tried it yet?

    It should be this:

    Set restricted = objParent.Items.Restrict( sFilter)
    Wscript.Echo objParent.Name & " - " & restricted.Count
    lngTotal = lngTotal + restricted.Count

    This would give you the count of only the items revealed with the filter.

    ¯\_(ツ)_/¯



    • Edited by jrv Tuesday, June 5, 2012 6:14 PM FIxed code example
    • Marked as answer by Roman Kuritsyn Tuesday, June 19, 2012 4:03 AM
    Tuesday, June 5, 2012 4:55 PM
  • Also, I would add that questions about the details of how to program the Outlook object model are probably best asked in the Outlook for Developers forum.

    Bill

    Tuesday, June 5, 2012 4:56 PM
  • Sorry - the example had an error,

    Const strFilter = "@SQL=%yesterday(""urn:schemas:httpmail:datereceived"")%"
    ...
    ...
    ...
    Set restricted = objParent.Items.Restrict(sFilter)
    Wscript.Echo objParent.Name & " - " & restricted.Count
    lngTotal = lngTotal + restricted.Count


    ¯\_(ツ)_/¯





    • Edited by jrv Tuesday, June 5, 2012 6:44 PM
    Tuesday, June 5, 2012 6:16 PM
  • Thank you jrv, I've tried something like this (below) but it end with error:

    Line:16 Char 1 Error: Object required: "

    800A01A8. Sounds strange for me...

    Option Explicit
    Dim objOutlook, objNamespace, lngTotal, objFolder, dctExcluded, aExclude, item, objFSO, objFile, sFilter,objParent, restricted
    aExclude = Array("Conversation History", "Deleted Items", "Sent Items", "Sync Issues", "Calendar", "RSS Feeds", "Drafts", "Contacts", "Tasks", "Journal", "Outbox", "Quarantine","Junk E-mail")
    Set dctExcluded = CreateObject("Scripting.Dictionary")
    For Each item In aExclude
        dctExcluded.Add LCase(item),item
    Next
    Const strFilter = "@SQL=%yesterday(""urn:schemas:httpmail:datereceived"")%"
    'Const strFilter = "urn:schemas:httpmail:datereceived >= 'yesterday' AND urn:schemas:httpmail:datereceived <= 'today'"
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile("C:\file1.htm")
    Set restricted = objParent.Items.Restrict(sFilter)
        'sFilter = "urn:schemas:httpmail:datereceived >= 'yesterday' AND urn:schemas:httpmail:datereceived <= 'today'"
    For Each objFolder In objNamespace.Folders
            Call EnumFolder(objFolder, 0)
    Next
    Wscript.Echo "Total: " & CStr(lngTotal)
    Sub EnumFolder(objParent, level)
        ' Recursive subroutine to count items in folders.
        Dim objSubFolder, tabs
        If Not dctExcluded.Exists(LCase(objParent.Name)) and objParent.Items.Count >=50 Then
    Wscript.Echo objParent.Name & " - " & restricted.Count
    lngTotal = lngTotal + restricted.Count
        Else
        End If
        For Each objSubFolder In objParent.Folders
            Call EnumFolder(objSubFolder, level + 1 )
        Next
    End Sub

    Wednesday, June 6, 2012 9:28 AM
  • You have added a file object for no reason.  Yu haev also placed the filter in the wrong place.  It must be where the object exists and yu have stuck it in an arbitrary location.  The filter must be applied to each specific folder. It will only filter the folder after you havve a refrence to the folder.


    ¯\_(ツ)_/¯

    Wednesday, June 6, 2012 10:00 AM
  • WOW, it works!!! :) Thanks a lot. Jrv - you rocks :)

    Option Explicit
    Dim objOutlook, objNamespace, lngTotal1,lngTotal2, objFolder, dctExcluded, aExclude, item, objFSO, objFile, objParent, restricted
    aExclude = Array("Conversation History", "Deleted Items", "Sent Items", "Sync Issues", "Calendar", "RSS Feeds", "Drafts", "Contacts", "Tasks", "Journal", "Outbox", "Quarantine","Junk E-mail")
    Set dctExcluded = CreateObject("Scripting.Dictionary")
    For Each item In aExclude
        dctExcluded.Add LCase(item),item
    Next
    'Const strFilter = "urn:schemas:httpmail:datereceived >= 'yesterday' AND urn:schemas:httpmail:datereceived <= 'today'"
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Set objFile = objFSO.CreateTextFile("C:\file1.htm")
    Const sFilter = "@SQL=%yesterday(""urn:schemas:httpmail:datereceived"")%"
        'sFilter = "urn:schemas:httpmail:datereceived >= 'yesterday' AND urn:schemas:httpmail:datereceived <= 'today'"
    For Each objFolder In objNamespace.Folders
            Call EnumFolder(objFolder, 0)
    Next
    Wscript.Echo "Total - " & CStr(lngTotal1) & " - " & CStr(lngTotal2)
    Sub EnumFolder(objParent, level)
        ' Recursive subroutine to count items in folders.
        Dim objSubFolder, tabs
    Set restricted = objParent.Items.Restrict(sFilter)
        If Not dctExcluded.Exists(LCase(objParent.Name)) and objParent.Items.Count >=50 Then
    Wscript.Echo objParent.Name & " - " & objParent.Items.Count & " - " & restricted.Count
    lngTotal1 = lngTotal1 + objParent.Items.Count
    lngTotal2 = lngTotal2 + restricted.Count
        Else
        End If
        For Each objSubFolder In objParent.Folders
            Call EnumFolder(objSubFolder, level + 1 )
        Next
    End Sub


    Wednesday, June 6, 2012 10:45 AM
  • Roman - you are not supposed to mark yourself as an answer.  You are supposed to mark the reponse that solved your issue as thr answer.  This is one way to help people find answers.

    Your code still has some cosmetic issues but that can wait until another time.

    Good luck.


    ¯\_(ツ)_/¯


    • Edited by jrv Monday, June 18, 2012 2:40 PM
    Wednesday, June 6, 2012 3:07 PM
  • Strange behavior of example above. It looks like script counts all objects in folder if I use "Conversation view" (I mean not only emails in each particular folder but all sent items also).

    Does somebody knew the way how to avoid it? (using a "Conversation view" is nesessary)

    Monday, June 18, 2012 10:16 AM
  • Strange behavior of example above. It looks like script counts all objects in folder if I use "Conversation view" (I mean not only emails in each particular folder but all sent items also).

    Does somebody knew the way how to avoid it? (using a "Conversation view" is nesessary)

    You are asking the same question over and over in different ways.  Each time you are changing the question because the answer does not gove you something.  What is it you are trying to count.

    'Conversation' is a view and not a folder.  You cannot use it to count items in a folder.  YOu need to think carefully about what you are trying to accomplish and be sure that your question makes sense,


    ¯\_(ツ)_/¯

    Monday, June 18, 2012 2:43 PM