Create Outlook task from Excel Macro?

[Jay Harris]

I want to know if I can create an Outlook task from an Excel macro. If so
can someone help me learn to do this?

Example of existing workbook:

A B C D
E

4 Start date Task Name Description Length of task End
Date

5 Start date Task Name Description Length of task End
Date

6 Start date Task Name Description Length of task End
Date



I would want to have the macro read the "A" column and, if populated, create
an Outlook 2003 task using the data in that row. This should be created for
the currently logged in user. I do understand that script wanings may occur
without something like Redemption, and that would be OK.



Any help would be appreciated,

Jay.Harris(at)removethis.cox.com

 

[Dick Kusleika]

Jay

Try here

http://www.dicks-clicks.com/excel/olTask.htm

 

[Jay Harris]

Thanks for the help, that is the right track...
Now I have jut two issues left.
First, I need to know how to pull the date out of the cell. When I try I get
an run time error 13 type mismatch.
Second, The other issue is how to get it to loop thruogh rows 4 to 30 and
when there is a value in the correlating "A" column to create a task based
on that rows data.

Thanks again

 

[Jay Harris]

OK, I have resolved most of my issues...the last issues I have is, what do I
do when it the loop hits a cell that is blank and it trys to create a task.
It gives the run time error 13 type mismatch beacuse the DueDate field is
reading from a blank cell in the workbook.

Here is my Code:
Private Sub CommandButton1_Click()

Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Dim w As Workbook
Dim s As Worksheet
Dim c As Range

Set s = Worksheets("Project Timeline")
Set olApp = CreateObject("Outlook.Application")

For i = 4 To s.Range("A4").CurrentRegion.Rows.Count
Set c = s.Cells(i, 1)
Set olTsk = olApp.CreateItem(olTaskItem)

With olTsk
.StartDate = c.Value
.Subject = c.Offset(0, 1).Value
.Body = c.Offset(0, 2).Value
.DueDate = c.Offset(0, 5).Value
.Status = olTaskInProgress
.Importance = olImportanceNormal
.Display
End With
Next

Set olTsk = Nothing
Set olApp = Nothing

End Sub

When it hits the first line in which it finds a blank cell, it errors run
time error 13 type mismatch. since I plan to give this spreadsheet to other
members of my team this behavior is not perferred.

Can anyone help?
Thanks

 

[Dick Kusleika]

Jay

You can just test for it. If it's empty do you want to skip the whole row?
Is it if any cells are empty, or one in particular, or is just a blank row?

These lines will skip the Task creation of the cell in column A is empty.

Set olApp = CreateObject("Outlook.Application")

For i = 4 To s.Range("A4").CurrentRegion.Rows.Count
Set c = s.Cells(i, 1)

If Not IsEmpty(c.Value) Then

Set olTsk = olApp.CreateItem(olTaskItem)

With olTsk
.StartDate = c.Value
.Subject = c.Offset(0, 1).Value
.Body = c.Offset(0, 2).Value
.DueDate = c.Offset(0, 5).Value
.Status = olTaskInProgress
.Importance = olImportanceNormal
.Display
End With

End If