Analyzing my Lotus Notes sent mail since January 2011
| analysis, ibm, lotusToday is my penultimate day at IBM! Having successfully turned my projects over to another developer (hooray for the habit of organizing project-related files in Lotus Connections Activities), I’ve been focusing on getting things ready for the traditional goodbye e-mail, which I plan to send tomorrow.
I dug around in the Lotus Connections Profiles API to see if I could get a list of my contacts’ e-mail addresses. I fixed a small bug in the feed exporter of the Community Toolkit (w4.ibm.com/community for people in the IBM intranet) and exported my contacts, giving me a list of 530 IBMers who had accepted or sent me an invitation to connect.
Not everyone participates in that Web 2.0 network, though, so I wanted to analyze my sent mail to identify other people to whom I should send a note. I couldn’t find a neat LotusScript to do the job, and I couldn’t get the NSF to EML or mbox converter to work. Because I didn’t need all the information, just the recipients, subjects, and times, I wrote my own script (included at the end of this blog post).
I used the script to summarize the messages in my sent mail folder, and crunched the numbers using PivotTables in Microsoft Excel. I worked with monthly batches so that it was easier to find and fix errors. I decided to analyze all the mail going back to the beginning of last year in order to identify the people I mailed the most frequently, and to come up with some easy statistics as well.
Spiky around project starts/ends, I’d guess.
I wanted to see which roles I tended to e-mail often, so I categorized each recipient with their role. I distinguished between people I’d worked with directly on projects (coworkers) and people who worked with IBM but with whom I didn’t work on a project (colleagues). The numbers below count individual recipients.
Role |
Number of people |
Number of individual |
Average e-mails sent |
colleague | 407 | 827 | 2.0 |
coworker | 50 | 562 | 11.2 |
client | 21 | 387 | 18.4 |
manager | 4 | 109 | 27.3 |
partner | 9 | 51 | 5.7 |
system | 9 | 21 | 2.3 |
other | 8 | 11 | 1.4 |
self | 1 | 5 | 5.0 |
Grand Total | 509 | 1973 | 3.9 |
As it turns out, I sent a lot of mail to a lot of people throughout IBM, mostly in response to questions about Lotus Connections, Idea Labs, or collaboration tools.
Now I can sort my summarized data to see whom I e-mailed the most often, and add more names to my don’t-forget-to-say-goodbye list. If all goes well, I might even be able to use that mail merge script. =)
The following agent processes selected messages and creates a table with one row per recipient, e-mailing the results to the specified mail address. It seems to choke on calendar entries and other weird documents, but if you go through your sent mail box in batches (Search This View by date is handy), then you should be able to find and delete the offending entries.
Option Public Dim TempNitem As NotesItem Dim TempNm As NotesName Dim session As NotesSession Dim db As NotesDatabase Sub Initialize mailAddress = "YOUR_ADDRESS@HERE" Dim ws As New NotesUIWorkspace Dim uidoc As NotesUIDocument Dim partno As String Dim db As NotesDatabase Dim view As NotesView Dim doc As NotesDocument Dim collection As NotesDocumentCollection Dim memo As NotesDocument Dim body As NotesRichTextItem Dim range As NotesRichTextRange Dim count As Integer Set session = New NotesSession Set db = session.CurrentDatabase Set collection = db.UnprocessedDocuments Dim FldTitles(3) As String FldTitles(0) = "E-mail" FldTitles(1) = "Subject" FldTitles(2) = "Date sent" Set maildoc = db.CreateDocument maildoc.Form = "Memo" maildoc.Subject = "Summary" maildoc.SendTo = mailAddress Dim ritem As NotesRichTextItem Set ritem=New NotesRichTextItem(maildoc,"body") ' passing the rich text item & other relevant details Set ritem = CreateTable(FldTitles, collection, ritem, "Sent items", "Summary created on " + Format(Now, "YYYY-MM-DD")) maildoc.send(False) End Sub Function CreateTable(FldTitles As Variant, doccoll As NotesDocumentCollection, rtitem As NotesRichTextItem,msgTitle As String,msgBody As String ) As NotesRichTextItem 'http://searchdomino.techtarget.com/tip/0,289483,sid4_gci1254682_mem1,00.html 'Takes Documentcollection & creates tabular information on to the passed rtitem (rich text item) Set ritem=rtitem Set rtnav = ritem.CreateNavigator Set rstyle=session.CreateRichTextStyle '=================================================== 'heading in the body section of the mail rstyle.Bold=True rstyle.NotesColor=COLOR_RED rstyle.Underline=True rstyle.NotesFont=FONT_COURIER rstyle.FontSize=12 Call ritem.AppendStyle(rstyle) ritem.AppendText(msgTitle) rstyle.Underline=False rstyle.NotesColor=COLOR_BLACK ritem.AddNewline(2) rstyle.FontSize=10 rstyle.Bold=False rstyle.NotesColor=COLOR_BLACK Call ritem.AppendStyle(rstyle) ritem.AppendText(msgBody) ritem.AddNewline(1) '=================================================== rows=doccoll.Count +1 cols=CInt(UBound(FldTitles)) Call ritem.AppendTable(1, cols) Dim rtt As NotesRichTextTable Call rtnav.FindFirstElement(RTELEM_TYPE_TABLE) Set rtt = rtNav.GetElement '================================================= 'heading of the table rstyle.Bold=True rstyle.NotesColor=COLOR_BLUE rstyle.FontSize=10 Call ritem.AppendStyle(rstyle) For i=0 To UBound(FldTitles) - 1 Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) Call ritem.BeginInsert(rtnav) Call ritem.AppendText(FldTitles(i)) Call ritem.EndInsert Next '================================================= rstyle.FontSize=10 rstyle.Bold=False rstyle.NotesColor=COLOR_BLACK Call ritem.AppendStyle(rstyle) Dim count As Integer count = 0 Set doc=doccoll.GetFirstDocument While Not (doc Is Nothing) subject = doc.GetFirstItem("Subject").values(0) posted = doc.GetFirstItem("PostedDate").values(0) Set sendTo = doc.getFirstItem("SendTo") For i = 0 To UBound(sendTo.values) Call rtt.AddRow(1) Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) Call ritem.BeginInsert(rtnav) ritem.appendText(sendTo.values(i)) Call ritem.EndInsert Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) Call ritem.BeginInsert(rtnav) ritem.appendText(subject) Call ritem.EndInsert Call rtnav.FindNextElement(RTELEM_TYPE_TABLECELL) Call ritem.BeginInsert(rtnav) ritem.appendText(posted) Call ritem.EndInsert Next count = count + 1 Set doc=doccoll.GetNextDocument(doc) Wend Set CreateTable=ritem MsgBox "E-mails summarized: " & count End Function
I find it helpful to save it as the "Summarize Recipients" agent and assign it to a toolbar button that runs @Command([RunAgent]; "Summarize Recipients").