Email Auto-Reply with Availability

My wife’s a Business Intelligence (sic) consultant, writing reports on hideously complicated databases. As is the way of things, she does reports for different customers and projects. The difficulty lies in getting across the idea that when working for customer X, she’s not available to fire-fight problems for customer Y. So she responds to emails, saying “I’m sorry, my next availability to look at your problem is on <date>”, where <date> is found by looking in her diary for the next free day.

Assuming that you’re using Outlook as both email client and calendar, is it possible to generate such replies automatically? Presumably incoming emails (with certain properties, say Senders) can be detected using Rules, and a reply sent. But can we intercept the rule-processing, and insert information derived from the Calendar (or indeed elsewhere) into the reply message?

Having not done a lot with Outlook (compared with Excel and Access), I have no idea at this stage, but I’ll investigate, and report back. Obviously, if anyone out there has done this already, let me know!


5 Responses to “Email Auto-Reply with Availability”

  1. 1 Jon September 4, 2009 at 2:26 pm

    You don’t need to use a rule-just put the Application_NewMail() sub in the ThisOutlookSession module in Outlook VBA. From there you should look at the latest item in the inbox, using this:

    Set that to a MailItem dimmed variable, and you can explore the properties, including Sender, and maybe have an Excel worksheet listing email addresses to respond to.

    Then you’d need to look through the calendar and find a clear day. You could use this function to filter appointment items between two dates into an items collection:

    Function GetAppointmentsBetweenDates(StartDate As Date, EndDate As Date) As Items
    Dim CalItems As Items
    Set CalItems = Application.Session.GetDefaultFolder(olFolderCalendar).Items
    CalItems.Sort “[Start]”
    CalItems.IncludeRecurrences = True
    Set GetAppointmentsBetweenDates = CalItems.Restrict(“[Start] > ” & Quote(Format(StartDate, “dd mmm yyyy”) & ” 12:00 AM”) & _
    ” AND [End] < " & Quote(Format(EndDate, "dd mmm yyyy") & " 12:00 AM"))
    End Function

    and to tie it all together, you'd have to just write a sub that checks if the sender is in your Excel worksheet or wherever you keep the list, and if so, calls a function to find the next available day, then creates a reply to their email using the .Reply() function on the MailItem object, setting the Body of the message, and then either use the Display method or Send method (Display if you want your wife to be able to edit before sending)

    Hope that gives you a head start.

  2. 2 Roy MacLean September 4, 2009 at 2:38 pm

    Thanks, Jon, that’s pointed me in the right direction. I’ll have a go at it. To be honest, it’s for curiosity/fun, as I don’t think my wife actually uses Outlook!


  3. 3 JP September 4, 2009 at 4:33 pm

    For Jon’s function you would also need the Quote function, which is

    Private Function Quote(MyText)
    Quote = Chr(34) & MyText & Chr(34)
    End Function

  4. 4 May 30, 2013 at 1:58 am

    Uninsured persons are most often diagnosed with advanced stage diseases 30 to 50
    percent than persons who are insured due to lack of
    preventive or medical care. In addition to that, the health insurance coverage will endow you with the facility to have
    a regular check up with the interval of ten to twelve months including your
    physical check up along with checking up several
    other necessary things such as blood, mammogram, colonoscopy, immunization test
    and many more, if necessary. These include proper eating habits, regular exercise and
    limiting intake of substances like caffeine and alcohol.

  1. 1 Email Auto-Reply – Solution « Roy MacLean’s VBA Blog Trackback on September 15, 2009 at 5:54 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: