Tracking Outlook emails in Excel dashboard

[Abermez]

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

[geekgirlau]

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)


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

[Abermez]

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?

[geekgirlau]

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!

[Abermez]

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?!)

[geekgirlau]

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


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.

[Abermez]

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?

[geekgirlau]

Yes this will work. However it has to match the subject EXACTLY. In the example you provided, it would be searching for
"Email A110810".

Just give it a name. Create "Sub Whatever" (it will automatically add "End Sub" as well) and paste the code into the middle.

[Abermez]

OK. I now have this:

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


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

[geekgirlau]

There is no "Text" function in VBA - use "Format" instead

[Bob Phillips]

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.

[khashila]

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.

[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.

[khashila]

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.


:dunno 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 ?! :D

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

Thanks in advance