ForumsTips & TricksCreated outlook macro that creates "waiting"task by BCC yourself

Created outlook macro that creates "waiting"task by BCC yourself
Author Message

Posted: Aug 11, 2013
Score: 0 Reference
I created a macro for Outlook 2010 that will create a "waiting" task in toodledo just by BCCing themselves on the email. This macro is part of my GTD workflow.

The macro uses Toodledo's API to work so you will need to get your own app token and do a little bit of MD5 hashing (you can use the MD5 hash calculator on the toodledo API webpage) to make this macro function, also you will need to reference "Microsoft Scripting Runtime" when in the VBA editor.

I use this macro when I send out an email and I want to create a "waiting" task in toodledo so I do not forget that I need for follow-up with this person if they do not respond. After I BCC my self a dialog box appears where I can type what the actionable task should be and then press OK. The macro then creates a task in toodledo adding the name of the person the email was sent to and the date it was sent and creates a due date for tomorrow (at the end of the day review toodledo and I update the day for when I really want it do).

I am sharing this macro with everyone because I am hoping others can add to it and make it even better. If someone was inclined they could use this macro to create one that will sync outlook and toodledo task if they wanted.

Let me know what you think, and if you have any questions.

Option Explicit
Private Const TD_UserID As String = "username" 'Replace with MD5 hash of your username
Private Const TD_AppToken As String = "AppToken" 'Replace with MD5 hash of your AppToken
Private Const TD_AppID As String = "AppID" 'Replace with MD5 hash of your AppID
Private Const TD_Password As String = "password" 'Replace with MD5 Hash of your password
Private TD_TokenSig As String
Private TD_SessionToken As String
Private TD_Key As String
Private XMLResponse As String
Private XMLtarget As String
Private PostInput As String

Sub WaitingForTask(Item As Outlook.MailItem)
Dim ConfigPath As String
Dim fs, f, LineCount, LineTxt(999), WhatIsNextAction, TDTaskTitle

ConfigPath = "C:\TDfile.cfg"

Set fs = CreateObject("Scripting.FileSystemObject") 'Need to Reference "Microsoft Scripting Runtime"
Set f = fs.OpenTextFile(ConfigPath, 1, True, 0)

'Reads the config file for the If statement below
Do While f.AtEndOfStream <> True
LineCount = LineCount + 1
LineTxt(LineCount) = f.ReadLine

'If statement looks to see if a Session Token already exists that is < than 4 hours old
If DateDiff("d", LineTxt(1), Date) = 0 And DateDiff("n", LineTxt(2), Time) < 240 Then
TD_SessionToken = LineTxt(3)
TD_Key = LineTxt(4)
'Authentication Script
TD_TokenSig = MD5(TD_UserID & TD_AppToken)
PostInput = "" & TD_UserID & ";appid=" & TD_AppID & ";sig=" & TD_TokenSig & ";f=xml"
XMLtarget = "token"
XMLResponse = TD_Com(PostInput)
TD_SessionToken = XMLParser(XMLResponse, XMLtarget)
'Generating Key Script
TD_Key = MD5(TD_Password & TD_AppToken & TD_SessionToken)
Kill (ConfigPath) 'deletes config file if it is older than 4 hours
Set f = fs.OpenTextFile(ConfigPath, 8, True, 0)
f.WriteLine Date 'Writes the date into the config file
f.WriteLine Format(Time, "hh:mm:ss") 'Writes the time into the config file
f.WriteLine TD_SessionToken 'Writes the session token into the config file
f.WriteLine TD_Key 'Writes the key into the config file
End If

'Add Task
'Creates a box that allows you to specify the task action
WhatIsNextAction = InputBox("What is the Next Action for this @Waiting For Item?", "Next Action")

'Creates a box that allows you to specify the task action
TDTaskTitle = Item.To & " " & Format(Item.SentOn, "mm/dd/yy") & ": " & WhatIsNextAction '& " (" & Item.Subject & ")"
PostInput = "" & TD_Key & ";tasks=[{""title""%3A""" & TDTaskTitle & """%2C""folder""%3A""3521051""%2C""context""%3A""1053099""%2C""duedate""%3A""" & DateDiff("s", "01/01/1970 00:00:00", Format(Item.SentOn, "mm/dd/yy")) & """}];fields=folder,context,duedate;f=xml"

TD_Com (PostInput)
End Sub

Function TD_Com(pInput As String) As String
Dim oXml As Object: Set oXml = CreateObject("MSXML2.DomDocument")
Dim oReq As Object: Set oReq = CreateObject("MSXML2.XMLHttp")

With oReq
.Open "POST", pInput, False
.Send ("")
oXml.async = False 'Load entire document before moving on
oXml.validateOnParse = False 'Do not validate
MsgBox .responseText
XMLResponse = .responseText
End With

TD_Com = XMLResponse
End Function

Function XMLParser(XMLResponse As String, XMLtarget As String) As String
Dim OpenTag As String, CloseTag As String, StartPos As String, EndPos As String, StartTagPos As String
OpenTag = "<" & XMLtarget & ">"
CloseTag = "</" & XMLtarget & ">"

StartPos = InStr(1, XMLResponse, OpenTag)
EndPos = InStr(1, XMLResponse, CloseTag)
StartTagPos = InStr(StartPos, XMLResponse, ">") + 1
XMLParser = Mid(XMLResponse, StartTagPos, EndPos - StartTagPos)
End Function

Function MD5(sInput As String) As String
Dim oTxt As Object
Dim b() As Byte
Dim l As Long

Set oTxt = CreateObject("System.Text.UTF8Encoding")

With CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
b = .ComputeHash_2((oTxt.GetBytes_4(sInput)))
End With

For l = LBound(b) To UBound(b)
MD5 = LCase(MD5 & Right$("00" & Hex$(b(l)), 2))
Next l
End Function

Posted: Sep 13, 2013
Score: 0 Reference
How is this script triggered on the BCC? I don't see that part in the code.
Jason Bushell

Posted: Sep 18, 2013
Score: 0 Reference
Its far easier to create a Quick Step in Outlook 2010.

Posted: Sep 24, 2013
Score: 0 Reference
Sorry I had forgot to mention that you have to create a rule in outlook that runs the macro when you BCC yourself. I'm working on an update to the macro that will pull in the folder data for a combo box and a calendar to set the due date.

Not sure how you would create a quick step that would what this macro does... Quick steps are very limited in their functionality.
You cannot reply yet

U Back to topic home

R Post a reply

To participate in these forums, you must be signed in.