tommyonegun
New Member
- Joined
- Aug 20, 2015
- Messages
- 25
I have the vba worked out to create the appointment with no issues.
It works great. Where I get lost is myApt.Body = "Tasks". That's just a place holder for now. What I want to do is grab every row, columns A:D, where the value in D equals ActiveCell.Value, and list those results in the body of the email instead of "Tasks". I've tried a few things and searched the forum with no results. Any help would be greatly appreciated. An example of the Spreadsheet is below. In the real sheet there is a bunch of data off to the right. Essentially I highlight the day of the week I'm creating the meeting for in Column F, and run the Macro. It confirms the date and creates a summary meeting with the total hours for that day in the Subject. Now I just need the other detail in the body of the meeting.
<tbody>
</tbody>
Code:
Sub Makeapt()
Dim warning
warning = MsgBox("You are about to create Outlook appointments for " & ActiveCell.Value & " " & Cells(ActiveCell.Row, 10) & ". Is that right?", vbOKCancel)
If warning = vbCancel Then Exit Sub
Set myOutlook = CreateObject("Outlook.Application")
Set ID = Cells(ActiveCell.Row, 10)
Set myApt = myOutlook.createitem(1)
myApt.Subject = ID & " " & Cells(ActiveCell.Row, 7) & " Hours Booked"
myApt.Start = Cells(ActiveCell.Row, 10) & " 6:00:00 PM"
myApt.End = Cells(ActiveCell.Row, 10) & " 7:00:00 PM"
myApt.Body = "Tasks"
myApt.Save
End Sub
It works great. Where I get lost is myApt.Body = "Tasks". That's just a place holder for now. What I want to do is grab every row, columns A:D, where the value in D equals ActiveCell.Value, and list those results in the body of the email instead of "Tasks". I've tried a few things and searched the forum with no results. Any help would be greatly appreciated. An example of the Spreadsheet is below. In the real sheet there is a bunch of data off to the right. Essentially I highlight the day of the week I'm creating the meeting for in Column F, and run the Macro. It confirms the date and creates a summary meeting with the total hours for that day in the Subject. Now I just need the other detail in the body of the meeting.
A | B | C | D | E | F |
Client | Task Detail | Hrs | Day | Meeting Day | |
Client 1 | Task 1 | 2 | Monday | Monday | |
Client 2 | Task 2 | 3 | Monday | Tuesday | |
Client 3 | Task 3 | 1 | Monday | Wednesday | |
Client 4 | Task 4 | 4 | Monday | Thursday | |
Client 5 | Task 5 | 4 | Tuesday | Friday | |
Client 6 | Task 6 | 3 | Tuesday | Saturday | |
Client 7 | Task 7 | 2 | Tuesday | Sunday | |
Client 8 | Task 8 | 2 | Wednesday | ||
Client 9 | Task 9 | 1 | Wednesday |
<tbody>
</tbody>
Last edited: