Assign Outlook Task Item loop (1 Viewer)

kit_sune · May 29, 2015

Hello all,

I'm struggling to understand how to accomplish this. At first I tried taking what I learned from two different forum posts and make it work, but I wasn't really sure what I was doing, and couldn't get anywhere. So I tried another aproach, and still wasn't able to get this working.

Basically, I have a query that formats data into a list of personnel who are coming due for refresher training. The query is formatted to provide me all the details that I need to create an outlook task, and assign it to the person. I tried creating a button on my form to create a record set of that query, and then create / assign a task to each person based on the values from the recordset.

Please take a look at what I have and let me know where I'm going wrong...

Code:

Private Sub SendTasks_Button_Click()

'Create the recordset and set it to the appropriate query
Dim rst As DAO.Database
Dim rstData As DAO.Recordset

Set rst = CurrentDb
Set rstData = rst.OpenRecordset("SendTasks_qry")

For Each record In rstData

    'Create a new Task item to work with
    Dim myOlApp As New Outlook.Application
    Dim myItem As Outlook.TaskItem
    Dim myDelegate As Outlook.Recipient
    
    Set myItem = myOlApp.CreateItem(olTaskItem)
    Set myDelegate = myItem.Recipients.Add(rstData!EMAIL)
    
    myItem.Assign
    
    myDelegate.Resolve
    
    If myDelegate.Resolved Then
        
        myItem.Subject = rstData!Subject
        myItem.DueDate = rstData!DueDate
        myItem.Subject = rstData!Subject
        myItem.Importance = rstData!Importance
        myItem.Display
        myItem.Send
        
    End If
Next record

End Sub

Basically, the compiler stops at "Dim myOlApp As New Outlook.Application" with the error: "Compile error: User-defined type not defined"

Thanks in advance! (And yes!!! I have read the MS Support article, things just haven't clicked yet for me)

vbaInet · May 30, 2015

I've never needed to create a task in Outlook before but it should be fairly straightforward.

First of all you need add a reference to the Outlook Object:
In the VBA Editor click Tools > References > look for and select Microsoft Outlook X.0 Object Libray > hit OK
... where X will correspond to your version of Outlook.

To clarify, do you want to send them a task item?

kit_sune · May 30, 2015

vbaInet said:
To clarify, do you want to send them a task item?

Yes, the idea is that it should set up the task item and then assign it to one of my personnel to complete so I can be notified when they mark it as completed. It would do this for each record that the query produced.

Thanks for your time.

vbaInet · May 30, 2015

Have you added the reference?

kit_sune · May 30, 2015

No, sorry. I went home for the day, but the suggestion makes sense. I'll implement it at the first chance I get. Thank you!

vbaInet · May 30, 2015

I'll see if your code needs some tidying up and post back.

vbaInet · May 30, 2015

Here you go.

For the parts I commented out:
1. rst!Name - it would be useful to have a name next to the subject so that you can identify who the task is assigned to by just looking at the tasks pane without the need of opening it.
2. Save and Display - take it out if you want to manually review each task before it's sent

Code:

Public Sub SendTask()
    Dim olApp        As Outlook.Application
    Dim olTask       As Outlook.TaskItem
    Dim olRecipient  As Outlook.Recipient
    Dim dbs          As DAO.Database
    Dim rst          As DAO.Recordset
    Dim intCount     As Long
    
    Set olApp = New Outlook.Application
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SendTasks_qry", dbOpenSnapshot)
    
    Do While Not rst.EOF
        ' Create new task item
        Set olTask = olApp.CreateItem(olTaskItem)
        
        ' Add the delegate
        Set olRecipient = olTask.Recipients.Add(rst!EMAIL)
        
        ' Resolve the delegate and send
        With olRecipient
            .Resolve
            If .Resolved Then
                With olTask
                    .Assign
                    .Subject = rst!Subject ' & ": " & rs!Name
                    .DueDate = rst!DueDate
                    .Importance = rst!Importance
                    '.Save
                    '.Display True
                    .Send
                End With
            Else
                intCount = intCount + 1
            End If
        End With
        rst.MoveNext
    Loop
        
    ' Notify
    If intCount = 0 Then
        MsgBox "All tasks were sent successfully!" & _
                vbNewLine & vbNewLine & _
                "Sent to " & intCount & " recipient(s)", _
                vbInformation + vbOKOnly, _
                "Tasks sent"

    ElseIf intCount <> rst.RecordCount Then
        MsgBox "Some tasks were not sent" & _
                vbNewLine & vbNewLine & _
                "Couldn't resolve " & rst.RecordCount - intCount & " recipient(s)", _
                vbExclamation + vbOKOnly, _
                "Tasks not sent"

    Else
        MsgBox "Tasks not sent" & _
                vbNewLine & vbNewLine & _
                "Couldn't resolve any recipient", _
                vbExclamation + vbOKOnly, _
                "Tasks not sent"
    End If
    
    ' Cleanup
    Set olTask = Nothing
    Set olApp = Nothing
    Set rst = Nothing
    Set dbs = Nothing
End Sub

kit_sune · Jun 1, 2015

I tried to sneak back to work over the weekend but my wife didn’t like that idea…
So this morning I tried your code and enabled the Outlook object reference. Enabling the reference did allow the compiler to work past the point that it was stuck at. But now it’s giving me another error that I’m looking into.
The compiler stops at “Set olRecipient = olTask.Recipients.Add(rst!EMAIL)”
With the message: “Application-defined or object-defined error”
It recognizes the email (when I hover over the rst!EMAIL it has my email address in there, which is what I wanted).

One thing I was curious about – in the initialized variables there was “Dim olRecipient As Outlook.Recipient” I considered changing this to “As Outlook.Recipients” to see if that would work, but it didn’t help. I wasn’t sure if that would do anything or not.

I’m looking into the issue but I wanted to post an update. I have to leave shortly for a medical appointment so it will be a little while before I get back to this, sadly.

I have more questions, but I’ll wait to ask them for now – I’m really trying to learn this stuff.

Just for reference to make sure I didn't do anything wrong:

Code:

 Private Sub SendTasks_Button_Click()
     Dim olApp        As Outlook.Application
    Dim olTask       As Outlook.TaskItem
    Dim olRecipient  As Outlook.Recipients
    Dim dbs          As DAO.Database
    Dim rst          As DAO.Recordset
    Dim intCount     As Long
    
    Set olApp = New Outlook.Application
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SendTasks_qry", dbOpenSnapshot)
    
    Do While Not rst.EOF
        ' Create new task item
        Set olTask = olApp.CreateItem(olTaskItem)
        
        ' Add the delegate
        Set olRecipient = olTask.Recipients.Add(rst!EMAIL)
        
        ' Resolve the delegate and send
        With olRecipient
            .Resolve
            If .Resolved Then
                With olTask
                    .Assign
                    .Subject = rst!Subject & ": " & rst!NAME
                    .DueDate = rst!DueDate
                    .Importance = rst!Importance
                    '.Save
                    '.Display True
                    .Send
                End With
            Else
                intCount = intCount + 1
            End If
        End With
        rst.MoveNext
    Loop
        
    ' Notify
    If intCount = 0 Then
        MsgBox "All tasks were sent successfully!" & _
                vbNewLine & vbNewLine & _
                "Sent to " & intCount & " recipient(s)", _
                vbInformation + vbOKOnly, _
                "Tasks sent"
     ElseIf intCount <> rst.RecordCount Then
        MsgBox "Some tasks were not sent" & _
                vbNewLine & vbNewLine & _
                "Couldn't resolve " & rst.RecordCount - intCount & " recipient(s)", _
                vbExclamation + vbOKOnly, _
                "Tasks not sent"
     Else
        MsgBox "Tasks not sent" & _
                vbNewLine & vbNewLine & _
                "Couldn't resolve any recipient", _
                vbExclamation + vbOKOnly, _
                "Tasks not sent"
    End If
    
    ' Cleanup
    Set olTask = Nothing
    Set olApp = Nothing
    Set rst = Nothing
    Set dbs = Nothing
 End Sub

vbaInet · Jun 1, 2015

I can't blame your wife for intervening

That's weird but don't change the code. It's definitely Recipient not Recipients.

I need more information:
1. At what cycle does it error? I.e. does it even assign at least one task or it fails at the very first?
2. Is Outlook open when you run the code? Keep it open.

Let's add/change a few lines.

Add a String variable:

Code:

    Dim strRecipient As String

Replace this block:

Code:

        ' Add the delegate
        strRecipient = rst!EMAIL
        olTask.Recipients.Add strRecipient
        Set olRecipient = olTask.Recipients(1)

Everything else stays the same.

spikepl · Jun 1, 2015

This is one of the instances where .Value has its rights, because Outlook gets confused between value and pointer. This also applies to atachments and other Outlook bits.

So

olTask.Recipients.Add rst!EMAIL.Value

should do the job just fine

vbaInet · Jun 1, 2015

That's just weird spikepl! But hopefully that sorts it out otherwise breaking it down should too.

I'm always explicit especially with .Value but in this case it was just a copy and paste of the poster's code.

kit_sune · Jun 1, 2015

Thanks, I set the "Recipients" back to a singular Recipient.

vbaInet said:
1. At what cycle does it error? I.e. does it even assign at least one task or it fails at the very first?

I enabled the Save/Display lines to see if I would get at least one confirmation, but I didn't, so it appears that it doesn't cycle even once.

vbaInet said:
2. Is Outlook open when you run the code? Keep it open.

Yes, Outlook is open. I have also just switched it over to "strRecipient = rst!EMAIL.Value" but it's the same issue. As soon as it reaches the next line it gives me that error.
"olTask.Recipients.Add strRecipient" "Application-defined or object-defined error"

I've rechecked to make sure that the Microsoft Outlook 15.0 Object Library was enabled, and it is (Just to be 100% sure).

Is there anything I should be doing inside of Outlook?

Or, perhaps, should I format the email as a link instead of a string? I have it as a short text string inside the personnel table that the query references. I looked at the email just to be sure that it was correct, and it is.

So many things to consider...

vbaInet · Jun 1, 2015

Did you try what I mentioned in post #9?

The email format as a String is fine.

kit_sune · Jun 1, 2015

vbaInet said:
Did you try what I mentioned in post #9?

The email format as a String is fine.

Yes I did - I added the variable initialization, and replaced the assignment code with what you suggested. It's strange, it feels like it doesn't know what to do with the variable. I was going to update my above post, but figured I'd just do it here since that was ages ago.

Code:

Private Sub SendTasks_Button_Click()
     Dim olApp        As Outlook.Application
    Dim olTask       As Outlook.TaskItem
    Dim olRecipient  As Outlook.Recipient
    Dim dbs          As DAO.Database
    Dim rst          As DAO.Recordset
    Dim intCount     As Long
    Dim strRecipient As String
    
    Set olApp = New Outlook.Application
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SendTasks_qry", dbOpenSnapshot)
    
    Do While Not rst.EOF
        ' Create new task item
        Set olTask = olApp.CreateItem(olTaskItem)
        
        ' Add the delegate
        strRecipient = rst!EMAIL.Value
        olTask.Recipients.Add strRecipient   ' This is where it stops
        Set olRecipient = olTask.Recipients(1)
        
        ' Resolve the delegate and send
        With olRecipient
            .Resolve
            If .Resolved Then
                With olTask
                    .Assign
                    .Subject = rst!Subject & ": " & rst!NAME
                    .DueDate = rst!DueDate
                    .Importance = rst!Importance
                    .Save
                    .Display True
                    .Send
                End With
            Else
                intCount = intCount + 1
            End If
        End With
        rst.MoveNext
    Loop
        
    ' Notify
    If intCount = 0 Then
        MsgBox "All tasks were sent successfully!" & _
                vbNewLine & vbNewLine & _
                "Sent to " & intCount & " recipient(s)", _
                vbInformation + vbOKOnly, _
                "Tasks sent"
     ElseIf intCount <> rst.RecordCount Then
        MsgBox "Some tasks were not sent" & _
                vbNewLine & vbNewLine & _
                "Couldn't resolve " & rst.RecordCount - intCount & " recipient(s)", _
                vbExclamation + vbOKOnly, _
                "Tasks not sent"
     Else
        MsgBox "Tasks not sent" & _
                vbNewLine & vbNewLine & _
                "Couldn't resolve any recipient", _
                vbExclamation + vbOKOnly, _
                "Tasks not sent"
    End If
    
    ' Cleanup
    Set olTask = Nothing
    Set olApp = Nothing
    Set rst = Nothing
    Set dbs = Nothing
  
 End Sub

vbaInet · Jun 1, 2015

Again, that's a strange occurrence. I will need to look into that because it should not fail.

Two things:

Can you manually create tasks in Outlook without any problems?

Instead of using an e-mail use the name of the person, manually typed in like this:

Code:

strRecipient = "Joe Bloggs"

... don't use the NAME field from the recordset, just type in a valid name that's in your Outlook address book. Perhaps your wife's

kit_sune · Jun 1, 2015

vbaInet said:
Can you manually create tasks in Outlook without any problems?

Yes, I've created my own training tasks previously (which is what gave me this idea to begin with), and I've also created / assigned tasks to some of my guys.

vbaInet said:
Instead of using an e-mail use the name of the person, manually typed in like this:

Code:

strRecipient = "Joe Bloggs"

... don't use the NAME field from the recordset, just type in a valid name that's in your Outlook address book. Perhaps your wife's

I created a contact for myself just now and tested that out. I tested out various versions (Before and after I created the contact) "First Last", "Last, First", also with Middle initial added, but the only difference was that it delayed a little longer than usual before erroring out.

vbaInet · Jun 1, 2015

That's interesting! We'll get to the bottom of this.

Before I look any further into this, add these lines:

Code:

        ' Create new task item
        Set olTask = olApp.CreateItem(olTaskItem)
[COLOR="Blue"]        olTask.Display
        DoEvents[/COLOR]

... and tell me the effect.

kit_sune · Jun 1, 2015

When I added those lines it was able to display the task item as "Untitled - Task" before erroring out at the usual location. So this confirms that it's not the Outlook object reference. We use a Global Address List to manage our personnel connections, but if at all possible I would rather just state the email address because I only need to manage a small group of people, and not everyone on the list. I already collect the email address as part of my set up...

I've been googling around to see if I can't stumble upon something insightful, but so far what I've noticed is that I'm not the only person to have this issue arise.

spikepl · Jun 1, 2015

Try .Assign before doing recipients

vbaInet · Jun 1, 2015

Did any of your searches result in any possible solutions?

I think I know what's happening.
Add these lines and tell me the results (and I'm obviously not expecting you to tell me your name "if" the code runs):

Code:

    Set olApp = New Outlook.Application
[COLOR="Blue"]    Msgbox "User: " & olapp.Session.CurrentUser
    Msgbox "Accounts count: " & olapp.Session.Accounts.Count
    Msgbox "Addresslist count: " & olapp.Session.AddressLists.Count
[/COLOR]

Assign Outlook Task Item loop (1 Viewer)

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Eledittingent Beliped

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Eledittingent Beliped

AWF VIP

Similar threads

Users who are viewing this thread