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.
- Use Twitter’s API to search for your tweets.
- If the search returns anything, then read the results.
- For each returned item check it for one of two hashtags: #apt and #tsk.
- If the code finds either hashtag, then create the corresponding item in Outlook — appointment for #apt or task for #tsk.
- Parse the text of the tweet for the item details (e.g. subject, start time, due date, etc.)
- Save the item.
- 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.
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
- The script will not work if Outlook is not open.
- 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.
- 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.
- Open Notepad.
- Copy the code below and paste it into Notepad.
- Edit the code as needed. I’ve included comments where changes are needed.
- Save the file with a .vbs extension. You can name it anything you want.
- Create a scheduled task that runs periodically. You can set the frequency to whatever you want.
'--- 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