Sending meeting requests from Excel

[Pasty]

Hi there,

I have messed around with some code and it fires off meeting requests to recipients if their actions are Priority 1 and then fill in next to the cell that a meeting request has been sent but the problem I am having is that it is going down the list and saying that 2 or 3 meeting request have been sent but only ever fires one off - would anyone be able to let me know where I am going wrong?

The code is

Sub SendMeetingRequest()
    Dim objOL   'As Outlook.Application
    Dim objAppt 'As Outlook.AppointmentItem
    Dim Subject As String
    Dim Body As String
    Dim wkBook As Workbook
    Dim wsMain As Worksheet
    Dim myCell As Range
    Dim myR As Range
    
    Const olAppointmentItem = 1
    Const olMeeting = 1
    
    Set wkBook = ThisWorkbook
    Set wsMain = wkBook.Worksheets("Audit")
    Set myR = wsMain.Range("F2:F20")
    Set objOL = CreateObject("Outlook.Application")
    Set objAppt = objOL.CreateItem(olAppointmentItem)
    
    For Each myCell In myR
    
    If myCell.Value = "Priority 1" And _
         myCell(1, 18).Value <> "Meeting request sent" Then
        
    'Set objAppt = objOL.CreateItem(olAppointmentItem)
       
    With wsMain
    
    Subject = "Priority 1 audit actions - please propose a new time"
    Body = "Action: " & vbCrLf & .Cells(myCell.Row, 5)
    
    End With
       
    Application.ScreenUpdating = False
    
    If Not objOL Is Nothing Then
        With objAppt
        .Subject = Subject
        .Body = Body
        .Start = Now + 1
        .End = DateAdd("h", 1, .Start)
        
        ' make it a meeting request
        .MeetingStatus = olMeeting
        .RequiredAttendees = "NFR Test Account"
        .Send
    End With
    
    Set objAppt = Nothing
    Set objOL = Nothing
    End If
    End If

If myCell.Value = "Priority 1" Then
myCell(1, 18).Value = "Meeting request sent"
myCell(1, 19).Value = Date
End If

Next myCell

End Sub

Regards

Matt

 

[VoG]

I may be missing something but here

       myCell(1, 18).Value <> "Meeting request sent" Then

you are testing for a condition that you set within the same loop:

myCell(1, 18).Value = "Meeting request sent"

So your loop will only ever execute once.

 

[Pasty]

Hi,

I had a similar thing set up to send tasks and this worked fine.

It was set up like this so that if a meeting request had already been sent it wouldn't fire off another one and annoy the recipient or confuse them.

I commented this out and it still only fires off one meeting request.