Hi all, I know this has been tackled in various forums but I just can't quite find the answer I need. I'm fairly new to this so struggling a bit...

I'm trying to extract data from a sheet to create meeting requests. Each line on the spreadsheet will set up a new meeting invitation.

After playing around a lot, I can get it to generate meeting requests if the data is on the same sheet as the code. However, I need the code to be on a different sheet to the table itself. I also can't get it to use email addresses within the sheet....Only when I specify them in the actual code.

Here is the data I'm using: The worksheet with this table is called 'Curtailments'

Site name Unit ID Agent Entity (MW) Start time Start date Cease time Cease Date
Batsworthy Cross ODFM44-01 TestCo1 Entity1 100 10:00 05/05/2020 16:00 05/05/2020
Denzel Downs ODFM45-01 TestCo1 Entity1 200 11:00 05/05/2020 15:00 05/05/2020
Forss ODFM42-01 TestCo1 Entity1 0 10:00 05/05/2020 16:00 05/05/2020
Little Raith ODFM80-01 TestCo1 Entity1 0 11:00 05/05/2020 15:00 05/05/2020


Below is the code that's on a different sheet:

Dim OLook As Outlook.Application
Set OLook = New Outlook.Application
Dim sh As Worksheet
Set sh = sheets("Curtailments")
Dim Oapt As Outlook.AppointmentItem
Dim r As Long
Dim mylist As String
On Error Resume Next




Set Oapt = OLook.CreateItem(olAppointmentItem)
On Error GoTo 0
r = 2 ' first row with data
Oapt.MeetingStatus = olMeeting


With Oapt
' read appointment values from the worksheet
On Error Resume Next
Oapt.Recipients.Add ("Need to get addresses from curtailments sheet")
.Start = sh.[Cells(r, 7).Value + Cells(r, 6).Value]
.End = Cells(r, 3).Value + Cells(r, 4).Value
.Subject = Cells(r, 5).Value
.Location = Cells(r, 6).Value
.Body = "testing"
.ReminderSet = Cells(r, 7).Value
On Error GoTo 0
.Display
'.Save ' saves the new appointment to the default folder


End With
Set Oapt = Nothing
Set OLook = Nothing


End Sub

As you can see I've tried but failed miserably so any help would be very much appreciated. Thank you in advance.