Assign Outlook Task Item loop (1 Viewer)

kit_sune

Registered User.
Local time
Today, 10:49
Joined
Aug 19, 2013
Messages
88
I was hoping to be able to allow anyone in a supervisory role of my office (There's currently 5, but will be 3 soon) to be able to use this feature, and I'm afraid that if it needs to be plugged into Outlook in any way, that they, or anyone in the future who needs to add this to Outlook, would have a hard time doing that.

If I'm not here to help them, or the future employees down the road, then it would end up being discarded. I would like to prevent that if possible. I'm the only one in the office who is remotely interested in learning this.

I would also like to add that, while I struggled in the past with some of these concepts, working with you on this one in particular has helped to see in action how things work together like this. Like I said previously, I do have a few questions for you, but I'll wait for now to ask them since they're not relevant to the issue.

Thank you so much for taking your time to help me with this ... very restrictive issue. I promise, I'm not trying to make this harder at every step.
 
Last edited:

vbaInet

AWF VIP
Local time
Today, 17:49
Joined
Jan 22, 2010
Messages
26,374
I hope you haven't given your workers high hopes of this functionality yet ;)

If the original code can be fired from Outlook then your users can run it from Access. My suspect is that the security feature just won't let it pass.

Anyway, following your suggestion, let's see if this works. If it does, your onto a winner because I know what to do with it:
Code:
Public Sub SendTasks()
    Dim olApp        As Outlook.Application
    Dim olNSpace     As Outlook.Namespace
    Dim olTask       As Outlook.TaskItem
    Dim olRecipients As Outlook.Recipients
    Dim olRecipient  As Outlook.Recipient
    
    Set olApp = New Outlook.Application
    Set olNSpace = olApp.GetNamespace("MAPI")
    
    With olNSpace.GetSelectNamesDialog
        .AllowMultipleSelection = True
        .ForceResolution = True
        .SetDefaultDisplayMode olDefaultTask
        .Display
        Set olRecipients = .Recipients
    End With
    
    ' Create new task item
    Set olTask = olApp.CreateItem(olTaskItem)
    
    ' Add the delegate
    Set olRecipient = olRecipients(1)
    
    MsgBox olRecipients.Count
End Sub
 

kit_sune

Registered User.
Local time
Today, 10:49
Joined
Aug 19, 2013
Messages
88
I gave that a try, but this time it produced the error at: "With olNSpace.GetSelectNamesDialog"

Something that I noticed when playing around with the previous code was that the task that would display didn't have an option to send to a delegate - I had to click the assign button first before I would be able to add someone to the "To" field.
 

vbaInet

AWF VIP
Local time
Today, 17:49
Joined
Jan 22, 2010
Messages
26,374
I gave that a try, but this time it produced the error at: "With olNSpace.GetSelectNamesDialog"
Again, the security policy set by your company is preventing access to your recipients that's why it stops at that line. There's nothing else you can do besides trying the options I gave you.

There's no To field in the Task item object. The way I wrote it from the start is the correct way.
 

kit_sune

Registered User.
Local time
Today, 10:49
Joined
Aug 19, 2013
Messages
88
Do you think maybe we need to put the ".Assign" first, to allow a delegate to be put into the task? I Checked through some of our previous work and found that "Assign" was put after the delegate / resolve lines. When I started to think about this I looked into the .Assign method and found that in their example it has the Assign come before the delegate / resolve lines.
This is the reference:
https://msdn.microsoft.com/en-us/library/office/ff869880.aspx
 

vbaInet

AWF VIP
Local time
Today, 17:49
Joined
Jan 22, 2010
Messages
26,374
If that would work I would have done it. I've already mentioned what the root cause is.
 

kit_sune

Registered User.
Local time
Today, 10:49
Joined
Aug 19, 2013
Messages
88
I understand. I think I am just grasping at straws because I really don't want to have to go through Outlook.

I would like to learn more about how you would have Outlook / Access talk to each other. Maybe it wouldn't be as bad as I'm anticipating!
 

kit_sune

Registered User.
Local time
Today, 10:49
Joined
Aug 19, 2013
Messages
88
Just for the sake of trying to contribute something...
I played around a bit and I have something that works. I would have to manually add the delegate myself for each training task that is required, which isn't ideal, but at this point, not a dealbreaker... I can work with that.

Code:
 Private Sub SendTasks_Button_Click()
    Dim olApp        As Outlook.Application
    Dim olNSpace     As Outlook.NameSpace
    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 olNSpace = olApp.GetNamespace("MAPI")
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SendTasks_qry", dbOpenSnapshot)
    
    Do While Not rst.EOF
        ' Create new task item
        Set olTask = olNSpace.Application.CreateItem(olTaskItem)
        
        With olTask
            .Assign
            .Subject = rst!Subject & ": " & rst!NAME
            .DueDate = rst!DueDate
            .Importance = rst!Importance
            .Body = rst!EMAIL & " - " & rst!Body
            .Save
            .Display True  ' At this point I can manually add the delegate, and press send.
            '.Send         - I had to comment this out, since the task gets sent during the Display method...
        End With
        rst.MoveNext
    Loop
    
End Sub
 

vbaInet

AWF VIP
Local time
Today, 17:49
Joined
Jan 22, 2010
Messages
26,374
That's acceptable, but you have to do this for each recipient and manually select them too. It kind of defeats the purpose of automating the task though. You can run any code as long as it doesn't attempt to fetch your Outlook recipients.

There's a workaround using SendKeys and the Clipboard. You could look into that.

And no, getting a function to run from either Access or Outlook is not as bad as you envisage.
 

kit_sune

Registered User.
Local time
Today, 10:49
Joined
Aug 19, 2013
Messages
88
There's a workaround using SendKeys and the Clipboard. You could look into that.

The only reference I could find for SendKeys in this context was here:
http://www.mrexcel.com/forum/excel-questions/703891-recipients-add-method-causing-error-287-a.html

I attempted to add sendkeys to this but the problem that I ran into was how, even with setting it's delay to "True", the function would only be able to add in the first few characters of the email address before it began on the next task... then it would continue adding in the rest of the email address into the next task. If there were a lot of tasks to send (around 10) then it would sometimes only get about 3-5 characters before moving on to the next task... and then when the last task was displayed, it would spam the remainder of the email adresses there.

You mentioned using the clipboard, was that the reason for it? I'll have to look at how to use that for this...


Code:
    Do While Not rst.EOF
        ' Create new task item
        Set olTask = olNSpace.Application.CreateItem(olTaskItem)
        
        With olTask
            .Assign
            .Subject = rst!Subject & ": " & rst!NAME
            .DueDate = rst!DueDate
            .Importance = rst!Importance
            .Body = rst!email & " - " & rst!Body
            .Save
            .Display ' At this point I can manually add the delegate, and press send.
            SendKeys rst!email, True
            '.Send         - I had to comment this out, since the task gets sent during the Display method...
        End With
        rst.MoveNext
    Loop
    
End Sub
 

vbaInet

AWF VIP
Local time
Today, 17:49
Joined
Jan 22, 2010
Messages
26,374
Just search for Sendkeys + VBA.

SendKeys fires off a keystroke simulating a user clicking those keys. So you would perhaps tab to the To field (if that's not the default), save the e-mail address into the office clipboard, then paste that value from the clipboard into the To box. Open the dialog and see if it defaults in the To box, if it does then you don't need to research the SendKeys function.
 

kit_sune

Registered User.
Local time
Today, 10:49
Joined
Aug 19, 2013
Messages
88
Alright, here's what I've been able to do. I'll admit, SendKeys feels risky, but it seems to work well enough. I read that DoEvents can slow the processor a bit, but when I tested it out it didn't have much of a lag at all.

Code:
 Private Sub SendTasks_Button_Click()
    Dim olApp       As Outlook.Application
    Dim olNSpace    As Outlook.NameSpace
    Dim olTask      As Outlook.TaskItem
    Dim dbs         As DAO.Database
    Dim rst         As DAO.Recordset
    Dim intWait     As Integer
       
    Set olApp = New Outlook.Application
    Set olNSpace = olApp.GetNamespace("MAPI")
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("TESTTasks_qry", dbOpenSnapshot)
    
    If MsgBox("Please ensure the Training Report is up to date.  While Access is processing your request please wait.", vbOKCancel) = vbOK Then

        Do While Not rst.EOF
            ' Create new task item
            Set olTask = olNSpace.Application.CreateItem(olTaskItem)
            
            With olTask
                .Assign    'Sets up the Task to allow delegation
                .Subject = rst!Subject & ": " & rst!NAME
                .DueDate = rst!DueDate
                .importance = rst!importance
                .Body = rst!Body
                .Save
                .Display   'Presents the task
                
                'The following lines manipulate the Task box. First by inputting the email address, and then by 'backing out of the "To" field,
                'and using the "Alt+S" macro to "Send" the task to the delegate.
                SendKeys rst!email
                SendKeys "+{TAB}"
                SendKeys "%S"
                'The For loop forces Access to pause for a little while in between each task to allow sufficient time for the SendKeys to
                'finish processing.
                For intWait = 1 To 500
                    DoEvents
                Next
    
            End With
            rst.MoveNext
        Loop
    Else
        MsgBox "Operation cancelled", vbOKOnly
    End If
End Sub
 

vbaInet

AWF VIP
Local time
Today, 17:49
Joined
Jan 22, 2010
Messages
26,374
Good job! I'll tweak your code later, not just at the moment.
 

vbaInet

AWF VIP
Local time
Today, 17:49
Joined
Jan 22, 2010
Messages
26,374
I've only had time to come back to this but I'm sure that you've developed this further. As promised, I've tweaked it to show how I would do it:
Code:
       .Body = rst!Body
        .Display
        DoEvents
        SendKeys rst!Email, True
        For intWait = 1 To 100
            DoEvents
        Next
        .Close olSave
        .Send
SendKeys is not ideal but in your case, it would do. You might also research something called redemption.

Remember to add some error checking code.
 

darbid

Registered User.
Local time
Today, 18:49
Joined
Jun 26, 2008
Messages
1,428
Hi all, this is a pretty long thread and I may not have picked up on everything, but I am a little bit concerned about leaving it with sendkey as a solution. I hope I can improve on that for you.

First I believe that if you are going to start a new instance of Outlook as you are doing in your code you should use the Logon method on the NameSpace like this.
Code:
olNSpace.Logon "", "", False, True
Second I think the order in which you do things is very important. For example you need to add recipients last and only after you have done this you call Assign. (you do not need a recipients object, if you do create one, and then call resolve on it you will be getting into the area of warnings depending on the computer and operating systems, versions of office and virus software.)

You do not have a task startDate. I would suggest you add one. Further all dates I would suggest you add as follows;

Code:
Dim dt_startdate As Date
dt_startdate = rst!startDate
.startDate = Format(dt_startdate, "Short Date") & " " & Format("9.00", "Short Time")
In this example your date (not text from a table) is the dt_startDate variable. I specifically do not use the time from the date but start at 9am.

I would suggest you add a status (although I have no idea if it will help you with any issues) eg

Code:
.Status = olTaskInProgress
Then I would suggest you add an owner - the person giving the task.
Code:
.Owner = "noddyandbigears@toytown.com"
Then after you have all properties set and immediately before you are about to display the task you start with the recipients - the first one being the owner again. I believe standard email addresses in string form are ok.

Code:
.Recipients.add ("noddyandbigears@toytown.com")
Code:
.Recipients.add ("mrPlod@toytown.com")
Add as many recipients as you like.
Now put your assign
Code:
.assign
    .display (False)

Change your code as I have suggested and try it out. If it is still not right copy your code and past so I can see how you changed it.
 

vbaInet

AWF VIP
Local time
Today, 17:49
Joined
Jan 22, 2010
Messages
26,374
Hi all, this is a pretty long thread and I may not have picked up on everything, but I am a little bit concerned about leaving it with sendkey as a solution. I hope I can improve on that for you.
The main points have been covered. The poster's Outlook is blocking access to his Recipients, this is a security feature in Outlook, and his IT will not allow him to change that setting hence the workaround with SendKeys.
 

darbid

Registered User.
Local time
Today, 18:49
Joined
Jun 26, 2008
Messages
1,428
The main points have been covered. The poster's Outlook is blocking access to his Recipients, this is a security feature in Outlook, and his IT will not allow him to change that setting hence the workaround with SendKeys.

Ok. I kinda gathered that, but find that very hard to believe. As long as he does not use an Outlook.Recipients object but just adds email addresses to the Task.Recipients.Add method his Outlook exchange is not going to know about it.

The thing to avoid is to call resolve on the Recipients object. That then will start asking the exchange server and address book for information.
 

vbaInet

AWF VIP
Local time
Today, 17:49
Joined
Jan 22, 2010
Messages
26,374
If you read through the thread you will find that has already been tried and you will see screenshots of the security option. It simply doesn't get past Task.Recipients.Add
 

darbid

Registered User.
Local time
Today, 18:49
Joined
Jun 26, 2008
Messages
1,428
I can only see one image added to the thread which is the trust center window. It does have some restrictions that I am not familiar with but I still think that if he follows my suggestions then he will not run foul of the special security settings set by an admin or Microsoft's Outlook security guard settings.

I can see that the code stopped on "SET recipients......" I can also read that playing around with the namespace caused issues like "currentUser". I don't think any of that is necessary.

I am 99% sure that Task.Recipients.add ("noddyandbigears@toytown.com") does not offend any standard security settings.
 

Users who are viewing this thread

Top Bottom