Create and email outlook appointment from MS Project w/VBA

Question

A VBA newbie.

I am using MS Project as a Task scheduler and have built custom views in MS Project so that each Resource can view their specific Tasks. I am using the code below to create appointments in each of the Resource's Outlook Calendars by way of them selecting their specific Task view in MS Project and running the macro below. This works fine and populates appointments in individuals diaries as required.

However I am trying to extend the functionality of this code to allow an "administrator" to select the specific Task views for each Resource in MS Project and to then run the macro to generate appointments to be sent to each indviudal to create an appointment in their calendar.

The problem I have encountered is that whilst the Outlook appointment is created correctly and includes the (resolved) Resource name in the attendees tab of the apointment, the appointment form itself lacks a Send button. If I then manually add any other attendee to the appointment it resolves and the Send button appears and can be sent correctly.

The Msgbox simply displays the name of the assigned Resopurce in MS Project.

I have tried mulitple variations in setting myDelegate but with no success, any thoughts on this would be greatly appreciated.

Option Explicit
Public myOLApp As Object
Sub Export_Selection_to_OL_Appointments_AutoEmail()
    Dim myTask As Task
    Dim myDelegate As Object
    Dim myItem As Object
    Dim Msg As Object

    On Error Resume Next
    Set myOLApp = CreateObject("Outlook.Application")
     For Each myTask In ActiveSelection.Tasks
     Set myItem = myOLApp.CreateItem(1)
     myItem.Assign
      With myItem
        Set myDelegate = myItem.Recipients.Add(myTask.Resources(1).EMailAddress)
        myDelegate.Resolve
        Msg = MsgBox("myDelegate is " & myDelegate, vbOKOnly)
        .Start = myTask.Start
        .End = myTask.Finish
        .Subject = myTask.Text1 & ": " & myTask.Text2
        .Categories = myTask.Project
        .Body = myTask.Notes
        .Display
        .Send
      End With
  Next myTask
End Sub

html_programmer html_programmer · Accepted Answer · 2013-12-10T17:01:20

I'm not sure if it will solve the problem entirely, but this looks like incorrect syntax:

With myItem
    Set myDelegate = myItem.Recipients.Add(myTask.Resources(1).EMailAddress)
End with

If you use With then you should either put it outside the with block:

Set myDelegate = myItem.Recipients.Add(myTask.Resources(1).EMailAddress)
With myItem
'...
End with

or access the properties this way:

With myItem
Set myDelegate = .Recipients.Add(myTask.Resources(1).EMailAddress)
End with

Also use

debug.print(ActiveSelection.Tasks & " ; " & myTask)

to check that the object is not null.

Also use the "locals" window to check the properties of your objects.
myTask should display a property list, among which Resources(1) should have its own sub-properties, among which "emaiAddress".

Create and email outlook appointment from MS Project w/VBA

1 Answers