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).