MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel Tasks to Outlook

  Asked By: Bonni    Date: Nov 23    Category: MS Office    Views: 1153

I am looking for some help on code that would take tasks listed in
an excel spreadsheet and automiatically insert them into Outlook via a

I am a novice, so I apologize for the "easy" questions. I have gotten so
far as to insert it calling out the specific row. The code is below:

Sub CreateTask()

Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Set olApp = New Outlook.Application
Set olTsk = olApp.CreateItem(olTaskItem)

With olTsk
.Subject = Cells(2, 1)
.StartDate = Cells(2, 2)
.DueDate = Cells(2, 3)
.Categories = Cells(2, 4)
End With

Set olTsk = Nothing
Set olApp = Nothing

End Sub

Obviously, i would rather that it checked to see how many active rows there
are, then inserted that number of tasks using the date for that row in the

Any code suggestions would be appreciated. I have combed the web looking
for this code already written (as I could easily see its applicability)
however I have come up empty.



2 Answers Found

Answer #1    Answered By: Dustin Dean     Answered On: Nov 23

Untested, but I think this is what you are after:

Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Dim lngRow as Long
Set olApp = New Outlook.Application
Set olTsk = olApp.CreateItem( olTaskItem)

lngRow = 2 `start in row  2

While cells(lngrow,1) <> "" ` stops when you hit first blank cell
With olTsk
.Subject = Cells(lngRow, 1)
.StartDate = Cells(lngRow, 2)
.DueDate = Cells(lngRow, 3)
.Categories = Cells(lngRow, 4)
End With
lngRow = lngrow + 1

Set olTsk = Nothing
Set olApp = Nothing

End Sub

Answer #2    Answered By: Ruairidh Anderson     Answered On: Nov 23

Try this... it works for me. You need to create a reference to Outlook for the
code to be executed.

Sub Send_Msg()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Dim rowNum As Integer

Set objOL = New Outlook.Application
For rowNum = 2 To ActiveWorkbook.Worksheets(1).UsedRange.Rows.Count
Set objMail = objOL.CreateItem(olMailItem)
Application.DisplayAlerts = False
With objMail
.To = ActiveWorkbook.Worksheets(1).Cells(rowNum, 1).Value
.Subject = "ABCD"
.Body = "Hello " & ActiveWorkbook.Worksheets(1).Cells(rowNum, 2).Value & ","
& vbCr & vbCr & _
ActiveWorkbook.Worksheets(2).Cells(1, 1) & vbCr & _
ActiveWorkbook.Worksheets(2).Cells(3, 1) & vbCr & _
ActiveWorkbook.Worksheets(2).Cells(5, 1) & vbCr & _
ActiveWorkbook.Worksheets(2).Cells(7, 1) & vbCr & _
ActiveWorkbook.Worksheets(2).Cells(9, 1) & vbCr & _

End With
Next rowNum
Set objMail = Nothing
Set objOL = Nothing
End Sub

Didn't find what you were looking for? Find more on Excel Tasks to Outlook Or get search suggestion and latest updates.