Tasks from Excel sheet in Outlook VBA

[Thoro]

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.​

26061

Thank you in advance!​​​

[gmayor]

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

[Thoro]

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.

2606626067

[gmayor]

Did you read my comment about the additional function that you require from the link? It won't work without it.

[Thoro]

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

26068

[gmayor]

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.

[Thoro]

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!