Excel data to Outlook tasks

MrDoc

Active Member
Joined
Jul 30, 2002
Messages
343
Hi,

I searched this topic and found some interesting code from TommyGun
- Sub EmailTasks(). But I still need some explanation on how the code works, and what it does exactly. If in my worksheet column A contains dates, column B times, column C tasks and column D reminder (Yes or No, for example)... how could I update Outlook (XP) Tasks with this data (or just the new data, for instance after a given date)?

I'd appreciate any help you can give me on this topic.

Best Regards,
MrDoc
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

MrDoc

Active Member
Joined
Jul 30, 2002
Messages
343
Hello,

No one replied until now, so I'm including the code I meant, maybe I should have done it in the first place. I only need to pass data from an Excel worksheet to Outlook, defining/updating tasks or events for my own use, I don't need to send mail to anyone.
Can anyone please explain in a general way what the different parts of this code do, and if possible modify it to send data from the worksheet "Sheet1", range A2:A & lastrow in column A to the outlook tasks or events? What happens if some of the tasks defined in Excel are already present in Outlook?



Sub EmailTasks()
Dim objOut As Object
Dim objTask As Object
Dim objMenu As Object
Dim objCommand As Object

Dim blnCrt As Boolean

Dim aryMail() As String
Dim strMail As String

Dim intaddr As Integer
Dim i As Integer

Dim c As Range
Dim r As Range

Dim j As Long

j = ActiveSheet.[A65536].End(xlUp).Row

If j < 5 Then Exit Sub

With ActiveSheet
Set r = .Range(.[A5], .Cells(j, 1))
End With

On Error Resume Next
Set objOut = GetObject(, "Outlook.Application")
If objOut Is Nothing Then
Set objOut = CreateObject("Outlook.Application")
blnCrt = True
If objOut Is Nothing Then
MsgBox "Unable to start Outlook."
Exit Sub
End If
End If
On Error GoTo 0

For Each c In r.Cells
If c.Value <> vbNullString Then
Set objTask = objOut.CreateItem(3)

With objTask
.Display
.Assign
.Subject = Sheets("Master List").Range("A1").Value 'mudar folha
.Body = c & " - " & c.Offset(0, 1).Value
.DueDate = c.Offset(0, 4).Value
.ReminderTime = DateAdd("ww", -1, .DueDate)
End With

ReDim aryMail(0) As String

strMail = c.Offset(0, 2)
intaddr = 0

Do While InStr(1, strMail, Chr(10)) <> 0
If intaddr > 0 Then
ReDim Preserve aryMail(UBound(aryMail) + 1) As String
aryMail(UBound(aryMail)) = Left(strMail, InStr(1, strMail, Chr(10)) - 1)
strMail = Mid(strMail, InStr(1, strMail, Chr(10)) + 1, Len(strMail) - InStr(1, strMail, Chr(10)))
intaddr = intaddr + 1
Else
aryMail(0) = Left(strMail, InStr(1, strMail, Chr(10)) - 1)
strMail = Mid(strMail, InStr(1, strMail, Chr(10)) + 1, Len(strMail) - InStr(1, strMail, Chr(10)))
intaddr = intaddr + 1
End If
Loop

If Len(strMail) > 0 Then
If intaddr > 0 Then
ReDim Preserve aryMail(UBound(aryMail) + 1) As String
aryMail(UBound(aryMail)) = Trim(strMail)
Else
aryMail(0) = Trim(strMail)
End If
End If

With objTask
For i = 0 To UBound(aryMail)
.Recipients.Add aryMail(i)
Next

Set objMenu = objOut.ActiveInspector.CommandBars("Tools")
Set objCommand = objMenu.Controls("Check Names")

objCommand.Execute

Set objMenu = Nothing
Set objCommand = Nothing

On Error Resume Next
.Send

If Err <> 0 Then
.Close 1

Set objTask = objOut.CreateItem(3)

With objTask
.Display
.Subject = Sheets("Master List").Range("A1").Value
.Body = c & " - " & c.Offset(0, 1).Value
.DueDate = c.Offset(0, 4).Value
.ReminderTime = DateAdd("ww", -1, .DueDate)
.Close (0)
End With
End If

On Error GoTo 0
End With

Set objTask = Nothing

End If
Next

If blnCrt = True Then objOut.Quit

Set objTask = Nothing
Set objOut = Nothing
Set r = Nothing
End Sub


Thank you very much for any help.
Best Regards,
MrDoc
 

MrDoc

Active Member
Joined
Jul 30, 2002
Messages
343
Re: TommyGun can you please help me?

Hi,

This is just a try to catch TommyGun's attention to this topic, because he wrote the code I quoted in my previous message. I still can´t figure out some parts of the code, so any help would be much welcome.
What I mostly need is a simple (?) way to add tasks to Outlook 2002, based on some cells in Excel - date, task, reminder on or off, reminder time if on. If Tommy or anyone can provide a simple example with some comments to help me figure out what each part does, or redirect me to an article where this is explained, I'd appreciate. Thanks for any help.
Best Regards,
MrDoc
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,095
Messages
5,768,053
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top