With VBA Excel list generate in Outlook colleague multiple Taks automated

[JMW1975]

Dear VBA Pro's,

I need some assistance, I made some progress but am very much stuck at the moment. Hopefully you'all VBA pro's could help me along a bit.

My goal with this litte VBA project, is from a list in Excel with projectdates, company names, action data to generate multiple tasks in a colleagues Outlook tasklist.

The Code below works fine for 1 item (excel line)

Omdat het een lijst is met diverse regels heb ik een Loop aangemaakt (met behulp van wat gegoogle) om de lijst te door lopen. In principe werkt de Loop, alleen niet op de manier zoals bedoeld.

Because the Excel list has multiple lines input, i constructed a Loop (with some Google help) to run through the excel list. Basically the Loop works but not as it was supposed to.

Some explanation is required it guess:

With the action .Display (to see what happens when running the VBA) is activated a new Task Windows in Outlook is opened and is being filled with the data per (excel) line perfectly.
The only thing is The Loop runs within this opened Task Window and all previous input (excel lines) is overwritten and only at the last item (excel line) the loop stops and shows the last input.
The mailaddresse line is filled with multiple mail addresses (mostly the same mail address).

Het Taakwindow sluit niet en er komt geen verdere (fout)melding.
The Taks Windows doesn't close and no further (error) report is presented.

It supposed to make 1 task in the colleagues Outlook for every excel line.

When the action .Send (as it is supposed to) is activated is runs through the program but presents the following error "Error -2147467259 (80004005) Can't assign " company x" Because you are not the owner of the task. Translated from Dutch so some meaning could be lost in translation. (Original Error report "Fout -2147467259 (80004005) U kunt de taak " Bedrijf x" niet toewijzen, omdat u niet de eigenaar van de taak bent.)

The Action .assign is highlighted as where the error occurred. The Task form the first company (in excel line) IS being generated in the Colleagues Outlook Task list. Although this colleague needs to accept the Task manually.

The Task is supposed to be entered Silently in the Colleagues Outlook Tasklist, without any action from that colleague, but at the moment I have no idea to accomplish this.

I hope some of you can help me with this.
Thanx in advance for any assistance


This is my VBA Code so far:

Sub SetTaskItemTask()
    Dim myTaskAssignment As TaskItem
    Sheets("Offerte overzicht").Select
    On Error GoTo Err_Execute
    
    Dim i As Long
    On Error Resume Next
    
    Set myTaskAssignment = CreateObject("Outlook.Application").CreateItem(olTaskItem)
     
    On Error GoTo 0
    
    i = 2
    Do Until Trim(Cells(i, 1).Value) = ""
    
    With myTaskAssignment
        .Assign
        .Recipients.Add Name:="colleague@domain.com"
        .Subject = Cells(i, 5)
            If Cells(i, 15).Value = "" Then
            .StartDate = Cells(i, 9)
            Else: .StartDate = Cells(i, 15)
            End If
            If Cells(i, 15).Value = "" Then
            .DueDate = .StartDate + 90
            Else: .DueDate = .StartDate + Cells(i, 16)
            End If
        '.DueDate = .StartDate + Cells(i, 16)
        .ReminderTime = .DueDate - 1
        .Body = Cells(i, 11)
        '.Display
        .Send
    End With
    
    i = i + 1
    Loop
    
    Exit Sub


Err_Execute:
MsgBox "An error occured! - Exporting Excel items to Outlook Tasks."


End Sub

This questions is also posted under "Questions in Other Languages"

and on helpmij.nl