jmcconnell
05-30-2020, 08:41 PM
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.
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.