Consulting

Results 1 to 14 of 14

Thread: Tracking Outlook emails in Excel dashboard

  1. #1
    VBAX Newbie
    Joined
    Jul 2010
    Posts
    5
    Location

    Post Tracking Outlook emails in Excel dashboard

    I am designing an Excel dashboard to monitor my team's daily operational activities.

    One of their tasks is to send a series of emails throughout the day with specific headings and attachments. I am a recipient of each of these emails and so I have to check my "Business Management\MI" Outlook folder (I have an Outlook rule that sends these emails to that folder) to see whether they have sent each one.

    However, I want my Excel dashboard to present a real-time (or button-triggered, if necessary) summary of whether each email has been received at the time of refreshing the dashboard (or clicking the button).

    For example, the emails in my MI folder could be:

    Subject: Email A - Received: Thu 15/07/2010 10:12
    Subject: Email B - Received: Thu 15/07/2010 09:36
    Subject: Email C - Received: Wed 14/07/2010 14:57
    Subject: Email A - Received: Wed 14/07/2010 14:30
    Subject: Email D - Received: Wed 14/07/2010 13:52

    So I want my dashboard summary to show:

    Thursday 15 July 2010
    Email A: Sent 10:12
    Email B: Sent 9:36
    Email C: Not yet sent
    Email D: Not yet sent

    I've searched the 'net for a couple of days now but haven't found what I'm looking for, and what I have found hasn't worked.

    I'm using Outlook 2007 (v12) and Excel 2002 (v10).

    Any help gratefully received!

    Thanks

  2. #2
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Can you confirm:
    • Each email sent only once each day, or do we need to count the number of each type for each day?
    • Would you be looking at unread emails only or all emails in the folder?
    You should be able to loop through all the emails in a specific folder fairly easily, so it's just a matter of deciding what to do with them once you've read them.

    This should get you started - please note it is untested (don't have Outlook onsite here)

    [VBA]
    Dim objNS As Namespace
    Dim objInbox As MAPIFolder
    Dim objMyFolder As MAPIFolder
    Dim objItem As MailItem


    Set objNS = objOL.GetNamespace("MAPI")
    Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
    Set objMyFolder = objInbox.Folders("FOLDER_NAME_HERE") ' assumes your folder is sub of Inbox

    For Each objItem In objMyFolder.Items
    ' test unread
    If objItem.Unread Then
    ' do something

    'objItem.Subject
    'objItem.Sent
    End If
    Next objItem

    [/VBA]

  3. #3
    VBAX Newbie
    Joined
    Jul 2010
    Posts
    5
    Location
    Thanks for the reply.

    Yes, each email sent only once each day.
    The emails may be read or unread, so it is all emails I want to evaluate.
    Also, the email folder is not sub to the inbox, it is at 'Mailbox - Diddly P Squat\Business Management\MI'

    So this code would go into a new module in the Excel workbook?

    How would I then refer to this function to use it in the dashboard?

  4. #4
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Quote Originally Posted by Abermez
    The emails may be read or unread, so it is all emails I want to evaluate.
    So on 23-Jul-10 you want to re-evaluate the emails that were sent 1-Dec-08? Unless you are moving the emails to another folder, you need some way to differentiate the ones that have already been processed.

    So this code would go into a new module in the Excel workbook?
    Yes.

    How would I then refer to this function to use it in the dashboard?
    Technically it's not actually a function, it's a procedure. You can have a button on a sheet, an automatic trigger when you open the workbook, a custom toolbar - take your pick.

    I'm actually heading off on holidays today, so probably will not be able to assist further for a little while. From here:
    • Confirm your logic - EXACTLY what do you want to happen? Think about it as though you were explaining it step-by-step to a child.
    • The code I've provided should give you a good starting point. There are lots of code example on how to refer to Outlook folders, so jump on Google and give it a try. The first test is try to loop through all the emails in your folder, and maybe just display a message box showing the email subject.
    • Try and get as far as you can with the information you have so far. You'll understand it better and retain the information if you try it yourself rather than just have someone write the code for you. Generally people are also much more willing to assist if you demonstrate that you're trying to work it out.
    Go forth and code!

    We are what we repeatedly do. Excellence, therefore, is not an act but a habit.
    Aristotle

  5. #5
    VBAX Newbie
    Joined
    Jul 2010
    Posts
    5
    Location
    Thanks very much for your help so far.

    So on 23-Jul-10 you want to re-evaluate the emails that were sent 1-Dec-08? Unless you are moving the emails to another folder, you need some way to differentiate the ones that have already been processed.
    No, I'm only interested in whether today's emails have been sent, regardless of whether I have read them or not.

    You can have a button on a sheet, an automatic trigger when you open the workbook, a custom toolbar - take your pick.
    OK, great. I'd like it to run automatically when I open the dashboard. How do I do that? I've tried associating the macro but nowhere in your code do you actually call the procedure anything?

    Confirm your logic - EXACTLY what do you want to happen? Think about it as though you were explaining it step-by-step to a child.
    Sorry, I thought I'd been pretty clear.

    I have the dashboard open permanently. It shows me key metrics as well as telling me real-time what tasks have and have not been completed for the day (today). I want it to tell me if today's emails (I just called them A to D for simplicity) have been sent (i.e. received by me) today, regardless of whether I have read them.

    There are lots of code example on how to refer to Outlook folders, so jump on Google and give it a try.
    I have. That's what led me here!

    The first test is try to loop through all the emails in your folder, and maybe just display a message box showing the email subject.
    Erm. How?

    Try and get as far as you can with the information you have so far. You'll understand it better and retain the information if you try it yourself rather than just have someone write the code for you. Generally people are also much more willing to assist if you demonstrate that you're trying to work it out.
    Go forth and code!
    Sorry but I simply don't have the time. That's why I've been looking up all these forums (fora?!)

  6. #6
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    [VBA]
    Dim objNS As NameSpace
    Dim objInbox As MAPIFolder
    Dim objMyFolder As MAPIFolder
    Dim objItem As MailItem


    Set objNS = objOL.GetNamespace("MAPI")

    ' find the correct folder
    Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
    Set objMyFolder = objInbox.Folders("Business Management")
    Set objMyFolder = objMyFolder.Folders("MI")

    For Each objItem In objMyFolder.Items
    With objItem
    ' was the email sent today?
    If Format(.SentOn, "yyyymmdd") = Format(Date, "yyyymmdd") Then
    ' test the subject line
    ' you have not indicated what you want to have happen in Excel, or provided
    ' a sample workbook, so this just displays a message
    Select Case .Subject
    Case "1st Test Subject"
    MsgBox .Subject & "Sent " & Format(.ReceivedTime, "hh:mm")

    Case "2nd Test Subject"
    MsgBox .Subject & "Sent " & Format(.ReceivedTime, "hh:mm")

    Case "3rd Test Subject"
    MsgBox .Subject & "Sent " & Format(.ReceivedTime, "hh:mm")

    Case "4th Test Subject"
    MsgBox .Subject & "Sent " & Format(.ReceivedTime, "hh:mm")
    End Select
    End If
    End With
    Next objItem
    [/VBA]

    The code above will loop through the folder looking for the specific email subjects (obviously you will need to enter the correct subject lines in the code). This is just a slight variation of the code I sent you earlier - did you try this at all? You could have run a test on this code simply by adding a message box.

    In terms of a trigger, you need to specify what you want. There is no point having it start when you open the dashboard if the dashboard is open all day. Otherwise you will have to keep closing and reopening the dashboard file to trigger the macro.

    I suggest you add a button either to the toolbar or directly on a sheet. You can call the procedure whatever you like, and then associate it with the button.

    Sorry but I simply don't have the time. That's why I've been looking up all these forums (fora?!)
    Keep in mind that people who respond to questions are volunteering their time. If you want to get responses you have to demonstrate a willingness to try and work things out for yourself. I (and many others on this forum) charge our clients to perform this kind of work - we're not here to write your code for you for free, but to help with specific issues as you attempt to write your own code.

    We are what we repeatedly do. Excellence, therefore, is not an act but a habit.
    Aristotle

  7. #7
    VBAX Newbie
    Joined
    Jul 2010
    Posts
    5
    Location
    Thanks again. I've copied this into the Excel VBA editor and changed the email subjects to those I'm searching for.

    1. The email subjects each also contain the date. Can I use something like
    "Email A" & TEXT(TODAY,"ddmmyy")
    in the search strings?

    2. I'm still struggling with how to actually call this procedure. I'm happy to assign it to a button, but it doesn't actually appear to have a name, and the VB editor won't even run it in edit mode?

  8. #8
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    1. Yes this will work. However it has to match the subject EXACTLY. In the example you provided, it would be searching for
      "Email A110810".
    2. Just give it a name. Create "Sub Whatever" (it will automatically add "End Sub" as well) and paste the code into the middle.

    We are what we repeatedly do. Excellence, therefore, is not an act but a habit.
    Aristotle

  9. #9
    VBAX Newbie
    Joined
    Jul 2010
    Posts
    5
    Location
    OK. I now have this:

    [vba]Sub Check_emails()
    Dim objNS As NameSpace
    Dim objInbox As MAPIFolder
    Dim objMyFolder As MAPIFolder
    Dim objItem As MailItem


    Set objNS = objOL.GetNamespace("MAPI")

    ' find the correct folder
    Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
    Set objMyFolder = objInbox.Folders("Business Management")
    Set objMyFolder = objMyFolder.Folders("MI")

    For Each objItem In objMyFolder.Items
    With objItem
    ' was the email sent today?
    If Format(.SentOn, "yyyymmdd") = Format(Date, "yyyymmdd") Then
    ' test the subject line
    ' you have not indicated what you want to have happen in Excel, or provided
    ' a sample workbook, so this just displays a message
    Select Case .Subject
    Case "Sign Off MI - " & Text(WORKDAY(TODAY(), -1), "dd/mm/yy")
    MsgBox .Subject & "Sent " & Format(.ReceivedTime, "hh:mm")

    Case "Swindon Fund Pricing Service Credit Tracker as at " & Text(WORKDAY(TODAY(), -1), "dd mm yy")
    MsgBox .Subject & "Sent " & Format(.ReceivedTime, "hh:mm")

    Case "Performance Tracker " & Text(WORKDAY(TODAY(), -1), "dd mm yy")
    MsgBox .Subject & "Sent " & Format(.ReceivedTime, "hh:mm")

    End Select
    End If
    End With
    Next objItem
    End Sub
    [/vba]

    But when I run the macro Check_emails, I get an immediate Compile error: Sub or Function not defined.

  10. #10
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    There is no "Text" function in VBA - use "Format" instead

    We are what we repeatedly do. Excellence, therefore, is not an act but a habit.
    Aristotle

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by geekgirlau
    There is no "Text" function in VBA - use "Format" instead
    Sometimes it is better to use Application.Text rather than Format, you have a richer set of formats.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12

    quick question!

    Quote Originally Posted by geekgirlau
    So on 23-Jul-10 you want to re-evaluate the emails that were sent 1-Dec-08? Unless you are moving the emails to another folder, you need some way to differentiate the ones that have already been processed.


    Yes.


    Technically it's not actually a function, it's a procedure. You can have a button on a sheet, an automatic trigger when you open the workbook, a custom toolbar - take your pick.

    I'm actually heading off on holidays today, so probably will not be able to assist further for a little while. From here:
    • Confirm your logic - EXACTLY what do you want to happen? Think about it as though you were explaining it step-by-step to a child.
    • The code I've provided should give you a good starting point. There are lots of code example on how to refer to Outlook folders, so jump on Google and give it a try. The first test is try to loop through all the emails in your folder, and maybe just display a message box showing the email subject.
    • Try and get as far as you can with the information you have so far. You'll understand it better and retain the information if you try it yourself rather than just have someone write the code for you. Generally people are also much more willing to assist if you demonstrate that you're trying to work it out.
    Go forth and code!


    well first of all your post has been very helpful; and i totally agree on the do it your self thing so my question is what kinda search string am i supposed to use!!!

    what i need to do is count flagged mails but instead of searching for that in specific i want to know how vba can handle outlook mails

    any help is much appreciated.Thanks.

  13. #13
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    VBA allows you to use the Outlook object model, so basically whatever you can do with an email in Outlook, you can do via vba.

    So exactly what did you want to do? Please be specific - for example, you might want to count the number of emails in your Inbox that are flagged for followup today, and includes those flagged prior to today.

    We are what we repeatedly do. Excellence, therefore, is not an act but a habit.
    Aristotle

  14. #14
    Quote Originally Posted by geekgirlau
    VBA allows you to use the Outlook object model, so basically whatever you can do with an email in Outlook, you can do via vba.

    So exactly what did you want to do? Please be specific - for example, you might want to count the number of emails in your Inbox that are flagged for followup today, and includes those flagged prior to today.

    well I kinda want to keep my options open, I'm designing a dash to monitor my teams mailboxes and currently I'm getting the unread count only, but I think I need to give it some more detail...
    thing out of the top of my head now are:
    *follow up flags with start and due date
    *follow up flags clearance date
    * categories (get the colors if possible)
    *subjects

    I don’t ask for much do I ?!

    and I can’t seem to find a decent reference for this, so any help would be appreciated

    Thanks in advance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •