Exporting Email Info for Panic’s StatusBoard


 

A reader named Marc contacted me with an interesting request. Marc is a fan of the iPad app Status Board. As the name suggests, the app display a dashboard (status board) composed of panels. Each panel displays a different piece of information, such as a clock, the weather, a news feed, etc. One of the app’s features is the ability to create custom panels containing user-defined information. Custom panels read a file and display its contents. Marc saw my post on exporting data to Excel and asked if I could create a knock-off that would export mailbox details to an HTML file instead of to Excel.

Marc’s goal is to export three pieces of information to three different HTML files. He can then create three custom Status Board panels to show those three pieces of information. In the first file, Marc wants the number of unread messages in his inbox. In the second file, Marc needs a table showing some details about each unread message in his inbox. In the third file, Marc would like a list of all the sub-folders he has under his inbox, along with the number of unread messages in each sub-folder.

Exporting the data is simple. What proved to be more complicated was deciding how to trigger the script. For this process to be useful, the script needs to run automatically. Outlook offers several different ways of triggering the script. Methods I considered include

  1. A rule that fires when a message arrives
  2. Trapping the event that fires when mail is delivered to the inbox
  3. Trapping the event that fires when mail is delivered to the mailbox
  4. Trapping the event that fires when a mailbox sync occurs
  5. Using a timer to run the script on some fixed schedule

The first two methods suffer from the problem of firing too often. Consider a scenario where 5 messages are delivered all at once. Instead of running the script once after all the mail was delivered, Outlook would run the script once after each message was delivered for a total of five runs. Triggering on method 3 would only run once, but the event occurs before the messages are actually placed in the inbox. Because the messages haven’t hit the inbox, the unread message count would be wrong. That leaves method 4. Method 4 would only run the script once, but the event does not fire every time mail delivery occurs if the mailbox is on an Exchange server (as Marc’s mailbox is). Instead, the event fires only when a scheduled send/receive occurs. Method 5 solves most of these problems, but suffers from its own set of problems. It requires more code, including calls to the Windows API, which can cause some Outlook stability problems. Also, since methods 4 and 5 run at fixed intervals, the information they report is out of date as soon as it’s reported. New messages could arrive just after they complete. After considering the pros and cons of each approach, I settled on using method 4. Using a built-in Outlook event to fire the code saves adding code to implement a timer and the fact that the sync event fires once at the end of the mail delivery process means that the script doesn’t run too often.

This solution produces three HTML files cleverly named HTML1.htm, HTML2.htm, and HTML3.htm. You can change the output files names and the HTML code each contains as desired. HTML1 contains the number of unread messages in the inbox. HTML2 contains a table listing all the unread messages in the inbox. For each message, the solution reports the time the message arrived, the sender’s name, and the message subject. HTML3 also contains a table, this one listing all the folders under the inbox. For each folder, the solution reports the folder name and the number of unread messages it contains.

Requirements.

This solution should work with Outlook 2007 and later.

Instructions.

The code for this solution comes in two parts.

Follow these instructions to add part 1 of the code to Outlook.

  1. Start Outlook
  2. Press ALT + F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. Right-click on Class Modules, select InsertModule
  5. In the Properties panel click on Name and enter clsStatusBoard
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Edit the code as needed. I included a comment where changes are needed or allowed.
  8. Click the diskette icon on the toolbar to save the changes
Private WithEvents olkSyn As Outlook.SyncObject
Private olkFol As Outlook.MAPIFolder, _
        objFSO As Object, _
        strFol As String

Private Sub Class_Initialize()
    strFol = Environ("USERPROFILE") & "\Documents\"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set olkFol = Outlook.Session.GetDefaultFolder(olFolderInbox)
    Set olkSyn = Outlook.Session.SyncObjects.Item("All Accounts")
End Sub

Private Sub Class_Terminate()
    Set olkSyn = Nothing
    Set olkFol = Nothing
    Set objFSO = Nothing
End Sub

Private Sub WriteHTML1()
    Dim objFil As Object
    Set objFil = objFSO.CreateTextFile(strFol & "HTML1.htm", True)
    objFil.Write olkFol.UnReadItemCount
    objFil.Close
    Set objFil = Nothing
End Sub

Private Sub WriteHTML2()
    Dim objFil As Object, _
        olkLst As Outlook.Items, _
        olkRes As Outlook.Items, _
        olkItm As Object, _
        strBuf As String
    Set olkLst = olkFol.Items
    olkLst.Sort "[ReceivedTime]"
    Set olkRes = olkLst.Restrict("[Unread] = True")
    For Each olkItm In olkRes
        strBuf = strBuf & "<tr><td>" & olkItm.ReceivedTime & "</td><td>" & olkItm.SenderName & "</td><td>" & olkItm.Subject & "</td></tr>"
    Next
    Set objFil = objFSO.CreateTextFile(strFol & "HTML2.htm", True)
    objFil.Write "<table>" & strBuf & "</table>"
    objFil.Close
    Set objFil = Nothing
    Set olkLst = Nothing
    Set olkRes = Nothing
    Set olkItm = Nothing
End Sub

Private Sub WriteHTML3()
    Dim objFil As Object, _
        olkSub As Outlook.MAPIFolder, _
        strBuf As String
    For Each olkSub In olkFol.Folders
        strBuf = strBuf & "<tr><td>" & olkSub.Name & "</td><td>" & olkSub.UnReadItemCount & "</td></tr>"
    Next
    Set objFil = objFSO.CreateTextFile(strFol & "HTML3.htm", True)
    objFil.Write "<table>" & strBuf & "</table>"
    objFil.Close
    Set objFil = Nothing
    Set olkSub = Nothing
End Sub

Private Sub olkSyn_SyncEnd()
    WriteHTML1
    WriteHTML2
    WriteHTML3
End Sub

Public Property Let FilePath(ByVal strValue As String)
    strFol = strValue
    If Right(strFol, 1) <> "\" Then strFol = strFol & "\"
End Property

Follow these instructions to add part 2 of the code to Outlook.

  1. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  2. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  3. Click the diskette icon on the toolbar to save the changes
  4. Close the VB Editor
  5. Click File and select Options
  6. When the Outlook Options dialog appears click Trust Center then click the Trust Center Settings button
  7. Click Macro Settings
  8. Select either of the two bottom settings (i.e. “Notifications for all macros” or “Enable all macros (not recommended; potentially dangerous code can run)”. The choice of which to choose is up to you. If you select “Notifications”, then you’ll be prompted at times to enable macros. If you pick “Enable all” then there’s a chance that a malicious macro could run. It’s a question of how much risk you want to assume.
  9. Click Ok until the dialog-boxes have all closed
  10. Close Outlook
  11. Start Outlook. Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run. Say yes.
Dim objSB As clsStatusBoard

Private Sub Application_Quit()
    Set objSB = Nothing
End Sub

Private Sub Application_Startup()
    Set objSB = New clsStatusBoard
    'On the next line, edit the path to the folder you want the three HTML files written to.
    objSB.FilePath = "c:\users\david\documents\testarea"
End Sub

The final step is to set Outlook send/receive groups to perform a scheduled send/receive at some regular interval. To do that

  1. Switch to the Send/Receive tab
  2. Click the Send/Receive Groups pulldown and select Define Send/Receive Groups
  3. In the Setting for group “All Accounts” section check the box labeled Schedule an automatic send/receive every and set the number of minutes to however frequently you want a send/receive to occur
Advertisements

27 comments on “Exporting Email Info for Panic’s StatusBoard

  1. Pingback: Syncing an Outlook Calendar to the Cloud | TechnicLee

  2. David I would love to try something similar to create an iCal export at timed intervals to the cloud and then use that in Google calendar now that the sync function is not supported in google calendar….say from Outlook 2010??

    • Hi, Todd.

      That’s actually pretty easy to do. I’ll add a new post as soon as I can (hopefully within the next 48 hours) with a solution.

    • Todd,

      A new post will appear at 8:00am (US Eastern Time) this morning with your solution. Please give it a try and let me know if it works for you.

  3. I am looking for your help
    1. I need to copy the below of outlook details into a excel sheet
    a. From field
    b. Subject Field
    c.Received Field
    d. CC Field
    f.Latest part of the email body
    g. Hiperlink all the corresponding email
    h. Generate a log for the attachment and save those attachment with unique ID as per the log.
    2. I can have the opportunity to export the data with in date range

    Requesting support from the exparts.

    • Hi, Sourendra Dey.

      I need additional details on some of the requirements to determine if I can help.

      f. “Latest part of the email body”. What do you consider to be “the latest part”?
      g. “Hiperlink all the corresponding email”. What does “corresponding email” mean in this context?
      h” “Generate a log for the attachment”. Generate it in what, a text file, another spreadsheet, a database?

  4. Hello David,

    Brilliant !!!

    That solved the problem right away (it was Alle accounts) and now all 3 files are created. Now I can start customizing the HTML files to suit the app better, although that’ll be something for next week 🙂

    I’ll post my revised HTML files to suit the app better here next week so that if anyone else uses the Status Board app they can use it as well.

    Many many thanks again for all your help in this !!!

    • Marc,

      Cool!

      You’re welcome.

      If you don’t mind sharing, I’d love to see a screenshot of your status board once it’s done. If you don’t want to post a screenshot where anyone can see it, then you can email it to me.

    • Hmmm… it seems that it’s going to take some more time for me to have this working perfectly. As I was changing the look and feel of the first panel, I noticed that it doesn’t automatically refresh itself, even though the HTML file was updated and automatically uploaded to dropbox.

      When I triple-tapped the section it reloaded.

      When I checked the tutorial again I noticed this text:
      http://www.panic.com/statusboard/docs/diy_tutorial.pdf

      If your webpage shows data that updates frequently (such as a bus tracker) you should update the data yourself. The app will not reload your web page automatically. We recommend using XMLHttpRequest to fetch and replace content dynamically. Do not use meta-refresh; Status Board’s CSS will not be injected into a page that is refreshed this way. If you need to manually reload the web page during development you can triple-tap the panel to reload it.

      So now I first have to figure out how to use XMLHttpRequest in order to have my content reloaded dynamically before I will continue with the finetuning of the look and feel.

      Since this is something I can only do when I’m at work and I only work on it in between tasks, this may take some time as I have no idea where to start 🙂

    • Also, I wil need to figure out how to have the 2nd HTML file (inbox details) changed so that it sets the newest mails at the top. Now whenever a new mail is added it adds it to the bottom of the list instead of to the top.

    • Marc,

      Change this line

      olkLst.Sort "[ReceivedTime]"
      

      to

      olkLst.Sort "[ReceivedTime]", True
      
    • Hello David,

      The reverse sorting of the inbox details (2nd html) file now works perfectly, many thanks.

      As for the auto refresh option, I’m affraid my lack of proper HTML / Java programming knowledge is holding me back on getting this to work.

      Neither the link you provided, nor the link the developer provided me ( https://t.co/redirect?url=https%3A%2F%2Ft.co%2FqejW7rM8vV&t=1&sig=631afd0a898fccc8eeef9925624c164d5475786a&iid=b2e89d40bed64df7b870103cc095ff0e&uid=2237403864&nid=27+1268 ) or his suggestion “It’s a little fussy and convoluted for small projects; use jQuery for a shortcut. —B” provided me with an answer which I could translate into changing the VBA code in Outlook to get this to work.

      The HTML file which is created from Outlook thanks to your code is placed in a folder on my hard drive at work, which is then synced to my Dropbox account automatically each time it is changed. That file within dropbox is shared via a secure and unique url to my Status Board app, which subsequently is displayed in the app.

      My problem is that all sollutions I see at those pages, doesn’t seem to work within the Outook script editor as as soon as I add anything, the line turns red upon saving. I know I’m doing something wrong and it’s probably really simple, I just don’t see it.

      MANY thanks for any advice you may have !!

    • Hi, Marc.

      You’re welcome.

      There’s nothing you can add to the VBA (i.e. nothing VBA-wise) that’s going to solve the problem. I’m not a Javascript expert, but my guess is that the process will work something like this

      1. The custom panel will load a static page (i.e. a page that does not change) containing Javascript. The Javascript will use the XMLHttpRequest object to get data and update the page.
      2. The XMLHttpRequest will read the data from another file.
      3. The static page will contain Javascript code that forces the page to refresh at some set interval.
      4. The script I wrote will write the data file the XMLHttpRequest reads in step #2.

      With those pieces in place, the script I wrote will write the data to a file. Periodically, the Javascript in the static page you’ll create will read that data from the file and update the panel. The problem I see in this solution is that an XMLHttpRequest normally makes a call to a web service that returns data. I don’t know that it’s capable of reading a file directly. If it’s not able to do that, then I don’t know how this will work unless you have a web server and it’s running a service that can serve the data up.

      It strikes me that another possible solution is to add HTML comands to the page my script writes out that forces it to refresh itself at some interval. When the page refreshes it should pick up any changes my script has made to the file. That would seem to be the simplest solution.

      Let’s test that theory. I added some HTML commands to this code that should force it to refresh itself once per minute. Please replace the WriteHTML1 subroutine you have now, I think you renamed it to something else, with the one below. Start the script and let it run for awhile. Pay special attention to whether the unread message count is changing at all.

      Private Sub WriteHTML1()
          Dim objFil As Object
          Set objFil = objFSO.CreateTextFile(strFol & "HTML1.htm", True)
          objFil.Write "<html><head><meta http-equiv=""pragma"" content=""no-cache""><meta http-equiv=""expires"" content=""-1""><meta http-equiv=""refresh"" content=""60""></head><body>" & olkFol.UnReadItemCount & "</body></html>"
          objFil.Close
          Set objFil = Nothing
      End Sub
      
    • Hello David,

      It does seem to do something as after a minute it changes to a 404 error message (regardless if anything has changed in the actual ammount of unread messages).

      After then manually reloading it again it is correctly displayed again, however after a minute it reverts to the 404 error message again.

      Do I need to “hardcode” the url or something perhaps?

    • Hi, Marc.

      I don’t know why it would give a 404 error. A 404 is a page not found. It’s reloading itself, so I don’t know how it could fail to find the page. I’m hampered by the fact that I don’t use Status Board myself. I’ve looked at some of the links you’ve provided, but haven’t read them in-depth. It might be that’s it’s an issue with hosting the files in Box. I don’t use Box either, and don’t know how it handles serving up a file to Status Board. I tested the concept of reloading the page from a web server, and it worked fine.

      As to hard-coding the URL, there’s nothing to hard code. I added three directives to the HTML file the code creates. The first two of these

      tell the browser to not cache the page. This insures that when the browser reloads the page it reads it from the server and not from the cache. The third directive

      tells the browser to reload the page every 60 seconds. It’s reloading itself, so there’s no URL to hard-code. My best guess is that Box either doesn’t serve the file to the app like a web server would or that the app can’t handle the refresh for some reason. It’s probably the former, but I’ll need to go back and read more about how the app handles custom panels to know that for sure.

    • Marc,

      I was just reading through the PDF you linked to, the one describing how to implement custom panels, and noticed that it specifically states that the method I tried to use here won’t work. That explains why it’s failing. I’ll see about the XMLHttpRequest approach.

    • Many, MANY thanks for all your effort and time with this. I bought the Status Board app several months ago, but after discovering the limitations I haven’t really used it anymore. I thought it was a waste of $10 for me, unitll I found your site. I can honestly say that without you and your tireless efforts, the entire Status Board app for me would still be almost useless as the integration with my work calendar and mail was one of the main reasons for me for buying it in the first place.

      So again, Thank you !!!

    • You’re welcome, Marc!

      I think I have a solution that uses an XMLHttpRequest object to fetch the data at a set interval. I need to do a little more testing to make sure. If it works, then I’ll modify the code and you can give it a try.

    • Marc,

      After some tinkering, here’s the solution I propose.

      1. Delete the code you currently have in Outlook from part 1 of the original post (i.e. delete the code in the class module, but don’t delete the class module itself).
      2. Copy the code you see in part 1 of the original post (I’ve modified it) and paste it into Outlook (i.e. paste the modified code into the class module).
      3. Open Notepad.
      4. Copy the code below and paste it into Notepad.
      5. Edit the file per the comments I included in the file (i.e. the lines that begin with //). You’ll need to set the refresh rate and the URL of the file the code will read from Dropbox.
      6. Save the file with a name like Module1.html.
      7. Repeat steps 3-6 two more times. When you’re done you’ll have three files (e.g. Module1.html, Module2.html, Module3.html). Each of these files will point to a different Dropbox URL: the URL of the data to be loaded into that module.
      8. Edit you StatusBoard settings so the first custom panel loads Module1.html, the second loads Module2.html, and so on.
      9. Close and restart Outlook.
      10. Open StatusBoard and let it run for awhile. If the code works properly, then the panels should update at whatever interval you set.

      The Javascript below use an XMLHttpRequest object to load data into the page at whatever interval you set. I tested this solution and it worked perfectly from both Firefox and Internet Explorer. Of course I don’t have a StatusBoard account, so I could not test this solution there. “Worked” in this context means that the solution updated the page with the contents of the file the XMLHttpRequest object reads at whatever interval I set.

      The Javascript you see here is a a slightly modified version of code I found on the internet. It is not my code (i.e. I didn’t create it). You can find the original code here on Pastebin and again on StackOverflow.

      <!DOCTYPE html>
      <html>
          <body>
              <p id="demo"></p>
              <script>
                  // On the next line, edit the number number of seconds between refreshes.  The default value below is 5 minutes (300 seconds).
                  var iInterval = 300;
                  var myVar=setInterval(function(){getParameters()},iInterval*1000);
                  
                  function getParameters() {
                    // On the next line, edit the path to the file the script will read each time it refreshes
                    var sURL = "http://dl.dropbox.com/Marc1.htm";
                    var oRequest = new XMLHttpRequest();
                    oRequest.open("GET",sURL,false);
                    oRequest.onreadystatechange = function (oEvent) {
                      if (oRequest.readyState === 4) {
                        if (oRequest.status === 200) {
                          console.Log (oRequest.responseText);
                        } else {
                          console.log("Error", oRequest.statusText);
                        }
                      }
                    };
                    oRequest.setRequestHeader("User-Agent",navigator.userAgent);
                    try {
                      oRequest.send(null);
                    } catch (err) {
                      alert(err);
                    }
                  
                    if (oRequest.status==200) document.getElementById("demo").innerHTML = oRequest.responseText;
                    else alert("Error executing XMLHttpRequest call!");
                  }
              </script>
          </body>
      </html>
      
    • Hello David,

      First off sorry for the delay in answering. I’ve had a week holiday off from work so I coudln’t test your sollution earlier.

      Here is the original first HTML file which is generated from Outlook:
      https://www.dropbox.com/s/jq4lubvj0g9rlm2/TotalUnread.htm

      As per your instructions I’ve copied the link above (which I previously added in Status Board and would show me the contents of the htm file) to the newly created one:
      https://www.dropbox.com/s/f255so4vj3x8x3m/TotalUnreadAutoSync.htm

      However I tried your sollution, but what the status board app now shows me when I add the TotalUnreadAutoSync.htm file is a page which will allow me to download the htm file itself. Similair to the page your shown when you open either of the above posted links.

      When I add the TotalUnread.htm file it shows me the number of unread messages as usual.

      I have no idea why the behaviour is different. Any thoughts perhaps?

    • Hi, Marc.

      No worries. I hope you had a good holiday.

      The first file, TotalUnread.htm, is wrong. Did you do step #1 from my instructions? If so, then the .htm file should look different.

    • Hello David,

      I’m sorry to say that I won’t be able to fully look into this until next month as after today Ive got another 3 week holiday ahead of me (I know, life is hard). I haven’t had any time this week to play around with it to be honest so I won’t be able to get back to you on this untill at least the 2nd week of July.

      I will let you know how everything worked out though and many thanks again for all the help sofar !!!

  5. Hello David,

    First of INSANELY thanks for putting all this effort into my request !!! It is simply awesome!

    I followed your detailed instructions to the letter, however for some reason no files are begin created. I haven’t even started with actually changing anything in the code so that can’t be the issue. I’ve made a screenshow of the code I added:

    I changed the macro settings as instructed so now I do see a pop-up when I start outlook. I also changed the send/receive groups setting. I cannot really see where I went wrong here. I’m using Outlook 2010 on a Windows 7 machine.

    Many thanks in advance for your response

    • Good morning, Marc.

      You’re welcome!

      My first thought is that the problem may be language related. Please check your send/receive groups to see if you have a group called “All Accounts” (in English, not Dutch). I’m thinking that on your computer that group may be called something else. If it is, then on line 10 of the code in the first part please change “All Accounts” to the correct send/receive group name.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s