Update Appointment category when changed in Excel

Status
Not open for further replies.

Kevin8675309

New Member
Outlook version
Outlook 2016 32 bit
Email Account
Exchange Server
I have a task tracking list in an Excel table. I have a macro (attached to a userform CommandButton) that, after I fill out the form, it adds the data to the last row of the table and then creates meeting invites based on the due date and time on each row. To avoid duplication of appointments, the code also adds "Created" in the 18th column, then only creates new appointments if that cell is empty.

This is working relatively well, but I'd like my appointment category to update based on the value in one of the columns (PENDING when created, and changed to CLOSED when it's completed).

Here is the code that creates the appointment invite. How do I construct VBA code to look at the Excel table again to change the category of appointments from PENDING to CLOSED if I have changed it to CLOSED in the table?


Sub Testing ()

Dim olApp As Outlook.Application
Dim OutMail As Outlook.AppointmentItem
Dim MySheet As Worksheet

Set olApp - New Outlook.Application
Set OutMail = olApp.CreateItem(olAppointmentItem)
Set MySheet = Worksheets("Tracker")

Cont COL_FLAG As Long = 18 'adds a column to my table - used to avoid duplicating appointments

For r = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Len(Cells(r, COL_FLAG).Value) = 0 Then

With OutMail

.MeetingStatus = olMetting
.Start = Cells(r, 2).Value + Cells(r, 3).Value
.Duration = "0"
.Subject = Cells(r, 10).Value & " (" & Cells(r, 14).Value & Cells(r, 15).Value & ")"
.Location = Cells(r, 6).Value
.Body = "Follow up with task lead"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 120
.Categories = Cells(r, 4).Value
.ReminderSet = True
'.Save
.RequiredAttendees = ("me@me.com")
.Display

Cells(r, COL_FLAG).Value = "Created"

End With
End If
Next

Set olApt = Nothing
Set olApp = Nothing

End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
category of appointments from PENDING to CLOSED if I have changed it to CLOSED in the table?
if you change it to closed in the excel sheet, you should use an 'update' macro that searches the calendar for matches and updates the found entry. You may need to use a column to show the appt was updated, so the macro doesn't lookup all entries with closed as the category.

You'll need a filter - this sample uses the subject to find the appt, but you could use other values too.

Code:
strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like '%" & strsubject & "%'"
Debug.Print strFilter
Set filteredItems = objCalendarFolder.Items.Restrict(strFilter)

If filteredItems.count = 0 Then
    Debug.Print "None found"
Else
    Found = True
For Each objItem In filteredItems
 objitem.category = "Closed"
objitem.save
next
Thos page: How to print a list of recurring dates using VBA shows how to filter by dates.
 

Kevin8675309

New Member
Outlook version
Outlook 2016 32 bit
Email Account
Exchange Server
if you change it to closed in the excel sheet, you should use an 'update' macro that searches the calendar for matches and updates the found entry. You may need to use a column to show the appt was updated, so the macro doesn't lookup all entries with closed as the category.

You'll need a filter - this sample uses the subject to find the appt, but you could use other values too.

Code:
strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like '%" & strsubject & "%'"
Debug.Print strFilter
Set filteredItems = objCalendarFolder.Items.Restrict(strFilter)

If filteredItems.count = 0 Then
    Debug.Print "None found"
Else
    Found = True
For Each objItem In filteredItems
 objitem.category = "Closed"
objitem.save
next
Thos page: How to print a list of recurring dates using VBA shows how to filter by dates.
Thanks! How do I incorporate my subject, which is a string (see .subject line), into the strFilter?
.Subject = Cells(r, 10).Value & " (" & Cells(r, 14).Value & Cells(r, 15).Value & ")"
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
strSubject = Cells(r, 10).Value & " (" & Cells(r, 14).Value & Cells(r, 15).Value & ")"
strFilter = "@SQL=" & Chr(34) & "urn:schemas:httpmail:subject" & Chr(34) & " like '%" & strsubject & "%'"
Debug.Print strFilter
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
H Reocurring outlook appointment (office 2010) does not update Outlook.com Using Outlook 3
S Send Update of Custom Appointment Form Using Outlook 2
E Outlook 2003 changing appointment date or time does not update on calendar Using Outlook 5
D.Moore VBA script fail after Office 365 update Using Outlook 8
W April 2020 Office 365 Update - Add-Ons fail after Office 365 Update Using Outlook 6
M Outlook 2010 Problem with OutLook 2010 32 bit, after Windows Auto Update Using Outlook 3
Jennifer Murphy Grant R/W (update) access to a friend Using Outlook 3
L Favorites don't update Using Outlook 1
F Copy and replace not update contact in another pst Using Outlook 0
M Message list font changed after update Using Outlook 2
M Quicken One Step Update Bill Reminders Not Syncing to Outlook Using Outlook 1
O Windows 1803 update : QAT and toolbar changed, language pack gone... Using Outlook 5
J Updating existing entry on shared calendar wants to send update from delegate Using Outlook 0
N Using email notification to update calendar events? Outlook VBA and Custom Forms 4
V Outlook 2003 problem with Windows 10 Creators Update 1709 Using Outlook 0
G Windows Update Causes BCM Database Access Problem? BCM (Business Contact Manager) 4
K Update subject based on text in body Outlook VBA and Custom Forms 3
A Creating Progress Bar or Status Bar Update Outlook VBA and Custom Forms 0
M Recent Update Did not Fix Search Problems Using Outlook 7
R Custom Contact Form how to update when loaded. Outlook VBA and Custom Forms 6
C Update Notes for Meeting Attendees Using Outlook 8
A Attendee Update Outlook Meeting Invite Using Outlook 0
Diane Poremsky Outlook Email Security Update New Slipstick.com Articles 0
S Task Update coming as email and not updating task Using Outlook 3
Diane Poremsky Remove Office 2013 Update Banner New Slipstick.com Articles 0
B IMAP folders don't update when Outlook 365 opens Using Outlook 0
J Outlook Macro to Update Sharepoint Excel File Using Outlook 1
Diane Poremsky Task Request Status Update Address Missing New Slipstick.com Articles 0
Christopher M Importing from Eudora Update Using Outlook 5
Cameron Piper Automatically update custom forms across multiple computers Outlook VBA and Custom Forms 1
M Update field codes when opening Outlook Template Outlook VBA and Custom Forms 2
Bachelle Macro to Update Existing Task from New Email Outlook VBA and Custom Forms 3
I Microsoft Security Update KB3097877 Using Outlook 14
S Outlook 2010: October 2015 Update New Slipstick.com Articles 0
S Outlook 2013: October 2015 Update New Slipstick.com Articles 0
Diane Poremsky Update Contacts with a New Company Name and Email Address New Slipstick.com Articles 0
Diane Poremsky Update Contacts with a New Company Name and Email Address New Slipstick.com Articles 0
F Update the notes field Exchange Server Administration 0
A BCM crashes after Windows 10 update BCM (Business Contact Manager) 1
H SQL update column BCM (Business Contact Manager) 0
Forum Admin BCM Update for Outlook 2010/2013 - Nov 2014 BCM (Business Contact Manager) 14
P Outlook 2007 update headers Using Outlook 2
M could not update calendar Using Outlook 1
B Looking for Outlook 2013 update for Send Drafts Macro Using Outlook 4
R Can BCM monitor and select specific emails and use content info to update the client's record? BCM (Business Contact Manager) 1
2 Task auto update to shared group? Using Outlook 1
Alex Hall When updating shared calendar, it does not automatically update personal calendar Using Outlook 3
Stefanos Update Sharepoint tasks through Outlook Using Outlook 2
S New Quick Step based on Team email QS - automatically update too Using Outlook 1
H Outlook.com calendar does not update from Outlook 2013 Home Premium Using Outlook 1
Similar threads


















































Top