Assign Outlook Task Item loop

[vbaInet]

Try .Assign before doing recipients

But yes, also try spikepl's suggestion:

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

 

[kit_sune]

Adding the olTask.Assign didn't seem to have any affect on the program (But it didn't hurt it).
I added the MsgBox commands - the first one didn't work for me, but the second two did once I commented the first one out. When I clicked Debug to look at the first line a mouse over revealed this: "olApp.Session.CurrentUser = <Application-defined or object defined error>"

Also, I wasn't successful in finding anything that worked elsewhere, other than this:
http://www.mrexcel.com/forum/excel-questions/703891-recipients-add-method-causing-error-287-a.html
Where the fix was using "sendKeys"


I've been peeking around in the Locals window for any variable values that might be out of control..

 

[vbaInet]

Ok, let's give this a try:

    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)
        MsgBox olNSpace.CurrentUser

... ever other code remains the same.

 

[kit_sune]

I'm at a loss for words... I'm still receiving the same error message, but now it's on the line "MsgBox olNSpace.CurrentUser"

I'm looking through the Locals window and I see CurrentUser = <> , with Type: Recipient.


I don't know if this has anything to with it, but I'll just throw this out there: My workstation uses Windows 8.

I'm heading out of the office for the day. I'm planning on taking a copy of the database with me, but I don't have access to the same MS Exchange server from home so I'm not sure if it will be the same kind of environment...

Just for consistency, to ensure I didn't make any mistakes when copying the last bit over, I've added the code below.

Thank you for all your patience.
Kit_sune

 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)
        MsgBox olNSpace.CurrentUser
        
        ' Add the delegate
        strRecipient = rst!EMAIL.Value
        olTask.Recipients.Add strRecipient
        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]

Don't worry about the other bits of code just yet. The reason why I've been asking you to carry out all those tests is because I think that it's unable to fetch your current Outlook session, that's why it fails on any code that has to do with the session, i.e. CurrentUser etc.

What sort of mail server are you using at work? Exchange?

 

[kit_sune]

Yes, it's an Exchange server. Is it having trouble communicating?

 

[vbaInet]

Something like that, it needs to be logged in. Perform another series of tests.

Private Sub SendTasks_Button_Click()
    Dim olApp        As Outlook.Application
    Dim olNSpace     As Outlook.Namespace
    
    Set olApp = New Outlook.Application
    Set olNSpace = olApp.GetNamespace("MAPI")
    
    With olNSpace
        MsgBox "Is Online: " & CBool(olNSpace.Offline)
        MsgBox "Conn mode: " & olNSpace.ExchangeConnectionMode
        MsgBox "Mail Server: " & olNSpace.ExchangeMailboxServerName
       [COLOR="Blue"] .Logon "", , True, True[/COLOR]
        MsgBox "Is Online: " & CBool(olNSpace.Offline)
        MsgBox "Conn mode: " & olNSpace.ExchangeConnectionMode
        MsgBox "Mail Server: " & olNSpace.ExchangeMailboxServerName
    End With
End Sub

You should be prompted to enter your credentials at the line in blue.

 

[kit_sune]

It doesn't prompt me to login at all. Online = False, and Conn mode = 700, but it does say I'm connected to a server. In our company we don't log in with a user Id / password combo, it's handled through a Smart Card. Another thing to note, In Outlook, when I click on File / Office Account, it doesn't give me the option to sign in, and says that feature is disabled by the Admin.

I researched a bit, and it looks like I'll have to get an ActiveX object reference library to access that, but I can't download it, so It looks like I'm stuck here.

The only thing I can think of to do now, is perhaps setting up the task one by one, without the delegate's name? I'm still not sure why it would partially work... and why it wouldn't let me add an email address...

EDIT:
I checked another forum topic that I read through before I saw that this person was able to use an email address... so it does appear to be possible!
http://www.access-programmers.co.uk/forums/showthread.php?t=79767

 

[vbaInet]

I forgot to mention that you should try that with Outlook closed.

 

[kit_sune]

I forgot to mention that you should try that with Outlook closed.

I gave this a try and it was the same thing. It didn't even attempt to open it (If it was supposed to). But all the values were the same.


EDIT:
Something else that I thought of - not sure if it pertains to this or not. I have my main account, and I also have access to an organizational email account... so maybe I need to specify which one to set up the task from?

 

[vbaInet]

EDIT:
I checked another forum topic that I read through before I saw that this person was able to use an email address... so it does appear to be possible!
http://www.access-programmers.co.uk/forums/showthread.php?t=79767

I missed this bit... the code I gave you works because I tested it on my machine, so it's nothing to do with whether it works or whether it's possible or not. There's just something that's preventing your recipients from being "manipulated" in code.

The previous tests were to establish whether there's a live connection/session, which there is, and now the question is what is preventing recipients from being added to the task item. I believe it is to do with your Outlook security settings so we need to perform some more tests.

First one is to open Outlook > hit Alt+F11 (this will open the VBA window) > create a new Module > copy and paste the code below > open the Immediate Window and run the sub.

Public Sub TasksTest()
    Dim olTask       As Outlook.TaskItem
    Dim olRecipient  As Outlook.Recipient
    
    ' Create new task item
    Set olTask = Application.CreateItem(olTaskItem)
    
    ' Add the delegate
    Set olRecipient = olTask.Recipients.Add("[COLOR="Blue"]Email address here[/COLOR]")
End Sub

... tell me if this runs successfully from within Outlook.

Second test is back in Access > run the following:

Private Sub SendTasks_Button_Click()
    Dim olApp        As Outlook.Application
    Dim olNSpace     As Outlook.Namespace
    Dim olFold       As Outlook.Folder
    Dim olExplorer   As Outlook.Explorer
    Dim olTask       As Outlook.TaskItem
    Dim olRecipient  As Outlook.Recipient
    
    Set olApp = New Outlook.Application
    Set olNSpace = olApp.GetNamespace("MAPI")
    
    olNSpace.Folders("[COLOR="blue"]Email address here[/COLOR]").Folders("Inbox").Display
'    olNSpace.GetDefaultFolder(olFolderInbox).Display
    
    ' Create new task item
    Set olTask = olNSpace.Application.CreateItem(olTaskItem)
    
    ' Add the delegate
    Set olRecipient = olTask.Recipients.Add("[COLOR="Blue"]Email address here[/COLOR]")
End Sub

... tell me if you see any security dialog.

With regards having multiple accounts, we can test that later. I wouldn't have thought that it would interfere but we'll see.

 

[kit_sune]

The TasksTest subroutine ran, but I put a break on "End Sub" to make sure it was actually doing something. I used a coworker's email address.

The other bit, I put my email adress in the first placeholder, and my coworker's email address in the second, and ran it. It Expands Outlook (from a minimized state), and gets hung up when adding the delegate, like before.

I have to admit... seeing Outlook popped up like that gave me hope again.

 

[vbaInet]

I have to admit... seeing Outlook popped up like that gave me hope again.

Ha, don't pop the champagne yet! It popped up because of the Display code I put there.

So we've also established that you can successfully add recipients through code from within Outlook but not from elsewhere on your machine.

Go to Outlook Options (i.e. File Options) > click Trust Center tab > then Trust Center Settings > show me screenshots of the following:

1. Programmatic Access tab
2. Macro Settings tab

There's definitely something preventing access to your recipients.

 

[kit_sune]

I'm not 100% sure if the attachment worked or not, but I did as you requested and attached the image to this message. Hopefully it worked, but if not I'll rework it.

In a nutshell, everything is set as read-only with the exception of the Add-ins option to apply macro security settings to them.

The only enabled radio button is the "Notifications for all macros", under Macro Settings.

 

[vbaInet]

Yes it worked. I'm surprised that nothing is selected in the Programmatic Access page.

Close Outlook > search for it > right-click the Outlook result and select "Run as Administrator". Go back to the options and see if those options are now enabled.

 

[kit_sune]

Sadly, I can't run it as an Admin. It prompts an admin account login. I won't be able to convince (whoever it is) who has Admin rights to make any changes, unfortunately.

 

[vbaInet]

That's definitely what's preventing code from accessing your address book.

I've got another idea but I'll let you know later.

 

[vbaInet]

I've given this some thought and you have two options:

1. Write the function in Outlook and fetch your data from Access. You'll have to run the code from within Outlook.
2. Write the function in Outlook but attempt to call the function from Access.

 

[kit_sune]

I've given this some thought and you have two options:

1. Write the function in Outlook and fetch your data from Access. You'll have to run the code from within Outlook.
2. Write the function in Outlook but attempt to call the function from Access.

Do you think this would be possible:
Instead of "With olRecipient", perhaps we could set it up to build the entire task first, but leave off the recipient... then, establish a pause when the system displays the task for me to manually add the recipient, and then continue after I acknoledge that it was added. I would be happy with that.

I'll see if I can't figure that out... Let me know if you think it's a waste of time (that it probably won't work, or whatnot).

 

[vbaInet]

Building the task before or after is not the problem, so it won't really matter which order it comes, however, there's logic in the way that I currently have it setup.

You can try bringing up the Recipients dialog but if your security is preventing access to your recipients I'm doubtful that this will work. In any case, I'll write some code to this later and paste it for you to test.