Recipients.Add method causing Error 287

[Excel1user]

I am writing vba code to create Outlook tasks for various employees from an Excel spreadsheet. The spreadsheet contains a list of reports under review and who is assigned to review the task.
I want the vba code to create an Outlook task for the individual assigned to review the report. I have found a number of websites with example code of how to do this, but everytime I have tried to use the code I get the same error on the "Recipients.Add" line. The error is vba "Run-time error '287': Application-defined or object-defined error".
Here is the code I'm using: (It is interesting to note that I have written code that successfully writes an e-mail, but that code uses the ".To" method, instead of the "Recipients.Add" method. Unfortunately the ".To" method is not available for Task Items.)

Option Explicit
Sub createtask()
    Dim olApp As Outlook.Application, olNs As Outlook.Namespace, olTask As Outlook.TaskItem
    Set olApp = Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
        olNs.Logon
    Set olTask = olApp.CreateItem(olTaskItem)
    olTask.Assign
    olTask.Subject = "Test"
    olTask.Body = "Testing macro"
    olTask.Recipients.Add ("myemail@myemail.com")
    olTask.Display
End Sub

 

[Norie]

That code works fine for me.

Is that the actual code that's causing an error?

 

[Excel1user]

Yes the above code is exactly the code I am trying to use. It gives me the error on the Recipients.Add method. I can create a task just fine, but if I try to use the Recipients.Add method then it fails every time.
I tried the code below in Outlook vba and it worked perfectly, so it appears that there is an issue with Excel vba using the Outlook Recipients.Add method. Is anyone aware of anything that might prevent using the Recipients.Add method in Excel vba, but not Outlook vba?

Public Sub CreateTask1()
    Dim olTask As TaskItem
    Set olTask = CreateItem(olTaskItem)
    olTask.Assign
    olTask.Subject = "Test"
    olTask.Body = "Testing macro"
    olTask.Recipients.Add ("myemail@myemail.com")
    olTask.Display
End Sub

 

[Norie]

Is Outlook open when you get the error?

 

[Excel1user]

Yes, although I have tried it both with Outlook open and with Outlook closed and I get the same error.

 

[Excel1user]

Thanks for the help. I have found a temporary work-around using the "SendKeys" method. Although I would like to find a better solution because SendKeys is not good practice, and is prone to malfunction if a different application is activated for some reason.

By the way, I am using Windows 7 and Office 2010. I also tested this code on a co-workers computer who still had Office 2007 and I got the same error.

 

[Norie]

Why are you using Recipients.Add when there's only one recipient?

Can't you use To?

 

[s.ridd]

Couldn't tell you why you're getting that error or why SendKeys would fix it, perhaps you're missing a windows update which seems to be the cause of a lot of these random errors.

Out of interest does this minor alteration work (again there's no reason it should)

Sub createtask()
    Dim olApp As Outlook.Application, olNs As Outlook.Namespace, olTask As Outlook.TaskItem, olRec As Outlook.Recipient
    Set olApp = Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
        olNs.Logon
    Set olTask = olApp.CreateItem(olTaskItem)
    olTask.Assign
    olTask.Subject = "Test"
    olTask.Body = "Testing macro"
    Set olRec = olTask.Recipients.Add("[EMAIL="myemail@myemail.com"]myemail@myemail.com[/EMAIL]")
    olRec.Resolve
    olTask.Display
End Sub

On a seperate note you might want to consider using a With Statement for olTask to both tidy up your code and avoid having to reference olTask for each property

 

[Excel1user]

Thanks for the attempt, but no that failed as well with the same error on the line

Set olRec = olTask.Recipients.Add("myemail@myemail.com")

Just for completeness, here is the code that works using SendKeys as a work-around.

Sub createtask()
    Dim olApp As Outlook.Application, olNs As Outlook.Namespace, olTask As Outlook.TaskItem
    Set olApp = Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
        olNs.Logon
    Set olTask = olApp.CreateItem(olTaskItem)
    olTask.Assign
    olTask.Subject = "Test"
    olTask.Body = "Testing macro"
    olTask.Display
    SendKeys "myemail@myemail.com"
End Sub

 

[Excel1user]

Norie,
Unfortunately the .To method is only available for composing an e-mail. It is not an option for creating a TaskItem.