Thread: Create multiple to do list

I have data in XL that containing (more than this but important for now) date, name and status (active/non active that is changable).

I would like to create To do list in outlook (or meeting request, appointment...) or anything else that will import all dates and warn me month before expired.
(that will be enough but if there is option to warn me each day or every monday within month if it's non-active even better).

I have hundreds of inputs and they are spread over whole year.

What's best and/or easiest way to do this so I don't need to write in one by one?

I don't know what kind of example to create... if you need some let me know...

I've posted a few times re: importing tasks etc into OL and I'm pretty sure Leith has also - if you do a Search with either of myself / Leith as member and Outlook as topic you should find a few threads that may help... worth also checking out the link re: Outlook VBA in my sig.

Being the capable chap you are see how far you can get under your own steam and then post back perhaps with a sample of where you've got to - if needed we can help from there...

Ty DQ, I can add them manualy since I'm not familiar with VBA (mea culpa, mea culpa, mea massima culpa)

but I need to update them from a time to time and it would be lot easier to import from XL since I have updated there.

Here is example of data and screenshot how I would like it to look.
If someone have time and will can make VBA.
If it's too heavy can ignore it (I don't have a clue).

Input data: example.xls

Screen shot: tasks.jpg

What happens when the Task has been completed ? ie Date3 is "Completed"

Are you looking to re-run this routine over-and-over and if so what happens to pre-existing Tasks ?

Still not clear to me what is meant to happen when the date fields are not dates ... in theory this will cause problems so in the below for the sake of demo I have ignored... as is this would take up too much of my time and I think you should enough to work from below....

Code:

Public Sub Demo()
Dim OLApp As Outlook.Application, OLTask As Outlook.TaskItem, OLPattern As Outlook.RecurrencePattern
Dim rngData As Range, rngCell As Range
Dim strBody As String
On Error GoTo Handler
Set OLApp = New Outlook.Application
Set rngData = Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))
For Each rngCell In rngData.Cells
    'Limit Set Initially to Prevent Mass Production !
    If rngCell.Row > 10 Then Exit Sub
    'Validate Required Fields
    If Val(rngCell.Offset(, 5).Value) > 0 And Val(rngCell.Offset(, 7).Value) > 0 And Val(rngCell.Offset(, 8).Value) > 0 Then
        Set OLTask = OLApp.CreateItem(olTaskItem)
        With OLTask
            .Subject = rngCell.Offset(, 6).Value
            .StartDate = rngCell.Offset(, 7).Value
            .DueDate = rngCell.Offset(, 8).Value
            .ReminderTime = DateAdd("m", -1, .DueDate) + TimeSerial(9, 0, 0)
            .ReminderSet = True
            Select Case UCase(rngCell.Offset(, 4).Value)
                Case "IN PROGRESS"
                    .Status = olTaskInProgress
                    .Categories = "Green Category"
                Case "COMPLETED"
                    .Status = olTaskComplete
                    .Categories = "Gray Category"
                Case "NOT STARTED"
                    .Status = olTaskNotStarted
                    .Categories = "Blue Category"
                Case Else
                    .Status = olTaskWaiting
                    .Categories = "Red Category"
            End Select
            .Importance = olImportanceNormal
            .PercentComplete = 0
            'Create Body
            strBody = "Name:" & rngCell.Offset(, 6).Value & vbLf
            strBody = strBody & "Status2:" & rngCell.Offset(, 4).Value & vbLf & vbLf
            strBody = strBody & rngCell.Offset(, 1).Value & "-" & rngCell.Offset(, 2).Value & vbLf & vbLf
            strBody = strBody & rngCell.Offset(, 12).Value & _
                                rngCell.Offset(, 13).Value & "ZZ, " & _
                                rngCell.Offset(, 14).Value & "WW, " & _
                                rngCell.Offset(, 15).Value & "FF " & vbLf & vbLf
            strBody = strBody & Cells(1, 1) & ":" & rngCell.Value & vbLf
            strBody = strBody & "Status1:" & rngCell.Offset(, 3).Value & vbLf & vbLf
            strBody = strBody & "Date1:" & rngCell.Offset(, 5).Text & vbLf
            strBody = strBody & "Date2:" & rngCell.Offset(, 7).Text & vbLf
            strBody = strBody & "Date3:" & rngCell.Offset(, 8).Text & vbLf & vbLf
            strBody = strBody & "Price1:" & rngCell.Offset(, 9).Text & vbLf
            strBody = strBody & "Price2:" & rngCell.Offset(, 10).Text & vbLf
            strBody = strBody & "Sum:" & rngCell.Offset(, 11).Text & vbLf & vbLf
            .Body = strBody
            'Set Recurrence
            Set OLPattern = OLTask.GetRecurrencePattern
            OLPattern.RecurrenceType = olRecursYearly
            OLPattern.PatternStartDate = .StartDate
            OLPattern.PatternEndDate = DateAdd("yyyy", 10, .StartDate)
            'Save
            .Save
        End With
        'Clear Recurrence Pattern & Task Prior to Iteration
        Set OLPattern = Nothing
        Set OLTask = Nothing
    End If
Next rngCell
exitpoint:
Set rngData = Nothing
Set OLApp = Nothing
Exit Sub

Handler:
MsgBox "Error Has Occurred" & vbLf & vbLf & _
        "Error Number: " & Err.Number & vbLf & vbLf & _
        "Error Desc.: " & Err.Description, _
        vbCritical, _
        "Fatal Error"
Resume exitpoint
End Sub

Note line in red - this is to limit the amount of tasks being created initially -- else you OL is liable to overflow.. ie only run full blooded version once you know it's doing exactly what you want.

Re: formatting Body etc... you can use HTML format if you want (ie use HTML mark-up) and you can also make use of HEX codes in addition to standard vblf etc... you will find plenty of examples on line.

The above is designed solely for use with OL2007 given use of Categories.

Originally Posted by DonkeyOte

What happens when the Task has been completed ? ie Date3 is "Completed"

Are you looking to re-run this routine over-and-over and if so what happens to pre-existing Tasks ?

Going to some bussines trip so I don't know will I manage to test it before friday.

Script not planned to run all the time.

Most of the time, once I upload all tasks, they are rarely changing - let say 20-30 per year so I can handle them manually.

But when I run script next time it can write over existing data - data in excel should always be correct.

I get this error:

untitled.JPG

The code utilises early binding and thus requires reference to OL Object Library (VBE - Tools - References: Microsoft Outlook 12.0 Object Library)
I had presumed you would be aware of that requirement.

Nope sorry.... My Macro is so poor that I don't know even what's macrobiotic

Now is OK, only two more things:

Regarding to your upper question, now I get duplicated tasks.
Can previous be overwritten?

Also, I get 6 values in (not all).
is that connected to

Code:

If rngCell.Row > 10 Then Exit Sub

part of code or somewhere else?

I run it 4 times so I have now 24 values (6 values duplicated 4 times).

zbor, as I stated earlier...

Originally Posted by DO

...as is this would take up too much of my time and I think you should enough to work from below....

I think you need to attempt to progress this with some effort on your own part - what you have presently is fairly extensive piece of work ...

Yes, sorry.
I'm really appriciated.

I came to this:

deleting is not problem...
And I figured out why can't get all values:

1. extend to 150
and
2. I must have populated all dates (not empty cells, text etc.)

I did that, putting some dummy data that I can adjust latter.

I have last question (I hope).

How to add in subject G column (like now ) plus B and C?

I think it's here: .Subject = rngCell.Offset(, 6).Value

But not sure....

Can be separated just with spaces, doesn't care... Just because of lot AB-????/???? cells it became unreadable.

Thanks in advance.

GOT IT!!!!!!!!!!

Method of tries and mistakes is still best method

Thank you DQ... Didn't get much about macros - at least I'm able to adopt it

Oh, yes, just for the info: things with labels didn't work. All categories are red categories and every status is Waiting for someone else.

But that's not problem. Every time when I get notification I'll update it and time by time I'll get correct list.

Thanks again.

Originally Posted by zbor

...just for the info: things with labels didn't work. All categories are red categories and every status is Waiting for someone else.

Regards Categories:

The Categories you apply will depend on what they're called in your OL ... for ex. I don't use Categories at all and so by default they are "Green Category", "Red Category" etc... if you have renamed your categories then you will most likely need to utilise those edited names.

HOWEVER - note point below...

Regards Status':

The fact that all are showing as "Waiting" would imply that the (upper case) contents of "E" are not matching any of: "IN PROGRESS", "COMPLETED", "NOT STARTED" ... you should check the cell contents against the Select Case statement options and revise accordingly
ie Red is applied to anything other than the above strings being in Col E
(note: Upper Case conversion - VBA unlike Native XL is by default Case-Sensitive - can be altered (add Option Compare Text at Head of Module) but enforcing common-case when required is sometimes a little safer IMO)

Originally Posted by zbor

I must have populated all dates (not empty cells, text etc.)

Agreed, this was a point I made early on in the piece I think.