vba that searches outlook appointments and opens them

[aspiringnerd]

So I currently have the following code that creates a new event based on values I have in a table. I need help creating a vba script that will open the event this generates whenever there is a change in my table so I can synch them together.

Sub FindAndSync()
    Set olOutlook = CreateObject("Outlook.Application")
    Set Namespace = olOutlook.GetNamespace("MAPI")
    Set myRecipient = Namespace.CreateRecipient("sharedcalendar@outlook.com")
    Set oloFolder = Namespace.GetSharedDefaultFolder(myRecipient, 9)
    Set Appointment = oloFolder.items.Add
    
    With Appointment
        .AllDayEvent = True
        .Reminderset = False
        .Start = Cells(ActiveCell.Row, "H").Value
        .End = Cells(ActiveCell.Row, "I").Value
        .Categories = Cells(ActiveCell.Row, "J").Value
        .Subject = Cells(ActiveCell.Row, "K").Value
        .display
    End With
End Sub

 

[tfurnivall]

I'm not an Outlook User - let alone an Outlook coder, but your code seems to be creating a new Appointment. This is at odds with the name of your routine, which is FindAndSync! Leaving aside this small issue, and assuing that somewhere you save the Appointment (so it can be found and sync'ed later on, I think what you really want is something like this:

Goal
Whenever the contents of the table change, find the right event and put in the changed values, as appropriate.

Questions
1) How do you identify the Appointment? You are establishing initial values for Star5t, End, Categories and Subject in the code you supply, and this would allow you to detect that values of interest have changed in the spreadsheet. But there seems to be no way to capture enough information to identify the Appointment. (You can't search for matching information, because those values have changed, right?)

2) How do you detect that the cells of interest have changed?
You'd probably use the Worksheet_Change event. This will tell you the Target (the cell(s) that changed), and you can set a flag based on the intersection of yourt Table range, or simply by parsing out the Target.Address, and comparing it. If this Worksheet event handler sets the flag, then you need to call (probably) an external routine to do the work of Finding and Sync'ing the Appointment. (Of course, Finding it goes right back to Question 1).

The part of your FindAndSync routine that updates the fields in Appointment is exactly what you need to change the Appointment once you've found it.

HTH, I look forward to seeing the answers to the questions

Tony

 

[aspiringnerd]

I'm not an Outlook User - let alone an Outlook coder, but your code seems to be creating a new Appointment. This is at odds with the name of your routine, which is FindAndSync! Leaving aside this small issue, and assuing that somewhere you save the Appointment (so it can be found and sync'ed later on, I think what you really want is something like this:

Goal
Whenever the contents of the table change, find the right event and put in the changed values, as appropriate.

Questions
1) How do you identify the Appointment? You are establishing initial values for Star5t, End, Categories and Subject in the code you supply, and this would allow you to detect that values of interest have changed in the spreadsheet. But there seems to be no way to capture enough information to identify the Appointment. (You can't search for matching information, because those values have changed, right?)

2) How do you detect that the cells of interest have changed?
You'd probably use the Worksheet_Change event. This will tell you the Target (the cell(s) that changed), and you can set a flag based on the intersection of yourt Table range, or simply by parsing out the Target.Address, and comparing it. If this Worksheet event handler sets the flag, then you need to call (probably) an external routine to do the work of Finding and Sync'ing the Appointment. (Of course, Finding it goes right back to Question 1).

The part of your FindAndSync routine that updates the fields in Appointment is exactly what you need to change the Appointment once you've found it.

HTH, I look forward to seeing the answers to the questions

Tony

1. The subject of the appointment is the unique identifier
2. I intend on creating a button that I can click whenever there is a change, when clicked it will search the subject line ahead of today (not behind as those are historical). If an event is found, I'd like it to change the dates and display the event so I can confirm the changes are correct.

 

[tfurnivall]

Bingo! You've just identified the overall approach that you need to take. (And I bet it was painless).

The key phrase is "and display the event, so I can confirm the changes are correct". This need to display additional information, and then approve - or disapprove - is calling out for the use of a User Form! ("Rats!" I hear you mutter, "I just want to get this done")

Using User Forms is a great way to expand your knowledge of Excel VBA, and an even greater way to free yourself of the tyranny of the grid. I've attached a little spreadsheet which has a User Form attached to it.

Hmm


Well, I haven't done that. Seems I can't upload a spreadsheet. I'll see if I can talk you through it in a set of posts. It'll take a bit longer, but you'll probably get a better grasp.

Let me know how you'd like to proceed...


Tony

 

[tfurnivall]

Primarily for aspiringnerd, but also for those who may be wondering...

1. Create a new directory, called UpdateOutlookAppointments.
This is where we will store the code and the workbook.

2. Within this new directory, create another directory called Components.
This is where you will store the code portions of the solution.

You now have a place within which you can work, and be reasonably isolated from outside influences.

3. Create a new workbook, and save it in the UpdateOutlookAppointments folder, as AppoointmentFormforOutlook.xlsm
The extension (.xlsm) is significant, because it implies that the workbook has macros.

(These next few lines apply to Microsoft Office 365. If your version of Excel shows significant differences, let me know, and we'll figure out how to get through this)

4. Within the excel workbook click on File, and then click on Options.
Then click on the Trust Center category.
This will bring up a new category with a list of sub-categories. Click on the Macro Settings sub-category.

Click on Enable VBA Macros.
Click on Trust access to the VBA Project object model.
Click OK to get out, and return to the previous level.
Repeat until you are back in the spreadsheet.

5. Now press Alt + f11, which will open up the VB development environment.

6. Select Insert on the Menu Bar, and then click on UserForm.
A new UserForm (with no other properties) will appear.

SAVE YOUR WORK!

7. Click anywhere on the new UserForm.

8. Click File | Export File
This will Export the UserForm, and brings up a dialog box. Navigate to the Components sub-directory which you created in step 2. Save the form as UserForm.


That'll do as a first chunk of work! Any problems/questions just drop me a line via this Forum.

Good Luck,

Tony