Creating Outlook Tasks and Appointments via Twitter


One day last February (2011) I was checking for tweets mentioning Microsoft Outlook and stumbled across this one from Richard Harbridge.

oh how I wish I could turn tweets into action items in outlook.

I was already aware of Twittercal (a service that turns tweets into entries on a Google calendar) so the basic idea isn’t new. I do see value in the concept and it sounded like an interesting challenge, and I love anything that’s challenging, especially if it involves Outlook. I had written a couple of scripts that send tweets, since rendered obsolete when Twitter discontinued basic authentication, but I’d never written anything that reads them. I have also developed Outlook macros that allow me to create tasks, add appointment, even fetch files from my computer via emails I send to myself. Except for the delivery mechanism (i.e. a tweet instead of an email) the process is essentially identical.

I did some research on reading tweets, found a couple of code examples to learn from, and cobbled together a workable solution. I can now send myself a tweet that creates an appointment or task in Outlook. The code I’m showing here is a proof-of-concept. For simplicity’s sake I’ve used VBScript and Windows’ built-in task scheduler. If this were a professional solution, then I’d implement it as an Outlook add-in or, better, a Windows service.

Here’s how it works.

  1. Use Twitter’s API to search for your tweets.
  2. If the search returns anything, then read the results.
  3. For each returned item check it for one of two hashtags: #apt and #tsk.
  4. If the code finds either hashtag, then create the corresponding item in Outlook — appointment for #apt or task for #tsk.
  5. Parse the text of the tweet for the item details (e.g. subject, start time, due date, etc.)
  6. Save the item.
  7. Save the ID number of the most recent tweet so the next time the script runs it can start from that point thereby saving time avoiding the problem of duplicate items.

Usage.

To create a task or appointment in Outlook you need only send a tweet with the details and the appropriate hashtag: #tsk (for a task) or #apt (for an appointment). You do need to properly format the tweet (hey, this is a proof-of-concept, I haven’t time to write a natural language interface). Parameters can appear in any order with the exception that starting dates/times must come before ending dates/times. None of the parameters are required. Parameter names are not case sensitive. By default parameters are separated from each other by the | character. Each parameter consists of a label and a value. These are separated from each other by a colon. You can change both separators as desired. You can also change the label names if you choose. Should you decide to do that, then you’ll have to edit the code accordingly.

Appointments. The parameters for an appointment are

  • Sub. The subject of the appointment. Enter any text you want save for either of the separator characters.
  • Start. The starting date/time of the appointment. Enter a date and time, just a time, or a number between 0 and 59. If you enter just a time, then the script assumes the appointment occurs today. Enter times in 24-hour format and with no separators (i.e. 1000 not 10:00). If you enter a number between 0 and 59, then the script assumes the appointment starts that many minutes from the time it receives the tweet.
  • End. The ending date/time of the appointment. The same rules apply as for Start. If you enter a number between 0 and 59, then the script assumes that the appointment ends that many minutes from the start time.
  • Len. The length of the appointment in minutes. This can be used in lieu of an end time.
  • Rem. Set a reminder for this appointment. The value passed can be Y, Yes, True, or 1 for True; N, No, False, or 0 for False.
  • Note. A note that goes in the body of the appointment. This can be anything you want so long as it does not include either of the separator characters.
  • Cat. This assigns the appointment to one or more categories. This can be anything you want so long as it does not include either of the separator characters.
  • Loc. The location of the meeting. This can be anything you want so long as it does not include either of the separator characters.

Here are a couple of examples.

  • Create an appointment to have lunch with Bob at The Diner at noon on November 11th. Reserve an hour and give me a reminder.
    sub:Lunch with Bob|start:11/11/2011 1200|len:60|loc:The Diner|rem:y #apt
  • Create an appointment to meet with Tom at 10:45 today for 30 minutes. Set a reminder and include a note to bring the sales figures.
    sub:Meet Tom|start:1045|len:30|rem:y|note:Bring sales figures #apt

Tasks. The available parameters for a task are

  • Sub. The subject of the task. Enter any text you want except for either of the separator characters.
  • Start. The start date of the task. Enter a date or a number. If you enter just a number, then the script adds that number of days to today’s date.
  • Due. The due date of the task. The same rules apply as for Start. If you enter just a number, then the script adds that many days to the start date.
  • Rem. Set a reminder for this task. The value passed can be Y, Yes, True, or 1 for True; N, No, False, or 0 for False.
  • Note. A note that goes in the body of the task. This can be anything you want so long as it does not include either of the separator characters.
  • Cat. This assigns the task to one or more categories. This can be anything you want so long as it does not include either of the separator characters.

Here are a few examples.

  • Create a task to remind yourself to brainstorm your next article.
    sub:Brainstorm article topic #tsk
  • Create a task to prepare next month’s sales report. The report is due on March 28th. Set a reminder.
    sub:Prepare sales report|due:3/28/2011|rem:y #tsk
  • Create a task with a subject of “Weekly Sales Report” that starts on November 14th, is due on November 18th, has a note of “Send report to Sally”, includes a reminder, and belongs to the category “Reports”.
    sub:Weekly Sales Report|start:11/14/2011|due:11/18/2011|note:Send report to Sally|rem:y|cat:Reports #tsk

Notes.

  1. The script will not work if Outlook is not open.
  2. I’ve not done extensive testing on this, so there may be errors. If you use this and discover an error, then please let me know and I’ll see about fixing it. No promises though.
  3. Remember that this is a proof-of-concept. It’s not intended to be a working solution although you may use it that way if you want to.

Adding the Code.

Follow these instructions to set up the script on your computer.

  1. Open Notepad.
  2. Copy the code below and paste it into Notepad.
  3. Edit the code as needed. I’ve included comments where changes are needed.
  4. Save the file with a .vbs extension. You can name it anything you want.
  5. Create a scheduled task that runs periodically. You can set the frequency to whatever you want.

Code.

'--- On the next line change TechnicLee to your Twitter name ---'
Const TWITTER_NAME = "TechnicLee"
Const TWITTER_URL = "http://search.twitter.com/search.atom?q=from

%3A*USER*&rpp=50&page=1"
Const INI_FILE = "LastTweet.ini"
'--- On the next two lines change the separators as desired.  The first character 

separates parameters, the second separates an individual parameter label from its 

value ---'
Const SEP_PARAM = "|"
Const SEP_OPT = ":"

Dim strLastID, strFolder, objFSO, objFile, objShell, olkApp

Set olkApp = GetObject(, "Outlook.Application")
If IsEmpty(olkApp) Or TypeName(olkApp) = "Nothing" Then
    WScript.Echo "Failure"
Else
    Set objShell = CreateObject("WScript.Shell")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    strFolder = objShell.SpecialFolders("MyDocuments") & "\"
    If objFSO.FileExists(strFolder & INI_FILE) Then
        Set objFile = objFSO.OpenTextFile(strFolder & INI_FILE)
        strLastID = objFile.ReadAll
        objFile.Close
    Else
        strLastID = ""
    End If
    Check4Tweets
    Set objFile = objFSO.CreateTextFile("C:\Users\David\Documents\" & INI_FILE, 

True)
    objFile.Write strLastID
    objFile.Close
    Set olkApp = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
    Set objShell = Nothing
End If
WScript.Quit

Sub Check4Tweets()
    Dim objHTTP, objXMLDoc, objDoc, objNodes, objNode, strURL, strTweet, arrTemp
    On Error GoTo 0
    strURL = Replace(TWITTER_URL, "*USER*", TWITTER_NAME) & "&since_id=" & 

strLastID
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    With objHTTP
        .Open "GET", strURL, False
        .setRequestHeader "User-Agent", "techniclee.wordpress.com"
        .setTimeouts 100000, 100000, 100000, 100000
        .Send ("")
    End With
    intCount = 1
    Set objXMLDoc = CreateObject("MSXML2.DOMDocument")
    objXMLDoc.LoadXML objHTTP.responseText
    Set objDoc = objXMLDoc.DocumentElement
    Set objNodes = objDoc.ChildNodes
    For Each objNode In objNodes
        If objNode.nodeName = "entry" Then
            arrTemp = Split(objNode.SelectSingleNode("id").Text, ":")
            If arrTemp(2) > strLastID Then
                strLastID = arrTemp(2)
            End If
            strTweet = objNode.SelectSingleNode("title").Text
            If InStr(1, strTweet, "#tsk") Then
                CreateTaskFromTweet strTweet
            Else
                If InStr(1, strTweet, "#apt") Then
                    CreateAppointmentFromTweet strTweet
                End If
            End If
        End If
    Next
    Set objHTTP = Nothing
    Set objXMLDoc = Nothing
    Set objDoc = Nothing
    Set objNodes = Nothing
    Set objNode = Nothing
End Sub

Sub CreateAppointmentFromTweet(strAppointmentInfo)
    Dim olkAppointment, arrParams, varParam, arrOption, strOption, strValue
    arrParams = Split(Replace(strAppointmentInfo, "#apt", ""), SEP_PARAM)
    Set olkAppointment = olkApp.CreateItem(1)
    For Each varParam In arrParams
        arrOption = Split(varParam, SEP_OPT)
        strOption = Trim(arrOption(0))
        strValue = Trim(arrOption(1))
        Select Case LCase(arrOption(0))
            Case "sub"
                olkAppointment.Subject = strValue
            Case "start"
                If IsDate(ConvertToValidDate(strValue)) Then
                    olkAppointment.Start = ConvertToValidDate(strValue)
                Else
                    If IsNumeric(strValue) Then
                        If Len(strValue) = 4 Then
                            olkAppointment.Start = Date & " " & 

ConvertTo12HourClock(CStr(strValue))
                        Else
                            olkAppointment.Start = DateAdd("n", strValue, Now)
                        End If
                    Else
                        olkAppointment.Start = Now
                    End If
                End If
            Case "end"
                If IsDate(arrOption(1)) Then
                    olkAppointment.End = strValue
                Else
                    If IsNumeric(arrOption(1)) Then
                        olkAppointment.DueDate = olkAppointment.Start + strValue
                    Else
                        olkAppointment.DueDate = Now
                    End If
                End If
            Case "len"
                If IsNumeric(arrOption(1)) Then
                    olkAppointment.Duration = strValue
                End If
            Case "rem"
                Select Case LCase(strValue)
                    Case "y", "yes", "true", "1"
                        olkAppointment.ReminderSet = True
                    Case "n", "no", "false", "0"
                        olkAppointment.ReminderSet = False
                End Select
            Case "note"
                olkAppointment.Body = strValue
            Case "cat"
                olkAppointment.Categories = strValue
            Case "loc"
                olkAppointment.Location = strValue
        End Select
    Next
    olkAppointment.Save
    Set olkAppointment = Nothing
End Sub

Sub CreateTaskFromTweet(strTaskInfo)
    Dim olkTask, arrParams, varParam, arrOption, strOption, strValue
    arrParams = Split(Replace(strTaskInfo, "#tsk", ""), SEP_PARAM)
    Set olkTask = olkApp.CreateItem(3)
    For Each varParam In arrParams
        arrOption = Split(varParam, SEP_OPT)
        strOption = Trim(arrOption(0))
        strValue = Trim(arrOption(1))
        Select Case LCase(strOption)
            Case "sub"
                olkTask.Subject = Trim(strValue)
            Case "start"
                If IsDate(ConvertToValidDate(strValue)) Then
                    olkTask.StartDate = ConvertToValidDate(strValue)
                Else
                    If IsNumeric(strValue) Then
                        olkTask.StartDate = DateAdd("d", strValue, Now)
                    Else
                        olkTask.StartDate = Date
                    End If
                End If
            Case "due"
                If IsDate(ConvertToValidDate(strValue)) Then
                    olkTask.DueDate = ConvertToValidDate(strValue)
                Else
                    If IsNumeric(strValue) Then
                        olkTask.DueDate = olkTask.StartDate + strValue
                    Else
                        olkTask.DueDate = Date
                    End If
                End If
            Case "rem"
                Select Case LCase(strValue)
                    Case "y", "yes", "true", "1"
                        olkTask.ReminderSet = True
                    Case "n", "no", "false", "0"
                        olkTask.ReminderSet = False
                End Select
            Case "note"
                olkTask.Body = strValue
            Case "cat"
                olkTask.Categories = strValue
        End Select
    Next
    olkTask.Save
    Set olkTask = Nothing
End Sub

Function ConvertTo12HourClock(strValue)
    Dim intTemp, bolPM, intHours, intMinuts
    intTemp = Int(strValue)
    If intTemp >= 1300 Then
        intHours = Int(Left(strValue, 2) - 12)
        intMinutes = Int(Right(strValue, 2))
        bolPM = True
    Else
        intHours = Int(Left(strValue, 2))
        intMinutes = Int(Right(strValue, 2))
    End If
    ConvertTo12HourClock = intHours & ":" & intMinutes & " " & IIf(bolPM, "PM", 

"AM")
End Function

Function ConvertToValidDate(strValue)
    Dim arrParts
    arrParts = Split(Trim(strValue), " ")
    If IsDate(arrParts(0)) Then
        ConvertToValidDate = arrParts(0)
        If UBound(arrParts) = 1 Then
            If IsNumeric(arrParts(1)) Then
                If Len(arrParts(1)) = 4 Then
                    ConvertToValidDate = ConvertToValidDate & " " & 

ConvertTo12HourClock(CStr(arrParts(1)))
                Else
                    ConvertToValidDate = strValue
                End If
            Else
                ConvertToValidDate = strValue
            End If
        Else
            ConvertToValidDate = strValue
        End If
    Else
        ConvertToValidDate = strValue
    End If
End Function

Function IIF(varCondition, varTruePart, varFalsePart)
    If varCondition Then
        IIF = varTruePart
    Else
        IIF = varFalsePart
    End If
End Function
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s