• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Outlook Macro

Hi Guys

Can anyone please help to use the below macro for the shared Inbox. As presently I can only run and get the data for the personal inbox


Sub Q28274550_1()
Dim acct As Account
Dim fldr As MAPIFolder
Dim arr As Variant
Dim intArraySize As Integer
Dim elem As Integer
Dim xlapp As Object
Dim xlWB As Object
Dim xlSh As Object

    intArraySize = 0
    ReDim arr(2, 0)
    For Each acct In Application.Session.Accounts
        ReDim Preserve arr(2, intArraySize)
        Set fldr = Application.Session.GetFolderFromID(acct.DeliveryStore.StoreID).Folders("Inbox")
        Q28274550_1a fldr, intArraySize, arr
    Next
    Set xlapp = CreateObject("excel.application")
    xlapp.Visible = False
    xlapp.screenupdating = False
    xlapp.enableevents = False
    Set xlWB = xlapp.workbooks.Add
    Set xlSh = xlWB.sheets(1)
    For elem = 0 To UBound(arr, 2)
'        Debug.Print arr(0, elem) & "<>" & arr(1, elem) & "<>" & arr(2, elem)
        xlSh.cells(elem + 1, 1) = arr(0, elem)
        xlSh.cells(elem + 1, 2) = arr(1, elem)
        xlSh.cells(elem + 1, 3) = arr(2, elem)
    Next
    
    With xlSh.Sort
        .SortFields.Add Key:=xlSh.Range("B1:B100") _
            , SortOn:=0, Order:=1, DataOption:=0
        .SortFields.Add Key:=xlSh.Range("A1") _
            , SortOn:=0, Order:=1, DataOption:=0
        .SetRange xlSh.cells
        .Header = 2
        .MatchCase = False
        .Orientation = 1
        .SortMethod = 1
        .Apply
    End With
    
    
    xlSh.Range("1:3").Columns.autofit
    xlapp.screenupdating = True
    xlapp.enableevents = True
    xlapp.Visible = True

End Sub

Function Q28274550_1a(fldr As MAPIFolder, intArraySize As Integer, arr As Variant)
Dim subFolder As MAPIFolder
Dim strFilter As String
Dim lngItemCount As Long
Dim strStarttime As String
Dim strFinishTime As String
Dim folderItems As Object
Dim strStart As String
Dim varStart As Variant
Dim strEnd As String
Dim varEnd As Variant
Dim varPeriod As Variant
Dim strIncrement As Integer
Dim varIncrement As Variant

    strStart = "08:00:00"
    strEnd = "17:00:00"
    strIncrement = 30
    varStart = TimeValue(strStart)
    varEnd = TimeValue(strEnd)
    varIncrement = TimeValue("00:" & strIncrement & ":00")
    For varPeriod = varStart To varEnd Step varIncrement
        strStarttime = Format(Date + varPeriod, "ddddd h:nn AMPM")
        strFinishTime = Format(DateAdd("n", strIncrement, Date + varPeriod), "ddddd h:nn AMPM")
        strFilter = "[ReceivedTime] >= '" & strStarttime & "'" & " and " & "[ReceivedTime] < '" & strFinishTime & "'"
        Set folderItems = fldr.Items.Restrict(strFilter)
        lngItemCount = folderItems.Count
        ReDim Preserve arr(2, intArraySize)
        arr(0, intArraySize) = fldr.FolderPath
        arr(1, intArraySize) = Format(strStarttime, "hh:mm") & " - " & Format(strFinishTime, "hh:mm")
        arr(2, intArraySize) = lngItemCount
        intArraySize = intArraySize + 1
    Next
    
'    Debug.Print fldr.FolderPath & " <> " & lngItemCount
    For Each subFolder In fldr.Folders
        Q28274550_1a subFolder, intArraySize, arr
    Next

End Function 

Open in new window

0
surah79
Asked:
surah79
  • 2
  • 2
1 Solution
 
Chris BottomleyCommented:
I cannot test so I leave it to you to test ... or someone else to upload a valid solution but try the following:

Sub Q28274550_2()
Dim acct As Account
Dim fldr As MAPIFolder
Dim arr As Variant
Dim intArraySize As Integer
Dim elem As Integer
Dim xlapp As Object
Dim xlWB As Object
Dim xlSh As Object
Dim recip As Recipient

    intArraySize = 0
    ReDim arr(2, 0)
    For Each acct In Application.Session.Accounts
        ReDim Preserve arr(2, intArraySize)
        Set fldr = Application.Session.GetFolderFromID(acct.DeliveryStore.StoreID).Folders("Inbox")
        Q28274550_1a fldr, intArraySize, arr
    Next
    ReDim Preserve arr(2, intArraySize)
    Set recip = Application.Session.CreateRecipient("mail@domain.com")
    Set fldr = Application.Session.GetSharedDefaultFolder(recip, olFolderInbox)
    Q28274550_1a fldr, intArraySize, arr
    
    Set xlapp = CreateObject("excel.application")
    xlapp.Visible = False
    xlapp.screenupdating = False
    xlapp.enableevents = False
    Set xlWB = xlapp.workbooks.Add
    Set xlSh = xlWB.sheets(1)
    For elem = 0 To UBound(arr, 2)
'        Debug.Print arr(0, elem) & "<>" & arr(1, elem) & "<>" & arr(2, elem)
        xlSh.cells(elem + 1, 1) = arr(0, elem)
        xlSh.cells(elem + 1, 2) = arr(1, elem)
        xlSh.cells(elem + 1, 3) = arr(2, elem)
    Next
    
    With xlSh.Sort
        .SortFields.Add Key:=xlSh.Range("B1:B100") _
            , SortOn:=0, Order:=1, DataOption:=0
        .SortFields.Add Key:=xlSh.Range("A1") _
            , SortOn:=0, Order:=1, DataOption:=0
        .SetRange xlSh.cells
        .Header = 2
        .MatchCase = False
        .Orientation = 1
        .SortMethod = 1
        .Apply
    End With
    
    
    xlSh.Range("1:3").Columns.autofit
    xlapp.screenupdating = True
    xlapp.enableevents = True
    xlapp.Visible = True

End Sub

Function Q28274550_1a(fldr As MAPIFolder, intArraySize As Integer, arr As Variant)
Dim subFolder As MAPIFolder
Dim strFilter As String
Dim lngItemCount As Long
Dim strStarttime As String
Dim strFinishTime As String
Dim folderItems As Object
Dim strStart As String
Dim varStart As Variant
Dim strEnd As String
Dim varEnd As Variant
Dim varPeriod As Variant
Dim strIncrement As Integer
Dim varIncrement As Variant

    strStart = "08:00:00"
    strEnd = "17:00:00"
    strIncrement = 90
    varStart = TimeValue(strStart)
    varEnd = TimeValue(strEnd)
'    varIncrement = TimeValue("00:" & strIncrement & ":00")
    varIncrement = Format(DateAdd("n", strIncrement, (Date + TimeValue("00:00:00"))), "hh:mm:ss")
    For varPeriod = varStart To varEnd Step varIncrement
        strStarttime = Format(Date + varPeriod, "ddddd h:nn AMPM")
        strFinishTime = Format(DateAdd("n", strIncrement, Date + varPeriod), "ddddd h:nn AMPM")
        strFilter = "[ReceivedTime] >= '" & strStarttime & "'" & " and " & "[ReceivedTime] < '" & strFinishTime & "'"
        Set folderItems = fldr.Items.Restrict(strFilter)
        lngItemCount = folderItems.Count
        ReDim Preserve arr(2, intArraySize)
        arr(0, intArraySize) = fldr.FolderPath
        arr(1, intArraySize) = Format(strStarttime, "hh:mm") & " - " & Format(strFinishTime, "hh:mm")
        arr(2, intArraySize) = lngItemCount
        intArraySize = intArraySize + 1
    Next
    
'    Debug.Print fldr.FolderPath & " <> " & lngItemCount
    For Each subFolder In fldr.Folders
        Q28274550_1a subFolder, intArraySize, arr
    Next

End Function

Open in new window


Modify the string mail@domain.com at line 20 to reflect the users email address and with a bit of luck it'll work the same as before but with the extra pst.

Chris
0
 
surah79Author Commented:
Awesome it works like a charm
0
 
surah79Author Commented:
sorry Chris last bit , can we also able to include the subfolder of the Shared Inbox,can I ask this as another new question on this.
0
 
Chris BottomleyCommented:
Sorry yu caught me overnight … the script should already return the subfolders of the inbox as it uses exactly the same recursion as the other solution.

Can you perhaps provide more details on the difference?

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now