As I type, a copy of Excel 2007 Formulas is making its way across the country from me to John Spanos, the lucky winner. Here are the stats:
Entrants: 186
Qualified Entrants: 123
Entries with something in the body: 44
Duplicate entries: 20
Late entries: 3
Wrong subject: 0
I put the data into swivel.com to make some nice charts. However, Swivel doesn’t let me query or chart the data, it does it for me. I guess I need to put the data in just the right format/structure to get what I want out of Swivel. I was hoping to do some simple queries on the data and display some charts based on those queries. When I first heard about Swivel, I thought it would be similar to YouTube, just more interesting (to me). I want to take data that other people upload, and query it for my own purposes. As far as I can see, there’s no way to do that.
I put the code I used to extract the email into Excel at the bottom of this post. Almost every time I ran this code, it left an OUTLOOK.EXE process running even though Outlook wasn’t running. I think it has something to do with the Outlook feature that allows access for a specified amount of time. I have not been able to get a straight answer on why the process doesn’t terminate properly. I’ve tried every manner of setting object variables to Nothing and quitting Outlook, but I couldn’t get it to consistently terminate the process.
After the code below was run, I had a nice list in Excel. I sorted the list by email address and reviewed it for duplicates. Then I sorted it my name and reviewed for duplicates. I’d like to think I was able to identify all the duplicates, but I’m sure one or two slipped by. Next, I put a random number next to each qualified entry and a zero next to each unqualified entry using this formula:
After filling this formula down, I copied it and pasted special > values so the random numbers wouldn’t keep changing. In the next column, I put a rank formula
John ranked #1 because he had the largest random number.
Warning: Running this code may leave orphaned processes on your computer.
Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim olFldr As Outlook.MAPIFolder
Dim olMi As Outlook.MailItem
Dim rFound As Range
Dim dTime As Date
wshEntrants.Cells.ClearContents
Set olApp = Outlook.Application
Set olNs = olApp.GetNamespace(“MAPI”)
Set olFldr = olNs.GetDefaultFolder(olFolderInbox).Folders(“BlogOther”).Folders(“FormulasBook”)
With wshEntrants.Range(“A1”)
.Value = “EntryID”
.Offset(0, 1).Value = “EmailAddress”
.Offset(0, 2).Value = “Name”
.Offset(0, 3).Value = “ReceivedTime”
.Offset(0, 4).Value = “BodyLength”
.Offset(0, 5).Value = “Subject”
End With
For Each olMi In olFldr.Items
With wshEntrants.Range(“A65000”).End(xlUp).Offset(1, 0)
.Value = olMi.EntryID
.Offset(0, 1).Value = olMi.SenderEmailAddress
.Offset(0, 2).Value = olMi.SenderName
.Offset(0, 3).Value = olMi.ReceivedTime
.Offset(0, 4).Value = Len(StripChars(olMi.Body))
.Offset(0, 5).Value = olMi.Subject
End With
Next olMi
‘olApp.Quit
Set olMi = Nothing
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
End Sub
Function StripChars(sBody As String) As String
Dim sTemp As String
sTemp = sBody
sTemp = Replace(sTemp, Chr$(9), “”)
sTemp = Replace(sTemp, Chr$(10), “”)
sTemp = Replace(sTemp, Chr$(13), “”)
sTemp = Replace(sTemp, Chr$(160), “”)
StripChars = sTemp
End Function
Hi Dick,
Thanks for mentioning Swivel. One of the features we will be rolling out in the future is a query language that allows folks to type-in requests across all the data at Swivel. That will be probably 6 months out, but we’re prototyping it in the lab now. Great blog, by the way, I’m glad I found it.
Regards,
Brian
Brian Mulloy
CEO & Cofounder
http://www.swivel.com
Dick, I am working right now on designing a flow that would let you create a chart your own way. Would you be willing to exchange emails or chat with me on the phone or IM so I can understand what you’d like to be able to do? I’m glad you thought of Swivel for your needs and I’m of course disappointed it didn’t do what you wanted, but I’m pretty confident it can get close with a little bit of your help.
Thank you and hoping to connect with you,
Dmitry Dimov
Product Chief & Cofounder
http://swivel.com
Yeah me! Now I get to brag about being such a big nerd that I won a free Excel Formulas book!
I somehow doubt that anyone will be surprised!
Thanks Dick, I look forward to receiving the book. :)
Dmitry: I’d love to. Ping me at dkusleika@gmail.com and we can go from there.
Dick
COM servers often hang around for a couple of minutes after the last reference is released. Its part of the optimisation to save the reload if you need it again soon after. If you click on an embeded workbook in a Word doc you get the same effect. After a couple of mins Excel.exe closes if you don’t use it. If your Outlook.exe closes after a couple of mins this might expain it, or not.
cheers
Simon
Thanks Simon, I’ll check that out.
Well done John
But commiserations to the other 122 entries beaten by a “Descending Sort Order”
Hui…
The reason Outlook does not terminate is not because of a delay after the last reference being released. I believe it’s more subtle than that.
It’s to do with implicit references. In other words, any object you create or reference should be done so with/by an object variable. For instance, in the following (and the With)
Set olFldr = olNs.GetDefaultFolder(olFolderInbox).Folders(“BlogOther”).Folders(“FormulasBook”)
there are implicit refs created and held which are not released.
You need to ensure no implicit references are created anywhere, by assigning each ref (.) to an object variable.
I tried to find the KB support article that descibes this in detail, but could not find it.
BTW This is the same problem experienced by people automating Excel from Word, Outlook, VB, etc..
ahh, here we go (it’s not the MSDN article; why is MSDN so frigging hard to search?):
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/index.htm
Perfect way of getting and processing the data. Is it possible to collect and process more complex data received by mail? In the contest there were the same data in body of all e-mails, according to the scheme. I often need to get it from many people. I use some Web based software but even for a slight modification I have to ask PHP and SQL men for help. I tried Webform (it was only in Excel 97) but with no success. It crashes in newer vesions, anyway. Some flexible to changes would be nice. Thanks for the blog. I used to be a daily reader.