Auto Create new Task upon Current Task completion

Status
Not open for further replies.

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
I am wanting to create some vba code that will automatically create a new task when an existing task is completed. I need it to auto-run (via the VbaProject.OTM) so that when tasks of certain categories are marked as complete a new task is created using the same subject, a different category, and using the completion date as the start date of the new task with a due date 10 days later.

Is this at all possible. I have tried using snippets of other code and modifying it to suit but so far seem to be going absolutely nowhere.
 

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
Thanks Michael,
That's actually the code that I was trying to use as a starting point, but I couldn't seem to get it to work as is much less modify it to do what I want. I'll keep researching and trying.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Basically, you need to replace the lines that set the category and save the task with the code to create a new task.

Code:
If Task.Status = olTaskComplete Then
      If LCase$(Task.Categories) <> LCase$(CategoryName) Then
        Task.Categories = CategoryName
        Task.Save
      End If
    End If

It's an Application_Startup macro, so it goes in ThisOutlookSession and for testing changes without restarting Outlook, click in Application_Startup and click Run. Make sure macro security is set to low until you are finished with it, then sign it with selfcert.
 

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
I am still having problems getting this to work.

Here is my code:

Code:
' <DieseOutlookSitzung>

Private Sub Application_Startup()
  Dim Ns As Outlook.NameSpace

  Set Ns = Application.GetNamespace("MAPI")
  Set Items = Ns.GetDefaultFolder(olFolderTasks).Items
End Sub

Private Sub Items_ItemChange(ByVal Item As Object)
  On Error Resume Next
  Dim Task As Outlook.TaskItem
  Dim DueDate As String
  Const CategoryName As String = "Assessment"

  If TypeOf Item Is Outlook.TaskItem Then
    Set Task = Item
    Task.Save
    If Task.Status = olTaskComplete Then
      If LCase$(Task.Categories) <> LCase$(CategoryName) Then
        Application.CreateItem (olTaskItem)
        Task.StartDate = Item.Start
        Task.DueDate = Item.DueDate + 1
        Task.Status = olTaskInProgress
        Task.Categories = "Assessment"
        Task.Importance = olImportanceHigh   'can be olImportanceNormal, olImportanceHigh or olImportanceLow
        Task.Subject = Item.Subject
        Task.Body = Item.Body
        Task.Save ' use .Send to send it instead
      End If
    End If
  End If
End Sub
' </DieseOutlookSitzung>



Unfortunately this still just changes the original task, rather than retaining and marking it as complete and then creating a new task with new properties as specified.
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Change this line
Application.CreateItem (olTaskItem)
to
Set Task=Application.CreateItem (olTaskItem)

The "Item" variable then still points to the edited item, while the "Task" variable will point to the new item returned by the CreateItem function.
 

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
Thanks Michael & Diane,

I now almost have the code working as hoped. Here is my updated code:

Code:
' <DieseOutlookSitzung>

Private Sub Application_Startup()
  Dim Ns As Outlook.NameSpace

  Set Ns = Application.GetNamespace("MAPI")
  Set Items = Ns.GetDefaultFolder(olFolderTasks).Items
End Sub

Private Sub Items_ItemChange(ByVal Item As Object)
  On Error Resume Next
  Dim Task As Outlook.TaskItem
  Dim DueDate As String
  Const CategoryName As String = "Assessment"

  If TypeOf Item Is Outlook.TaskItem Then
    'Set Task = Item
    'Task.Save
    If Task.Status = olTaskComplete Then
      If LCase$(Task.Categories) <> LCase$(CategoryName) Then
        Set Task = Application.CreateItem(olTaskItem)
        Task.StartDate = Item.DateCompleted
        Task.DueDate = Item.DateCompleted + 10
        Task.Status = olTaskInProgress
        Task.Categories = "Assessment"
        Task.Importance = olImportanceHigh   'can be olImportanceNormal, olImportanceHigh or olImportanceLow
        Task.Subject = Item.Subject
        Task.Body = Item.Body
        Task.Save ' use .Send .Save .Display
      End If
    End If
  End If
End Sub
' </DieseOutlookSitzung>

The only problem now is it is creating three new tasks. So looks like I am continuing with more troubleshooting.
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
First, whenever a code doesn't do what you expect, remove the On Error Resume Next as it instructs to ignore errors, and that is nonsense if you don't know what the code does.

It'll then tell you that here
If Task.Status = olTaskComplete Then
the Task variable is nothing. That's because you commented this line out:
Set Task = Item
 

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
Oops. Thanks for the tip about the "On Error Resume" line. Very handy.

I had been commenting various lines out to see how it changed the outcome in an attempt to fix the issue with it creating three new tasks. I had obviously forgot to change it back before posting the code.

I have checked and the "Set Task = Item" line has definatley been made active and there are no error messages being advised. The code is somehow still generating three new identical tasks when an existing task is marked a complete.
 

Forum Admin

Senior Member
I'm using this and getting just one new task - bu, if you are copying it as an identical task, why not just using the regenerate task feature?

Code:
Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()
  Dim Ns As Outlook.NameSpace

  Set Ns = Application.GetNamespace("MAPI")
  Set Items = Ns.GetDefaultFolder(olFolderTasks).Items
End Sub

Private Sub Items_ItemChange(ByVal Item As Object)
  'On Error Resume Next
  Dim Task As Outlook.TaskItem
  Dim DueDate As String
  Const CategoryName As String = "Assessment"

  If TypeOf Item Is Outlook.TaskItem Then
    'Set Task = Item
    'Task.Save
    If Item.Status = olTaskComplete Then
     ' If LCase$(Task.Categories) <> LCase$(CategoryName) Then
        Set Task = Application.CreateItem(olTaskItem)
        Task.StartDate = Item.DateCompleted
        Task.DueDate = Item.DateCompleted + 10
        Task.Status = olTaskInProgress
        Task.Categories = "Assessment"
        Task.Importance = olImportanceHigh   'can be olImportanceNormal, olImportanceHigh or olImportanceLow
        Task.Subject = Item.Subject
        Task.Body = Item.Body
        Task.Save ' use .Send .Save .Display
     ' End If
    End If
  End If
 

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
Thanks for testing. It's not an identical task that I am regenerating. It uses the date completed as the start date and sets a due date 10 days later. There will also be other changes and filters that will be made but I want to get the basic concept working first before I proceed further.

Further testing shows that when a new task is created and marked complete immediately then I do not get duplicate copies. Only when my existing task seems to be already in the task list with a different status does it seem to create duplicates when marked read. Interestingly I have had a some tasks only regenerate two copies, others will generate three. Could it have something to do with the fact that I am using outlook on exchange with my task list accessible on multiple computers?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Hmm. I'm getting 3 with an exchange account too. I'm guessing it's due to cached mode. The updated item syncs and that triggers the macro again for some unknown reason.

Add another if categories - check for Completed then at the end, add the completed category to the original task.

Code:
If Item.Status = olTaskComplete Then
 If Item.Status = olTaskComplete And InStr(1, Item.Categories, "Complete") = 0 Then

      If LCase$(Item.Categories) <> LCase$(CategoryName) Then
        ' do your stuff
      End If

        Item.Categories = Item.Categories & ";Complete"
        Item.Save

    End If
    End If
 

Michael Bauer

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
If you assign more categories, this
Code:
If LCase$(Item.Categories) <> LCase$(CategoryName) Then
doesn't work. Instead you could search with Instr, too.

If the issue is due to syncing, that is a procedure that calls itself, I'd try this approach first:
Code:
sub bla()
  on error goto err_handler
  static busy as boolean
  if busy=false then busy=true else exit sub
  'your stuff
  err_handler:
  busy=false
  if err then msgbox err.description
end sub
 

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
Thanks Diane & Michael,

You are a wealth of information and have been a great help, both for this project and general learning.

Diane, your solution seems to be working so far. I will proceed with my other adjustments.

Michael, I will keep your response in mind as there may be more categories involved down the track and I may need to implement this to combat any resulting issues.
 

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
Hmmmm. No I spoke too soon. There appears to have been a delay but between the creation of the first task but an extra two eventually appeared.
 

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
I haven't been able to figure out exactly where to put "busy flag" code into mine. I either get an error when i run it or it doesn't do anything.
 

sjbtax

New Member
Outlook version
Outlook 2007
Email Account
My current code still looks like this:

Code:
Private Sub Application_Startup()
  Dim Ns As Outlook.NameSpace

  Set Ns = Application.GetNamespace("MAPI")
  Set Items = Ns.GetDefaultFolder(olFolderTasks).Items
End Sub



Private Sub Items_ItemChange(ByVal Item As Object)
  'On Error Resume Next
  Dim Task As Outlook.TaskItem
  Dim DueDate As String
  Const CategoryName As String = "Assessment"

  If TypeOf Item Is Outlook.TaskItem Then
    Set Task = Item
    'Task.Save
    If Task.Status = olTaskComplete Then
      If LCase$(Task.Categories) <> LCase$(CategoryName) Then
        Set Task = Application.CreateItem(olTaskItem)
        Task.StartDate = Item.DateCompleted
        Task.DueDate = Item.DateCompleted + 10
        Task.Status = olTaskInProgress
        Task.Categories = "Assessment"
        Task.Importance = olImportanceHigh   'can be olImportanceNormal, olImportanceHigh or olImportanceLow
        Task.Subject = Item.Subject
        Task.Body = Item.Body
        Item.Categories = Item.Categories & ";Complete"
        Task.Save ' use .Send .Save .Display
      End If
    End If
  End If
End Sub

I am still trying to figure out exactly where to place the "busy flag" code as everywhere I place it seems to result in an error.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
C Visual Basic auto create task from email including attachments Using Outlook 9
D create auto number field in task form Outlook VBA and Custom Forms 4
rc4524 Create auto follow-up reminder email for already sent messages Outlook VBA and Custom Forms 1
R Auto-create receipt from email and forward to payer Using Outlook 3
H Create/Apply auto formatting rules by VB? Outlook VBA and Custom Forms 2
G Macro: Create New Message and Auto populate To Field Outlook VBA and Custom Forms 5
S Create Auto eMail with Web table Content Outlook VBA and Custom Forms 1
S Outlook Macro to send auto acknowledge mail only to new mails received to a specific shared inbox Outlook VBA and Custom Forms 0
V Auto-Submitted: auto-replied in header Using Outlook 0
R Auto display of new email does not work on non-default account Outlook VBA and Custom Forms 0
B Outlook 2016 Auto-archive creates new folder Using Outlook 4
J Edit auto-complete list in Outlook 2016+/365? Using Outlook 0
P Auto assign shared mailbox Outlook VBA and Custom Forms 1
M Outlook 2010 Problem with OutLook 2010 32 bit, after Windows Auto Update Using Outlook 3
P [SOLVED] Auto remove [EXTERNAL] from subject Using Outlook 14
Z Add text to auto-forwarded e-mail Outlook VBA and Custom Forms 4
N Disable Auto Read Receipts sent after using Advanced Find Using Outlook 4
Q Prompt button to auto turn on Out of Office Outlook VBA and Custom Forms 3
P Auto Insert Current Date or Time into Email Subject Outlook VBA and Custom Forms 2
S Messages moved / deleted by auto-archive are not synchronized to exchange Exchange Server Administration 8
B Outlook 2010 is Auto Purging when not configured for that Using Outlook 1
M VBA to auto forward message with new subject and body text Outlook VBA and Custom Forms 8
A Auto Accept Meetings from the General Calendar Using Outlook 3
R auto send email when meeting closes from a shared calendar only Outlook VBA and Custom Forms 2
S auto-mapping mailboxes in outlook impacting an ost file? Exchange Server Administration 2
M Auto expand Distribution List Before Sending Email Outlook VBA and Custom Forms 1
M Auto-export mail to Excel Outlook VBA and Custom Forms 2
Ms_Cynic Auto-pasting email content in calendar appt? Using Outlook 2
R How Do I insert images in and Auto Reply Using Outlook 3
S Received mail as part of DL, need to auto-CC the same when replying Outlook VBA and Custom Forms 5
T Have Outlook 2016 suggest email address auto complete entries directly from the user's contacts list Using Outlook 10
T Have Outlook 2016 suggest email address auto complete entries directly from the user's contacts list Using Outlook 0
P Auto scroll to specific folder in Folder Pane Outlook VBA and Custom Forms 3
C Auto categorize duplicate subjects Outlook VBA and Custom Forms 11
N Auto-complete - block select emails Using Outlook 3
C Auto save outlook attachments when email is received Outlook VBA and Custom Forms 1
J HELP- Rule to auto strip prepend from external emails Using Outlook 0
S BCM Auto Backup Data and Customizations BCM (Business Contact Manager) 6
G Auto accept meeting request for non primary account Outlook VBA and Custom Forms 1
J Outlook Rules - Changing auto-submit address in multiple rules, according to rule name Outlook VBA and Custom Forms 0
E Outlook Form - Voting Responses Not Auto Processing If Form Contains Any Code Outlook VBA and Custom Forms 0
J Auto Forward - Include Attachment and change Subject depending on original sender Outlook VBA and Custom Forms 3
K Extract email address from body and auto-reply outlook Using Outlook 1
S Auto move search results to folder Outlook VBA and Custom Forms 0
E Outlook 2010 disable date auto-complete Using Outlook 2
C Auto subject,name,email,deferred Using Outlook 2
ashcosta2 Auto Reply rule based on speficied time Outlook VBA and Custom Forms 0
B Auto Preview Attachment in Inspector Reading Pane Outlook VBA and Custom Forms 1
Z Auto Forward Using Outlook 4
M can anyone recommend an alternative to DS auto-followup? Using Outlook 2

Similar threads

Top