none
Access email in public folder by VBScript in Outlook 2013 RRS feed

  • Question

  • How to use VBScript or other way to access email in public folder?

    I would like to filter some emails in public folder (filter the sent date time) and then send notification email to client.

    Anyone can help?

    Thank you!

    Thursday, March 31, 2016 9:20 AM

All replies

  • Hello,

    You can use the GetDefaultFolder method of the Namespace class which returns a Folder object that represents the default folder of the requested type for the current profile. You just need to pass the olPublicFoldersAllPublicFolders value. For example:

    ' GetFolder - Gets a Public folder based on a string path - e.g. 
    'If Folder name in English is
    'Public Folders\All Public Folders\Europeen Workflow
    'The just pass in "Europeen Workflow'
     
    Public Function GetPublicFolder(strFolderPath)
        
        Dim colFolders 
        Dim objFolder 
        Dim arrFolders 
        Dim i 
        On Error Resume Next
        strFolderPath = Replace(strFolderPath, "/", "\")
        arrFolders = Split(strFolderPath, "\")
         
        Set objFolder = Application.Session.GetDefaultFolder(18)
        Set objFolder = objFolder.Folders.Item(arrFolders(0))
        If Not objFolder Is Nothing Then
            For i = 1 To UBound(arrFolders)
                Set colFolders = objFolder.Folders
                Set objFolder = Nothing
                Set objFolder = colFolders.Item(arrFolders(i))
                If objFolder Is Nothing Then
                    Exit For
                End If
            Next
        End If
        Set GetPublicFolder = objFolder
        Set colFolders = Nothing
         Set objApp = Nothing
        Set objFolder = Nothing
    End Function

    Take a look at the following pages for more information:

    Get Public Folder

    How to get reference to Public Folder Store using Outlook Object Model for Outlook 2010?

    Working with VBA and non-default Outlook Folders

    • Proposed as answer by David_JunFeng Friday, April 1, 2016 2:36 AM
    Thursday, March 31, 2016 10:30 AM
  • Application.Session.GetDefaultFolder(18) is return empty.

    And I would like to ask how to filter email in public folder.

    Friday, April 1, 2016 3:00 AM
  • >>>Application.Session.GetDefaultFolder(18) is return empty.

    According to your description, I suggest that you could make sure whether Public Folders have items? you could Press Ctrl+6 to see Public Folders in the Folder Pane:


    For more information, click here to refer about Create a public folder in Outlook 

    and here to refer about How to get reference to Public Folder Store using Outlook Object Model for Outlook 2010?

    >>>And I would like to ask how to filter email in public folder

    Filtering Email Messages in Microsoft Outlook, you could refer about Office Space: Tips and Tricks for Scripting Microsoft Office Applications

    Tuesday, April 5, 2016 7:15 AM
  • The public folder has items. 

    My public folder path is : All Public Folders\A store\B store

    And the code:

        Dim strFolderPath
        Dim colFolders 
        Dim objFolder 
        Dim arrFolders 
        Dim i 
        On Error Resume Next
        strFolderPath("A store\B store")
        strFolderPath = Replace(strFolderPath, "/", "\")
        arrFolders = Split(strFolderPath, "\")
         
        Set objFolder = Outlook.Application.Session.GetDefaultFolder(18)
    	Msgbox objFolder
        Set objFolder = objFolder.Folders.Item(arrFolders(0))
        If Not objFolder Is Nothing Then
            For i = 1 To UBound(arrFolders)
                Set colFolders = objFolder.Folders
                Set objFolder = Nothing
                Set objFolder = colFolders.Item(arrFolders(i))
                If objFolder Is Nothing Then
                    Exit For
    		else
    		Msgbox objFolder
                End If
            Next
        End If
        Set GetPublicFolder = objFolder
        Set colFolders = Nothing
         Set objApp = Nothing
        Set objFolder = Nothing

    Can I do the filter like this? It can filter all email items received over 30 mins.

    Set col = fldr.Items
    Set fltr = col.Filter
    fltr.TimeLast = DateAdd("n", -30, Now)


    • Edited by _dson Tuesday, April 5, 2016 8:43 AM
    Tuesday, April 5, 2016 8:36 AM
  • >>>My public folder path is : All Public Folders\A store\B store

    Based on my testing, there are some errors in your VBA code, you could modify your code like below:
    strFolderPath = "A store\B store"
    strFolderPath = Replace(strFolderPath, "/", "\")
    arrfolders = Split(strFolderPath, "\")
    otherwise I suggest that you could set a breakpoint to debug your codes.

    >>>Can I do the filter like this? It can filter all email items received over 30 mins.

    No, you could modify below code based on your requirement:
    Sub DemoFilter()
        Dim restriction As String
        
        Dim startTime As String
        startTime = Format(DateAdd("n", -120, Now), "yyyy-mm-dd hh:mm")
        Debug.Print startTime
        
        Dim endTime As String
        endTime = Format(Now, "yyyy-mm-dd hh:mm")
        Debug.Print endTime
        
        restriction = " [ReceivedTime] > '" & startTime & "' and  [ReceivedTime] < '" & endTime & "'"
        
        Set folder = Application.Session.GetDefaultFolder(6)
        Set restrItems = folder.Items.Restrict(restriction)
        For Each item In restrItems
          If TypeName(item) = "MailItem" Then
            Debug.Print item.ReceivedTime & " - " & item.Subject
          End If
        Next
    End Sub

    Wednesday, April 6, 2016 9:32 AM