Outlook Tasks

I have searched and think I have found what I need to create Outlook tasks from my Excel spreadsheet. I'm hoping that someone here can help me pull it together into something that will be usable in my project tracking spreadhsheet...

I try to keep track of milestones... these milestones are listed in column B of my spreadsheet and are in rows 10 through about 105

For each of my customers I then keep 4 columns D, E, F, G - H, I, J, K - L,M,N,O - etc. Each group of 4 is a different customer....

The customer's first column (D, H, L, etc.) is a Due date of the Milestone that is found in column B.
The second column is a check box that I use to indicate when the milestone is complete
The 3rd column is the completion date
The last colum is just a filler space that helps to separate each customer.

What I would like to do is insert a button at the bottom of EACH grouping of 4 that when pressed would look at the first column for the customer group and for ANY Milestone that has a DUE date process the stuff in the VB below to create an OUTLOOK task for each item that has a DUE date. (It would be conceivable that as it stepped through each row for a customer group of columns that I could have it create 90+ Tasks in Outlook.

A couple of additional things... in row 110 for the first column of the customer I have a DATE or a blank. So for example in D110 it might be blank or a date like 10/29/2007. If it is blank then when I press the button it would loop through ADD the tasks for each row with a due date. If row 110 has a DATE I would get a popup indicating that the tasks are already in OUTLOOK and that they were added on whatever the date is in row 110... This is kind of the safeguard that I don't get the same group of tasks multiple times by pressing the button by mistake.

Please Login or Register  to view this content.

After all of the ROWS for a specific customer are processed update row 110 with the date that everything was processed.

I have included a picture of what my data looks like.

If I press the button on the first grouping of 4 I would get the popup telling me I already added them. If I pressed the button on the second grouping of 4 it would add an Outlook task called: Sample Customer - AP Training Scheduled with a DUE date of 10/10/2007. It would also put into H110 today's date so that if I push the button again it would not process.

Any help that anyone can provide would be greatly appreciated... I don't know much about VB programming.

Here are some additional pics...
Thanks all for your help

I was looking through these images... seems as if I only include the tasks area... on the previous image you will see that there is customer header data across the top and each grouping of 4 columns applies to the customer across the top.

I manage projects that I am working on using an Excel Workbook with many different worksheets. One of those worksheets is helps me keep track of TASKS that need to be done for each project.

I have attached an image of what the spreadsheet might look like...

The worksheet is designed such that in column A is a list of tasks moving across the worksheet will have each project with dates filling the a due column and a completed column. The top rows identify the project information.

What I would like to happen is to place a button or a link at the bottom of the DUE date column that when pressed would look through each row in same column as the button for a due date and if it finds one it would create a task in Outlook using the Project Name - Task Description as the Subject and the Due Date as the date, and would then move to the next row... ultimately processing every row in the button's column. To safeguard against processing the same data over and over it would also write the date the button was pressed in a cell below the button and when the button is pressed it checks to see if that cell has a date... if it does it provides a popup indicating that "This project's data has already been processed. To force a reprocessing and perhaps duplicating Outlook task you could delete the date in the cell below and then re-press the button."

So for example from the attached image:

Click the button in C13 it would check for a date in C14... if none were found, it would start processing at C5 and create a TASK in Outlook with a Due Date of 12/15/2007 and Subject of: Project Name - Task 1 (Concatenate C3 and A5) then move to the next row, and the next and the next processing each row that had a due date. The last thing it would do is write the date that the button was pressed into C14

I'm not familiar enough with VB to be able to code this. Can anyone help?

Is there a way to determine the column based on where the link or button is located/pressed... This would allow for only a single script instead of having to do it 40 or 50 times across the spreadsheet (once for each project)?

I am going to play around with this script later this evening... thanks for the effort I really appreciate it!

I have been reading in various forums that Shapes("buttonname").TopLeftColumn or something similar might be usable to determine where the button is...

I have also read that if I do it as a link that perhaps and ONCLICK event would work?

OK...

I think the framework for what I need is here... I have a couple of problems...

1. It is not looking at each of the DUE dates in the column defined by the variable C - It's as if it is not stepping through them... it just grabs the last one.

2. The Due date should be coming from the ROW that it is currently processing in the column defined by the variable C.

Anyone?

Please Login or Register  to view this content.

So here is what I have pieced together so far from looking at posts on here and other place on the Internet...Remember I'm not a coder so this is probably not the prettiest code you've ever seen. What I'm trying to do is to create just one routine that I can attach to buttons all the way across my worksheet. What I still need help with...

1. [Broken] Stepping through the range in the loops for some reason it is grabbing/processing the last entry in the range and only building a task for that one. So if in my range (defined in Set ChkRng) I have 30 items with due dates and I would expect it to create 30 tasks it is only creating 1 task.

2. [Addition but really needed] I need to have it check to see if their is a date below the button. If there is then it should just stop with a message indicating that the Outlook Tasks have already been created.

3. [Addition - a nice to have] I would like a notification when it finishes that XX tasks were added to Outlook.

Please Login or Register  to view this content.

I decided I needed to check if it was making it into the loops AND if it was stepping through each of the rows in my range so I put in message boxes so I could follow it along the way... (I know for you programmers out there, there is probably a much better way to do this.) What I have found is that each instance of the While has a task with the appropriate information but it doesn't appear as if it releases the task so that on the next iteration it creates a new one... Instead what it does is takes the task that was created on the prevoius iteration of the while loop and UPDATES it with the information from the current iteration of the while loop.

So my question now is HOW do I get it to start a NEW Outlook task after it has created and .Saved One?

Here is my code complete with all of my message boxes and comments, etc...

Please Login or Register  to view this content.

Instead of Initializing the task outside of the FOR LOOP I put it as the first line of the For loop prior to my While... This makes so much sense... I think I just needed to sleep on it awhile.

This is the line I moved into the first line of my FOR LOOP in the Code above.

Set olTsk = olApp.CreateItem(olTaskItem)