Sending an Email from VBA

The Excel ToDo list has a keyboard shortcut that sends an email containing all the “red” todo items (i.e. those that are due today or before today). I use this to send my open items from “home” to my email address at my current customer site. That way, if I have to remember my todos for a given day at work, I always have them available.

The code that sets up the email body is probably not very interesting, here is just a short excerpt that builds the email body in the variable text by concatening the titles of the open items that are due until today

Do While Cells(i, ncNr) <> ""
 If Cells(i, ncStatus) = ToDo.scStatusOpen _
  And Cells(i, ncWhen) <= Date Then
      text = text _
          & "(" & Cells(i, ncNr) & ")" & vbTab & Cells(i, ncText) _
          & vbCrLf
 End If
 i = i + 1
Loop

(For the full code, check out the Worksheet sub SendToDoMail.)

After the body is built, it is then sent to the sub DoSendMail in the Mail module. (This call is commented out in the version that you can download. In order to enable this functionality, you’ll have to do add some configuration changes in the code.) Here’s the code that is delivered:

Sub DoSendMail(pmailBody As String)

Dim objConfig As CDO.Configuration
Dim objMessage As CDO.Message

' Set up the configuration
Set objConfig = New CDO.Configuration
objConfig.fields.Item(cdoSendUsingMethod) = cdoSendUsingPort
objConfig.fields.Item(cdoSMTPServer) = "<SMTPServer>"
objConfig.fields.Item(cdoSMTPServerPort) = 25
objConfig.fields.Item(cdoSMTPConnectionTimeout) = 10
objConfig.fields.Item(cdoSMTPAuthenticate) = cdoBasic
objConfig.fields.Item(cdoSendUserName) = "<username>"
objConfig.fields.Item(cdoSendPassword) = "<password>"
objConfig.fields.Update

' Build and send the message
Set objMessage = New CDO.Message
Set objMessage.Configuration = objConfig
With objMessage
  .To = "<EMail to send to>"
  .From = "<EMail sender>"
  .Subject = "ToDos for Today"
  .TextBody = pmailBody
  .Send
End With

'Tear Down
Set objMessage = Nothing
Set objConfig = Nothing

End Sub

This code instantiates a few objects from Microsoft's Collaborative Data Object (CDO). The CDO API allows sending e-mail using SMTP. You have to include a reference to the Microsoft CDO Library. (The full title is "Microsoft CDO for Windows 2000 library", but it worked fine on my XP and Vista machines.) The best reference I could find on CDO is by John Peterson and can be found here.

First, the configuration for the SMTP-server that you want to use has to be set up (lines 7 to 15). In order for this code to work properly, you have to enter the information specific to your SMTP-server:

• “Name” of the server (check with your email provider)
• UserName of your account at the email provider
• Password of your account

Once you’ve got the proper configuration, you can then build the message (lines 20 to 24). Interestingly, you can make this look as if the mail comes from any EMail-address (probably not even a valid one). After you’ve built the complete message, you can then send the email (line 25) to another EMail account (like my “work” address). Of course, this code does not do any error handling in case the server is not available (e.g. by buffering the message somewhere and then trying to resend at a later time). However, this has not been necessary for me (as we’re having a DSL line at home and are thus always connected to the Internet).

AutoFiltering with a Macro and a Keyboard Shortcut

In order to make the filtering easier to use, there is a keyboard shortcut Ctrl-Shift-A that calls a macro:

Here’s the code of the macro AutofilterUpdate that is called:

Sub AutofilterUpdate()
 If Selection.Cells.Count = 1 Then
     If Selection.Value <> "" Then
         Selection.AutoFilter
         Selection.AutoFilter
     End If
 End If
 Selection.AutoFilter Field:=ncRelevance, Criteria1:=scRelevanceCurrent
End Sub

First (lines 2 & 3), this code checks if the selection is within the list of items and only one cell is selected. (Otherwise, the call to Selection.AutoFilter will cause a strange runtime error.) This requires two ifs because I could not find a way to “short-circuit” the evaluation.

Then there are two calls to Selection.AutoFilter (lines 4 & 5). This extends the reach of the autofilter to newly added items in the list.

Finally, in line 8 the filter is set to only select those items that have a relevance (as calculated with the User Defined Function) of “current”. This simple code takes care of “removing” old items from your view without deleting them. Next: Sending an EMail.

Struggling with Access

Here’s one of the issues I’m currently working on. At work, we’ve “inherited” a non-trivial access application – maybe 20 tables, a bunch of forms, queries and reports. For now, we’re struck with the architecture, but we still have to add a bunch of functionality (new/changed tables and forms).

Getting accustomed to developing in Access was pretty quick – if you have a relatively good understanding of databases and application development, you can get some functionality working pretty quick. Some “interesting” issues, but nothing major so far.

The thing that has been bugging me (as I’m the one stuck with the development) is the “development cycle”. When you’re used to Enterprise Development in environments like SAP, this is a big shock. Development, QA and production environment? Forget about it, it’s three separate copies of the Access file. Moving functionality “up the chain”? Replace the production file with the qa file. Add a new table and a new form – no (simple) way to “move” just that part of the functionality. Push new functionality while people are using the application? Wait till everyone’s gone home … quite frustrating. And don’t get me started on multi-user handling, groups, rights …

We’ve tried to make things a bit more workable, but we’re still struggling:

• Changing data and developing the Access file at the same time Without any tricks, when you want to work on the application you need exclusive access to the file, so that no one else can change data in the application’s database. Of course, that is not tolerable if you have more than a handful of users. One workaround is to “split” the Access file into a database or backend file (which contains just the tables and the data) and application file (which contains the queries and forms). The application file has “links” to the tables in the backend file. This seems to make working on code and data in parallel possible, it’s still a bit tricky to add new tables or change existing ones, but that’s okay for now.
• Performance Especially after splitting, the performance has gone South – it is now on the verge of being unusable. All our files have to be on the network (which is not very fast, but shouldn’t be much of a problem as the file is just a few MBs). Also the variations of performance are very strange – and it has been degrading over the last days without any reasonable explanation.
• Usage Reports Just one example: A form relies on a query for its data. You can change the name of the query, but the form does not pick up this change. So the next time it is called, you just get an error. We’ve got a couple of “Query 1” or “Macro 1” or “Makro 1” etc., but are afraid to delete or rename them because we don’t know where they are used. Any “compilers” for Access code that would show us these errors or help us in renaming?
• I’ll be adding more over the next few days …

Maybe some of you have some ideas on what we could do to improve the user and developer “experience”? Let me know in the comments …

Calculating Relevance with a User Defined Function

Here’s the code to properly calculate the relevance of a todo list item:

Function Relevance(ByRef pStatus As String, ByRef pdLastUpdate As Date, ByRef pdDue As Date) As String
'calculates the relevance of an item based on the parameters
'possible results:
' - old (done and hasn't been touched for x days - constant ncUnchangedForDays)
' - future (open and due after the next y days - constant ncDueInDays
'           or someday)
' - current (open and due in the next y days - constant ncDueInDays
'           or closed with the last x days - constant ncUnchangedForDays)
Const ncDueInDays = 7
Const ncUnchangedForDays = 3

Select Case pStatus
   Case ToDo.scStatusClosed
       If pdLastUpdate < Now() - ncUnchangedForDays Then
           Relevance = scRelevanceOld
       Else
           Relevance = scRelevanceCurrent
       End If
   Case ToDo.scStatusSomeday
       Relevance = scRelevanceFuture
   Case Else 'at least "open"
       If pdDue < Now() + ncDueInDays Then
           Relevance = scRelevanceCurrent
       Else
           Relevance = scRelevanceFuture
       End If
End Select
End Function

This started as a simple Excel “If” formula, but that quickly got too complicated and very tricky to understand, so I switched to a UDF (user defined function). In order for the UDF to be available, it has to be defined in a module. Then you have the regular function wizard available:

It’s important to properly name the parameters, I have not yet found out a way to provide more detailed help to the wizard or to properly format the parameter values.

The code used the following constants to define status and relevance values:

Public Const scStatusOpen = "open"
Public Const scStatusClosed = "done"
Public Const scStatusWaiting = "waiting"
Public Const scStatusSomeday = "someday"

Public Const scRelevanceCurrent = "current"
Public Const scRelevanceOld = "old"
Public Const scRelevanceFuture = "future"

This makes it easier to change the language of the sheet (like I had to do for this blog post), and it also makes it easier to think about what needs to be done if you want to add some more detailed status or relevance values. Next: Updating the AutoFilter.

Automatically updating Changed and setting up a new line

In order to make things easier, a couple of things are triggered when you work on a todo list item. The following code snippet achieves two things:

• When you edit an existing todo item, it automatically updates the “lastChanged” column.
• When you are working on a new line to create a new todo item, it sets up the line with the proper formatting and formulas.

Const ncNr = 1
Const ncText = 2
Const ncNew = 4
Const ncWhen = 5
Const ncStatus = 6
Const ncChanged = 7
Const ncStatus = 8
Const ncPrio = 9
Const ncComment = 10

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If (Target.Cells.Count = 1) And _
    (Target.Column <> ncChanged) And _
    (Target.Row <> 1) Then
    Cells(Target.Row, ncChanged) = Now
End If

If (Target.Cells.Count = 1) And _
    (Target.Column <> ncNew) And _
    (Target.Row <> 1) And _
    Cells(Target.Row, ncNew) = "" Then
        Cells(Target.Row, ncNew) = Now
        Cells(Target.Row, ncStatus) = ToDo.scStatusOpen
        'copy formulas to id and status columns
        Cells(Target.Row - 1, ncNr).Copy Destination:=Cells(Target.Row, ncNr)
        Cells(Target.Row - 1, ncRelevance).Copy Destination:=Cells(Target.Row, ncRelevance)
End If

End Sub

Here’s a brief explanation of this code:

• Constants (lines 1 to 9) In order to reference the columns in the code, I’ve created constants so that when the layout of the sheet changes (adding/deleting a column), all is needed is a change to the constant.
• Sub Workbook_SheetChange (line 11) In order for the code to be automatically called, you have to implement the sub Workbook_SheetChange. Whenever a cell in the workbook is changed, Excel calls this sub with two parameters denoting the sheet that was changed and the particular cells that were changed. (You can change more than one cell at a time, e.g. when selecting a range of cells and changing the cell’s background color.)
• Updating the Changed-Column (lines 13-17) The if-statement checks that only one cell is selected (line 13), it’s not the changed-column (line 14 – this would trigger an endless loop), and we’re not in the first row (line 15). Typically, this means that the user has changed something in an existing todo item, for example the title, the due-date, the status or the comment. If these conditions apply, then the changed-column is updated to the current date/time (line 16).
• Setting up a new line (lines 19-28) The if-statement performs similar checks as above, but also checks if the new column is still empty (i.e. that we have a new todo item). Then the new line is set up accordingly. The new column is set to Now (line 23) and the status is set to open (line 24). Then we have to set up the formulas in the id and relevance columns. The way this is done is by copying the cells from the line above. If these are set up correctly (e.g. id as a formula adding one to the value above), then the formulas work in the new row as well.

This way, you can just focus on working with the todo items without having to think about how to make the filtering work.

Next:Calculating the relevance of an item.

Coding Overview

There are three main parts of the code to make the ToDo List work:

1. Workbook Code This is code that is directly related to the Workbook – mainly one sub Workbook_SheetChange that is triggered by Excel when a user changes something. This also contains a couple of constants referring to the column numbers and the code to build the text to be sent by mail.
2. Module ToDo This module contains the code changing todo list items like calculating relevance, filtering and updating. It also contains constants for the status and relevance values. It changes some contents of the sheet, so it’s not completely decoupled from the sheet, but if required, the required changes are pretty minimal. This could be code in the workbook, but Excel insists that some types of code (like user defined functions) have to be in a module.
3. Module Mail This is a pretty much self contained module to send an email over SMTP. Most code is a number of constant definitions to properly call the mail sending.

The code in each of these parts is described in the following posts:

• Automatically updating a changed item
• Calculating the relevance
• Updating the AutoFilter
• Sending an EMail

Next: Automatically updating a changed item.

Using the Excel Sheet

Once you’ve downloaded the todo sheet, you can then start playing around with it. Here’s a couple of pointers:

Working on an existing item

If you have an existing item, you can enter some information to report what you have been working on. The “update column” (Column G) will be automatically updated with the current timestamp to show when the last changes were made.

Something I like to do at the start of the day is go through my open items and decide if I’m going to try to do them today or at a later date. If I change the “due column” to today, the status column will get a red background. If the item is open but due at a future date, the background will be yellow. Once you complete an item and change its status to “done”, the background color will be removed (i.e. “white”).

Adding a new item

If you have something new to do and want to add a new item, just start typing in the first empty line. Once you leave the cell, the rest of the line will be properly set up by the code. For example, the id and status columns will be set up, the new column will be set to the current date and the status (with its conditional formatting) will be set to “open”. You can just enter the information you want to capture.

Filtering

Once you’ve completed the items for a while (the default is three days, but you can change that in the code), there is no longer a need to show this item. The sheet uses Excel’s Autofilter to display just the items that are still relevant. For the keyboard users, there is a keyboard shortcut Ctrl-Shift-A (for “Autofilter”) to automatically update the autofilter and only display the relevant items (i.e. those with a value in the Relevance column of “current”). Another feature is that items that I plan to do in the further future (next month, next year, …) get a relevance of “future” and aren’t displayed until the due date comes nearer.

Send Mail

The sheet is just an Excel sheet, so there is not much “web-type” functionality. As I’m mainly working at home (with my home todos) or at work (with the work todos), this has not been much of a problem for me. If I need my home todos at work, I can send all items due today in an email by hitting the keyboard shortcut Ctrl-Shift-M (for “Mail”).

Even though there is not much functionality in the sheet, I have found this sheet to be very useful in keeping tracks of what needs to be done. I have added some little things over time, but the code has been very stable in the last years. I hope you’ll like the sheet as well .. let me know if you have ideas for further improvements.

Next: Coding Overview.

First look at the Excel ToDo Sheet

This is the (almost) empty Excel sheet you can download:

Here's a rough overview of the sheet:

• column A : ID a unique identifier for each entry
• column B: What a short title of the todo item
• column C: Tag a simple way of grouping related todo items (e.g. project A/B/C, phone, email, private) - for simple use cases this column can be hidden
• column D: New timestamp the item was created
• column E: Due day this item should be done or worked on
• column F: Status status of this item, mainly open or done. This column has a Conditional Formatting, so you can easily see today's items (red) or those that are still open (yellow).
• column G: Update timestamp this item was last updated. This column is automatically updated by the sheet, see another post.
• column H: Relevance indicates if this item is still relevant and should be displayed when filtered
• column I: Priority priority of the todo item - usually hidden because I don't work with priorities (similar to GTD). This is a feature I thought I would need at the start, but which turned out were not very useful and just ended up using real estate space.
• column J: Comments just a big column to let you enter any comments to the item, like stuff you've done, who you're waiting for etc.

Column H (Relevance) can have the following values:

• Old The item has been closed for more than a few days
• Future The item is open but with a due date more than a couple of days in the future
• Current All other items. This includes some closed items as well, which is great to look back on the last week and see the items you've been working on.

Based on relevance we can then use a filter to just display the items that are current. So there is no need to delete old, completed items - rather you can always go back to stuff you've done in the past and get back to your notes.

Next: Using the Excel Sheet.

Excel ToDo (with VBA)

For the longest time I have been using my own ToDo List. I started with a simple Excel table and added features over time. You can find an Excel 2003 version here. A lot of it depends on some relatively simple VBA macros, but it's gotten quite powerful over time.

Here are some basic thoughts behind what I have now:

• Excel is almost universally installed on any machine. As I am working as a consultant and am usually on-site at a customer and have to use the machine I was provided with, a custom program was not an option.
• There may be some good online choices by now, but the beginnings of my sheet are much older than prevalent internet access, and using the internet is tricky at most customers. For example, some have strange browser versions, blocked sites, inability to add plug-ins, lack of rights for code execution etc.
• The style of list has been influenced by David Allen's GTD (Getting Things Done) methodology (even if I didn't know that when I started). So I tend to have a lot of "small" todos (like call person x, small coding tasks), rather than humongous items (implement new data warehouse) that follow me around for a long time.
• My own ToDo list gives me the ultimate flexibility. When I want some feature, I can add it. Usually, this is not very critical to using the sheet, so I can add the things in on my own schedule .. and I enjoy playing with code. So it's a little fun thing for me which gives me quite a bit of satisfaction.

If you enjoy tinkering with code as well, this series of posts (tagged ExcelToDo) might be for you. If you just want to use the sheet, that's fine with me as well (but remember that it doesn't come with any warranty).

Here's a short overview of the posts describing the Excel ToDo list:

• First look
• Using the Excel Sheet
• Coding Overview

The code is described in the following posts:

• Automatically updating a changed item
• Calculating the relevance
• Updating the AutoFilter
• Sending an EMail

Next: First look at the Excel Sheet.