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!
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:
Application.Session.GetDefaultFolder(olFolderInbox).Items.GetLast()
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.
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!
/Roy
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