+ Reply to Thread
Results 1 to 2 of 2

VBA to sync Excel To-Do sheet with Outlook Tasks (2010)?

Hybrid View

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    13

    VBA to sync Excel To-Do sheet with Outlook Tasks (2010)?

    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!
    Attached Files Attached Files
    Last edited by JBeaucaire; 05-06-2013 at 07:56 PM.

  2. #2
    Registered User
    Join Date
    04-11-2013
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: VBA to sync Excel To-Do sheet with Outlook Tasks (2010)?

    Anyone out there have any ideas?
    So very much obliged!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1