Invite Attendies to display in Outlook Macro

[Jay3]

Hi,

Can someone help me with the following code. I need to understand why my invite attendees box would display when I run the following macro.

Option Explicit
Public Sub Create_Reminder(ByVal aRow As Long)
  Dim oApp As Object ' Outlook.Application
  Dim oNameSpace As Object  ' Namespace
  Dim oItem As Object  ' AppointmentItem
  Const olImportanceNormal As Integer = 1
  Const olAppointmentItem As Integer = 1
  Dim iLastRow As Long
  Dim irow As Long
  Dim wsData As Worksheet
  Dim colCB As Object 'Office.CommandBars
  Dim oInsp As Object 'Outlook.inspector
  
    
  Set wsData = ThisWorkbook.Sheets("Sheet1")
  
  On Error Resume Next
  ' check if Outlook is running
  Set oApp = GetObject("Outlook.Application")
  If Err <> 0 Then
    'if not running, start it
    Set oApp = CreateObject("Outlook.Application")
  End If
  
  Set oNameSpace = oApp.GetNamespace("MAPI")
  
  Set oItem = oApp.CreateItem(olAppointmentItem)
  With oItem
    .Subject = wsData.Range("C" & aRow).Value
    .Body = "Originator: " & wsData.Range("B" & aRow).Value & vbCrLf _
          & "Request: " & wsData.Range("D" & aRow).Value & vbCrLf _
          & "Created: " & Format(Now(), "dd/mm/yyyy hh:nn:ss") & vbCrLf
    .Start = wsData.Range("E" & aRow).Value + TimeValue("09:00:00")
    .Duration = "00:00"
    
    .AllDayEvent = True
    .Importance = olImportanceNormal
    .Location = "*** Location goes here ***"
    
    .ReminderSet = True
    .ReminderMinutesBeforeStart = 24 * 60 ' assume reminder is 24 hours before
    .ReminderPlaySound = True
    .ReminderSoundFile = "C:\Windows\Media\Ding.wav"
 
    .Recipients.Add ([EMAIL="Someone@somewhere.co.uk"]Someone@somewhere.co.uk[/EMAIL])
    
    Set oInsp = oItem.GetInspector
    Set colCB = oInsp.CommanBars
    colCB.ExecuteMso ("InviteAttendees")
    
    .Display   'or use .Send
    
  
  End With
    
  Set oApp = Nothing
  Set oNameSpace = Nothing
  Set oItem = Nothing
  Exit Sub
  
End Sub

This is the code that should display the "Invite Attendees" box.

  Set oInsp = oItem.GetInspector
    Set colCB = oInsp.CommanBars
    colCB.ExecuteMso ("InviteAttendees")

but it doesn't seem to be working.

Can anyone help?

Thanks,
Jay3

 

[VoG]

Shouldn't it be

Set colCB = oInsp.CommandBars

 

[Jay3]

Arghhhhhhh!!!!

Thanks very much )

 

[Jay3]

Ah! It's still not working??

 

[VoG]

It worked for me when I added a valid e-mail address here

.Recipients.Add "VoG@work.com"

 

[Jay3]

Could it be related to the actual settings in outlook itself, because it still isn't working for me?

Thanks,
Jay3

 

[VoG]

Sorry, I don't know. Have you tried sending the invitation to yourself? That is what I did to test.

 

[Ruddles]

Strange... it worked at home last night but it doesn't work today at work...

(Outlook 2007 at both locations.)

 

[VoG]

Strange... it worked at home last night but it doesn't work today at work...

(Outlook 2007 at both locations.)

I tested "at work" (something of a euphemism these days ) using Excel 2000 and Outlook 2003 (don't ask!).

 

[Ruddles]

Jay3, rearrange the commands so the code to display the invitees is after the code to display the message - this way round:-<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>

[FONT=Courier New]      .Display<o:p></o:p>[/FONT]
[FONT=Courier New]      Set oInsp = oItem.GetInspector<o:p></o:p>[/FONT]
[FONT=Courier New]      Set colCB = oInsp.CommandBars<o:p></o:p>[/FONT]
[FONT=Courier New]      colCB.ExecuteMso ("InviteAttendees")<o:p></o:p>[/FONT]