Consulting

Results 1 to 7 of 7

Thread: Tasks from Excel sheet in Outlook VBA

  1. #1
    VBAX Newbie
    Joined
    Feb 2020
    Posts
    4
    Location

    Tasks from Excel sheet in Outlook VBA

    Hi everyone!,

    I am a newbie in VBA and macros and I am wondering if it's possible to import a tasks list from Excel to Outlook and get instant pop ups (eg. one your before the task deadline) basing on that list.

    The problem is that not the whole date is in one cell.
    Dates are in column A while hour of task in row 4.
    The task is on the intersection of these lines (column A & row 4).

    Once the task is found I would like to assign it as task with the appropriate date and time in Outlook to get automatically pop ups.



    Could anyone can help me how to figure it out? Attached the sheet with tasks.​

    Capture.JPG

    Thank you in advance!​​

  2. #2
    Based on your screenshot, the following Excel macro should do the job. Note the comment at the start of the macro as you will need to get some additional code from the link.

    Option Explicit
    'Graham Mayor - https://www.gmayor.com - Last updated - 23 Feb 2020 
    Sub CreateOutlookTasks()
    Dim xlSheet As Worksheet
    Dim LastRow As Long, LastCol As Long
    Dim lngCol As Long, lngRow As Long
    Dim strSubject As String
    Dim strDate As String
    Dim dDate As Date
    
        Set xlSheet = ActiveSheet
        With xlSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column
            For lngRow = 9 To LastRow
                For lngCol = 3 To LastCol
                    If Not .Cells(lngRow, lngCol) = "" Then
                        strSubject = .Cells(lngRow, lngCol)
                        strDate = .Cells(lngRow, 1) & Chr(32) & CDate(.Cells(4, lngCol))
                        dDate = CDate(strDate)
                        AddTask strSubject, dDate
                    End If
                Next lngCol
            Next lngRow
        End With
        MsgBox "Task list created"
    lbl_Exit:
        Set xlSheet = Nothing
        Exit Sub
    End Sub
    
    Private Sub AddTask(strSubject As String, dDate As Date)
    'Graham Mayor - https://www.gmayor.com - Last updated - 23 Feb 2020 
    'Requires the code by Ben Clothier - http://www.rondebruin.nl/win/s1/outlook/openclose.htm
    'to either retrieve an open instance of Outlook or open Outlook if it is closed.
    Dim olApp As Object
    Dim olTask As Object
    On Error GoTo err_Handler:
        Set olApp = OutlookApp()
        Set olTask = olApp.CreateItem(3)
        With olTask
            .Subject = strSubject
            .StartDate = dDate
            .DueDate = dDate
            .Importance = 2
            .ReminderSet = True
            .ReminderTime = dDate
            .Close 0
        End With
    lbl_Exit:
        Set olApp = Nothing
        Set olTask = Nothing
        Exit Sub
    err_Handler:
        Beep
        MsgBox "User Cancelled", vbCritical, "Cancelled"
        Err.Clear
        GoTo lbl_Exit
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Newbie
    Joined
    Feb 2020
    Posts
    4
    Location
    Thank you. I guess "AddTask" is not defined as I am getting errors "Sub or Function not defined"

    Anyway, I have managed to structure my data in a list form to be uploaded to Outlook.
    The problem I have not mentioned before is that in column A I do not have hour values but "0, 1, 3, 4, 5" and similarly in column A - "(1 November)" instead of eg. "1/11/2020". Additionally I need to drag this value through empty cells till the next filled cell.
    Is there any possibility to do this quickly? Can I use Find and Replace for hours in Row 4?

    Attached 2 print screens - one from the initial data and the second one from already grouped data.

    Capture.JPGCapture3.JPG

  4. #4
    Did you read my comment about the additional function that you require from the link? It won't work without it.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Newbie
    Joined
    Feb 2020
    Posts
    4
    Location
    Quote Originally Posted by gmayor View Post
    Did you read my comment about the additional function that you require from the link? It won't work without it.
    Thank you. I have used a part of that code and it's working quite decently.

    One last question. If I export my apart list of tasks as csv is there any possibility to import it quickly using VBA instead of Import or Export Wizard?

    Please see the print screen for your reference

    Capture.JPG

  6. #6
    A CSV is a text file. You can use VBA to read each line of a text file and split the line at the separators to provide the data for the AddTask sub.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Newbie
    Joined
    Feb 2020
    Posts
    4
    Location
    Thank you for the tip.
    Finally I have use a different way to make it uploaded in Outlook.
    Once again thank you for the help and code! Giant accomplishment!

Posting Permissions

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