Greetings!
I'm an ultra novice when it comes to VBA and have always been able to search for, find, and then make tiny modifications to suit my needs, but this time I need serious help!
I have a custom client tracking and to-do workbook I've been building. I'm looking for code that will sync the first worksheet in my workbook, which contains my to-do list, with Outlook 2010 Tasks. I need it to be a two-way sync because I often create new tasks on my Android device via Exchange when I'm out of the office and I want those tasks to appear on my Excel to-do list when I open the file.
My file is attached, but below is a basic map of columns on the worksheet that I want to sync to Outlook tasks:
Col. A (Client) & Col. B (to-do) = Task Subject (Concatenate, separated by "/" or ":" etc. )
Col. C = Start Date
Col. E = Status
Col. F = Due Date
Now if I were to create a task remotely on my Android device and it syncs with Outlook, and then in turn syncs to the Excel worksheet, that task will not have a designation for Column A, so that can be blank or just a placeholder like, "Select Client".
I found this code somewhere, which was written to accomplish something similar to what I'm looking for, but I'm not savvy enough to take the code I need and modify it to suit:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Edit the constants below as needed so they correctly reflect the column number they appear in in the spreadsheet'
Const WORKREQUEST = 1
Const DESCRIPTION = 2
Const CURRENTSTATUS = 3
Const WORKSTATUS = 4
Const REQUIRED = 5
Const ESTIMATEDCOMPLETION = 6
Dim excSheet As Excel.Worksheet, _
olkApp As Outlook.Application, _
olkFolder As Outlook.MAPIFolder, _
olkTask As Outlook.TaskItem, _
olkProp As Object, _
intRow As Integer, _
datRun As Date
Set olkApp = GetObject(, "Outlook.Application")
If olkApp.Session.CurrentUser = Application.ActiveSheet.Name Then
If MsgBox("Should I sync the tasks with Outlook?", vbQuestion + vbYesNo, "Outlook Synchronization") = vbYes Then
datRun = Now
Set excSheet = Application.ActiveSheet
Set olkFolder = olkApp.Session.GetDefaultFolder(olFolderTasks)
intRow = 2
Do Until excSheet.Cells(intRow, WORKREQUEST) = ""
Set olkTask = olkFolder.Items.Find("[Subject]='" & excSheet.Cells(intRow, WORKREQUEST) & "'")
If TypeName(olkTask) = "Nothing" Then
Set olkTask = olkApp.CreateItem(olTaskItem)
olkTask.UserProperties.Add "ExcelTaskList", olYesNo, True
olkTask.UserProperties.Item("ExcelTaskList").Value = True
olkTask.UserProperties.Add "Synced", olDateTime
End If
With olkTask
.Subject = excSheet.Cells(intRow, WORKREQUEST)
.Body = excSheet.Cells(intRow, DESCRIPTION) & vbCrLf & excSheet.Cells(intRow, CURRENTSTATUS)
Select Case excSheet.Cells(intRow, WORKSTATUS)
Case "Complete"
.Status = olTaskComplete
Case "Deferred"
.Status = olTaskDeferred
Case "In Progress"
.Status = olTaskInProgress
Case "Not Started"
.Status = olTaskNotStarted
Case "Waiting"
.Status = olTaskWaiting
End Select
.DueDate = excSheet.Cells(intRow, REQUIRED)
If excSheet.Cells(intRow, 1) <> "" Then
.ReminderTime = excSheet.Cells(intRow, ESTIMATEDCOMPLETION)
.ReminderSet = True
Else
.ReminderSet = False
End If
olkTask.UserProperties.Item("Synced").Value = datRun
.Save
End With
intRow = intRow + 1
Loop
For intRow = olkFolder.Items.Count To 1 Step -1
Set olkTask = olkFolder.Items(intRow)
Set olkProp = olkTask.UserProperties.Find("ExcelTaskList", True)
If TypeName(olkProp) <> "Nothing" Then
If olkTask.UserProperties.Item("Synced") < datRun Then
olkTask.Delete
End If
End If
Next
End If
End If
Set olkTask = Nothing
Set olkApp = Nothing
Set olkProp = Nothing
Set excSheet = Nothing
End Sub
Please let me know if I need to elaborate!
Thank you so much!
Bookmarks