Solved

Outlook Macro

Posted on 2013-10-22
21
440 Views
Last Modified: 2013-11-07
Hi

I am managing 9 outlook inboxes with various subfolders. Is it possible to create a report in excel via macro with a break down like how many email received every half an hour in the whole day across each inbox.

Thanks
Output in Excel
\\\customerservice\     8:00-8:30 am - 10
\\\customerservice\      8:30 - 9:00  - 12
\\\customerservice\     9:00 - 9:30  - 14
0
Comment
Question by:surah79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
21 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39623605
surah79

Can you confirm which version of outlook is in use and whether you need to run the script from excel or outlook?

Chris
0
 

Author Comment

by:surah79
ID: 39626242
14.0.6129.5000(32 bit) from outlook but excel is also fine.
Thanks
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39626576
I haven't a clue what the number means … can you talk in terms of the 'common' designation for example outlook 2010 / 2011 / 2013?

note if I use the example of mine then its 14 something in the full version but outlook 2011 in the common 'version'.  I recognise when I say version you have responded literally but what i need is the boxed version rather than the low level detail.

Chris
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:surah79
ID: 39626717
Outlook 2010
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39627049
Version is good to allow most things, when you say inbox folders are these sub folders of the one inbox or multiple psts each of which has an inbox?

Chris
0
 

Author Comment

by:surah79
ID: 39628034
Yes multiple sub folder in one inbox,so I will keep changing the inbox name to generate the report for the other inbox with multiple folder and then combine everything
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39628044
Sorry but trying to get my head around this:

pst1
    inbox
        folder 1
        folder 2
        …
        folder n
pst2
    inbox
        folder a
        folder b
        …
        folder z

pstn
    inbox
        folder 1a
        folder 2b
        …
        folder nz

or something else

Chris
0
 

Author Comment

by:surah79
ID: 39628110
Yep
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39628164
I believe something can be done but processing so many sub folders will take some time so how far down do we need to go all sub folders or just the first level and do you want the mails identifying against the past or by folder?
0
 

Author Comment

by:surah79
ID: 39629180
Hi just want to go all sub folder down ;mails identifying against  should be the time slot
8:30 - 9:30  20
9:30 - 10:00 30
Then I can run 9 times at diff inboxes separately and make the combine report
Inbox structure is as below
Inboxa
  Sybfolder1
  SSub folder 2
Sub folder 3
Sub folder 4
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39629462
I was assuming you wanted one script that ran through all the psts?

My latest question however was do you want:

\\\customerservice\     8:00-8:30 am - 10
\\\customerservice\      8:30 - 9:00  - 12

or

\\\customerservice\     8:00-8:30 am - 2
\\\customerservice\subfolder1     8:00-8:30 am - 5
\\\customerservice\Subsubfolder1     8:00-8:30 am - 3
etc

Chris
0
 

Author Comment

by:surah79
ID: 39629464
Second one with the sub folder
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39629889
I have some test code that evidences the basics to meet this however at the moment it processes folders then times and this is another area where the requirement needs refining:

\\\customerservice\     8:00-8:30 am - 2
\\\customerservice\     8:30-9:00 am - 1
\\\customerservice\     9:00-9:30 am - 5

\\\customerservice\subfolder1     8:00-8:30 am - 3
\\\customerservice\subfolder1     8:30-9:00 am - 5
\\\customerservice\subfolder1     9:00-9:30 am - 2

or

\\\customerservice\     8:00-8:30 am - 2
\\\customerservice\subfolder1     8:00-8:30 am - 3
\\\customerservice\     8:30-9:00 am - 1
\\\customerservice\subfolder1     8:30-9:00 am - 5
\\\customerservice\     9:00-9:30 am - 5
\\\customerservice\subfolder1     9:00-9:30 am - 2

Chris
0
 

Author Comment

by:surah79
ID: 39629976
Second one
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 39630660
This should do it then

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


Chris
0
 

Author Closing Comment

by:surah79
ID: 39631895
Chris you are awesome. Thanks a million, you are saviour
0
 

Author Comment

by:surah79
ID: 39631918
Hi Chris

Can I open another question and ask you If have to use this  for  another  Shared Inbox where I have to put his address

Thanks
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 39631927
Hmmm

I guess it should be doable though it would need a different call I think the overall process will work much the same for the shared inbox. so ask away and someone will answer … the hard bit is done I think and i'll think about it a bit myself

Chris
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below errors for MS Exchange Server 2013 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question