Email Auto-Reply – Solution

In the previous posting, I was wondering how to generate an email auto-reply that contains your next free day, based on Calendar appointments. I had been thinking of using Rules, but a comment by Jon Peltier pointed me in the right direction.

The trigger is to handle the event Application_NewMail (the handler goes in the ThisOutlookSession module):

Private Sub Application_NewMail()
    Dim ib As MAPIFolder
    Dim mi As MailItem

    Set ib = _
    Set mi = ib.Items.GetLast

    RespondTo mi
End Sub

The incoming message needs to be inspected for information about the sender, and responded to, if appropriate:

Sub RespondTo(mi_in As MailItem)

    If IsAutoRespond(mi_in) Then
        MakeResponse mi_in
    End If

End Sub

An incoming message has a SenderName property. Oddly, it has a To property, but not a From property (why? am I missing something?). The SenderName is the ‘display’ name, that you typically see in the ‘From’ column of your Inbox, in preference to the actual email address. As far as I can see, this comes from the alias set up for the email account of the sender; it doesn’t correspond to any name field in the corresponding Contact (Full Name, E-mail, etc).

So we need to record the SenderName, as it appears in your Inbox, in a field of the corresponding Contact. We could add a user-defined field, but on the Details tab there’s a Nickname field, which I doubt is ever used, and so can be hi-jacked. Use of this field could be taken as enabling our auto-reply, but to be on the safe side, let’s add a user-defined field AutoRespond, set to True.

The IsAutoRespond function uses the Find function on the Contacts:

Set contact = contactsfolder.Items.Find(filter)


filter = _
     "[Nickname] = " + Chr(34) + mi.SenderName + Chr(34) + _
     " And [AutoRespond] = " + Chr(34) + "True" + Chr(34)

When a filter string is given as a literal, the double-quotes are doubled (e.g. “”True””). I discovered that this isn’t necessary when using chr(34). If the Find fails, then the result variable is Nothing.

MakeResponse replies to the incoming message, using the next free date in the reply message’s body:

Sub MakeResponse(mi_in As MailItem)

    Dim mi_out As MailItem
    Dim nextfree As Date

    Set mi_out = mi_in.Reply

    With mi_out
        .Body = responsetext & NextFreeDay
    End With
End Sub

The NextFreeDay function goes through the Calendar looking for All-Day-Event appointments (these could be fake ones, used just to block out days; I have mine as Free time, so that they don’t clash with ‘real’ appointments). The result starts as tomorrow’s date, and gets pushed into the future, as appointments are found. Weekends and other unavailable dates should be taken into account.

The initial version is pretty basic. It would seem possible to use Calendar information in more sophisticated ways, or indeed to analyse the incoming message content.


3 Responses to “Email Auto-Reply – Solution”

  1. 1 Augusto Albeghi September 19, 2009 at 10:34 am

    Nice! I’d need something similar as I do consulting along with developing Viney@rd.
    One suggestion: what happens if the contact does not ask for a visit in the mail? I’d hit a button before sending back the mail. Then, if I sent back the mail, I should block out the day in the calendar.

  2. 2 Auto News Europe February 13, 2014 at 12:49 am

    Great beat ! I would like to apprentice even as you amend your web site, how cann i subscribe for a blog web site?

    The account aided me a acceptable deal. I hzve been tiny bit acquainted of this your
    broadcast provided bright transparent concept

  1. 1 Managing Email Attachments « Roy MacLean’s VBA Blog Trackback on September 25, 2009 at 12:03 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

September 2009
« Aug   Oct »

%d bloggers like this: