Create Outlook task from Excel Macro?

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

Jay

Try here

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

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Jay Harris wrote:
> 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

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


"**** Kusleika" <[email protected]> wrote in message
news:[email protected]...
> Jay
>
> Try here
>
> http://www.*****-clicks.com/excel/olTask.htm
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Jay Harris wrote:
>> 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
>
>

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

"**** Kusleika" <[email protected]> wrote in message
news:[email protected]...
> Jay
>
> Try here
>
> http://www.*****-clicks.com/excel/olTask.htm
>
> --
> **** Kusleika
> Excel MVP
> Daily Dose of Excel
> www.*****-blog.com
>
> Jay Harris wrote:
>> 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
>
>

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

> Next
>
> Set olTsk = Nothing
> Set olApp = Nothing
>
> End Sub
>

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Hey guys! Any thoughts on how might I modify this code so that it would only make a task if the value in one of the column is below a set number (ie 50)? Would there be anyway of altering the program so that it would recognize if there is already a task in outlook?

Or is there a way that I could modify the code only to make a task from the selected row?