Solo Developer Success

If you’re a fan of David Allen’s Getting Things Done (GTD), then you know the top critical success factor is to have a trusted system for everything.  Over the years, I’ve tried just about every combination of system you can imagine, from paper to pda to computer to online. Nothing ever had all the features necessary to make it my single trusted system. I finally settled on Outlook using the GTD Outlook Add-In from NetCentrics.

One troublesome aspect of my life as a solo developer involves record keeping. I have to be able to easily capture the time I’m spending on client work with enough detail to properly justify an invoice or time sheet. In order to keep everything together, I use Outlook’s Journal feature to create time records for later invoicing or reporting. However, getting this information into a usable report format is not something Outlook does easily.  In order to get the reports I need, I had to create a macro that accepts certain criteria and creates a new Journal item containing the report I need as the body of the item.  I can then copy/paste this information into Invoice detail lines or a status report email.

First, I started with some standard practices for capturing my time.  Every reportable/billable Journal entry gets the subject “Time Log” and the client name is entered in the Company field.  Start date, time and duration is also entered or a timer started along with the detail of the work performed in the Body of the Journal entry.  If the entry is not going to be reported, such as non-billable time, then I leave the duration as zero minutes.

Next, comes the macro.  It’s simply a form that allows me to enter the date range, the subject line and the company name to search for.  There are two buttons, one which produces a time sheet listing only date and total hours worked per day, the other is a log which produces a detail listing showing date, time worked and the text body for each entry all in one report.

Each button calls the same subroutine, CreateTimeReport, with a parameter of “TIMELOG” or “TIMESHEET” depending on the type of output desired.  This subroutine creates a new journal entry and populates the body with the type of report selected.

The TimeSheet report layout looks like this:

Mon 8/10/2009 - 8 hours
Thu 8/13/2009 - 11 hours
Mon 8/24/2009 - 8 hours
Tue 8/25/2009 - 9 hours

Total: 36 hours

And the TimeLog report contains the same information but also the detail of the individual time journal entries.  The journal entry content has been changed to protect my victims, I mean, clients.

Mon 8/10/2009 - 8 hours
Continued debugging of status email update form.
Phone from Charlie, problems getting into Outlook.
Working on sales pipeline overview and infrastructure overview.

Mon 8/10/2009 - 11 hours
Meeting with client. Finished browse modifications.
Began development of Invoice Footer.
Modified Invoice Browse startup direction.

Thu 8/13/2009 - 8 hours
Added new record to live database via Application.
Removed HTML reference for body of Request email.
Also turned off RTF option for email body textbox field.
Compiled and tested. Received wierd .net error when testing.
Tried just a plain result email, worked fine.
Getting an "unhandled win32 exception"

Mon 8/24/2009 - 9 hours
Removed call from SendRequest and tested. Works fine.
Removed passed parameters from SendRequest. Works fine.

Total: 36 hours

The CreateTimeReport subroutine code is as follows:

Private Sub CreateTimeReport(ReportType As String)
' Pass TIMESHEET to print date - hours only.
' Pass TIMELOG to print date - hours and body for transferring to Accounting/Billing software

Dim olAPP As Outlook.Application
Dim jItems As Outlook.Items
Dim jObject As JournalItem
Dim jNote As JournalItem
Dim totalTime As Integer
Dim searchPattern As String
Dim searchSubject As String
Dim jFolderName As String
Dim currentDate As String
Dim dateTotal As Integer
Dim dowName As String
Dim dateString As String

searchPattern = Me.txtCompany.Text
searchSubject = Me.txtSubject

Set olAPP = CreateObject("Outlook.Application")
Set Journal = olAPP.GetNamespace("Mapi").GetDefaultFolder(olFolderJournal)

Set jItems = Journal.Items
jItems.Sort "[Start]", False

Set Notes = olAPP.GetNamespace("Mapi").GetDefaultFolder(olFolderNotes)

Set jNote = olAPP.CreateItem(olJournalItem)
If ReportType = "TIMESHEET" Then
  jNote.Subject = Me.txtCompany.Text & " Time Sheet " & Me.txtStartDate.Text & _
                        " - " & Me.txtEndDate.Text & vbCrLf & vbCrLf
Else
  jNote.Subject = Me.txtCompany.Text & " Billing Log " & Me.txtStartDate.Text & _
                        " - " & Me.txtEndDate.Text & vbCrLf & vbCrLf
End If
jNote.Type = "Document"

totalTime = 0
dateTotal = 0
currentDate = "00/00/0000"

For Each jObject In jItems
  If jObject.Companies = searchPattern And jObject.Subject = searchSubject _
     And jObject.Start >= Me.txtStartDate.Text And jObject.Start <= Me.txtEndDate.Text Then
    If currentDate = "00/00/0000" Then
      currentDate = DateValue(jObject.Start)
    End If

    If (currentDate <> DateValue(jObject.Start) And dateTotal > 0) Or ReportType = "TIMELOG" Then
      If ReportType = "TIMELOG" Then
        dateTotal = jObject.Duration
      End If
      dowName = WeekdayName(Weekday(currentDate, vbUseSystemDayOfWeek), True, vbUseSystemDayOfWeek)
      dateString = dowName + " " + currentDate + " - "
      jNote.Body = jNote.Body + dateString + CStr(dateTotal / 60) + " hours" + vbCrLf
      If ReportType = "TIMELOG" Then
        jNote.Body = jNote.Body + jObject.Body + vbCrLf + vbCrLf
      End If
      currentDate = DateValue(jObject.Start)
      dowName = WeekdayName(Weekday(currentDate, vbUseSystemDayOfWeek), True, vbUseSystemDayOfWeek)
      dateTotal = 0
    End If

    totalTime = totalTime + jObject.Duration
    dateTotal = dateTotal + jObject.Duration
  End If
Next

If dateTotal > 0 And ReportType = "TIMESHEET" Then
  dateString = dowName + " " + currentDate + " - "
  jNote.Body = jNote.Body + dateString + CStr(dateTotal / 60) + " hours" + vbCrLf
End If

jNote.Body = jNote.Body + vbCrLf + "Total: " + CStr(totalTime / 60) + " hours" + vbCrLf
jNote.Close (olSave)

MsgBox ("Total Time: " + CStr(totalTime / 60) + " hours")

Set olAPP = Nothing
Set Journal = Nothing
Set jItems = Nothing
Set jObject = Nothing
Set jNote = Nothing

End Sub

For each record, I accumulate the time if it’s a timesheet report and the date is the same as before or I output it and the body of the entry if it’s a time log report.  The logic may not be as efficient as it could be, so I welcome any suggestions for improvement.