Deleting an Outlook Appointment in VBA

[Cyd44]

I am trying to delete an outlook appointment based upon the value of variables for Location, Start Date, Start Time & Name (Subject)

I am at a loss as where to start on declarations for Outlook Objects. I found some code on the Web and tried to alter it. Could you please help with the declarations for finding the appointment in Outlook. My rubbish attempt at coding is as follows:-

Dim olApp As Outlook.Application
  Dim objAppts As Outlook.AppointmentItem
 
  Dim AP_Date As Date
  Dim AP_Start_Time As Date
  Dim AP_Location As String
  Dim AP_Subject As String
 
  Dim OutlookStartTime As Date
  Dim OutlookLocation As String
  Dim OutlookSubject As String
  Dim sFilter As Variant
 
  AP_Date = Module1.oldDate
  AP_Start_Time = Module1.oldStart
  AP_Location = Module1.oldLocation
  AP_Subject = Module1.oldName
 
  OutlookStartTime = CDate(AP_Date & " " & AP_Start_Time)
  OutlookLocation = (AP_Location)
  OutlookSubject = (AP_Subject)
 
  Set olApp = CreateObject("Outlook.Application")
  Set objAppointments = objAppts.DeleteItem(olAppointmentItem)
 
  sFilter = "[Start] = '" & OutlookStartTime & _
    "' And [Location] = '" & OutlookLocation & "' " & _
    " And [Subject] = '" & OutlookSubject & "'"
 
  Set objAppointments = objAppointments.Items.Find(sFilter)
 
  If Not TypeName(objAppointments) = "Nothing" Then
    MsgBox ("Appointment Found")
    'objAppointment.Delete
  End If
 
  Set objAppointments = Nothing
  Set objAppts = Nothing
  Exit Sub

If I can have some help on declaration I could possibly work out the rest.

Many thanks in advance

[NeoPa]

Cyd, you need to post the question with care. Not just dump code in and expect us to work out what the question should be. Describe what you're actually doing and where you get stuck. The declarations seem fine to me, but you don't really provide a question to work with.

General tips on items and how they fit into the Outlook class structure can be found by using Object Explorer within the VBA IDE. You can do it from Access too, but probably easier to do it in the native application. See Debugging in VBA for how to invoke and have a play.

[Cyd44]

Ok NeoPa Sorry if I am not asking correctly. I will try to explain better.

I have successfully managed to create appointments in Outlook using the following sub:-

'Add a new appointment.
 
                Dim objOutlook As Outlook.Application
                Dim objAppt As Outlook.AppointmentItem
                'Dim objRecurPattern As Outlook.RecurrencePattern
 
                Set objOutlook = CreateObject("Outlook.Application")
                Set objAppt = objOutlook.CreateItem(olAppointmentItem)
 
                With objAppt
                    .Start = Me!BookStartDate & " " & Me!BookTime
                    .Duration = Me!TImeConversion
                    .Subject = Me!BookName
                    .Location = Me!BookLocation
                    If Not IsNull(Me!BookNotes) Then .Body = Me!BookNotes
 
                    .Save
                    .Close (olSave)
                    End With
                    'Release the AppointmentItem object variable.
                    Set objAppt = Nothing
 
 
            'Release the Outlook object variable.
              Set objOutlook = Nothing
              MsgBox " Changes have been Saved and  New Appointment Added to Outlook!"

I now wish to turn this around so that find an appointment by using the unique identfiers which are Location, StartDate, BookTime, Location and BookName. I want to delete this appointment if found.

I am unsure as to how I call outlook to find and delete the appointment using the above variables as I do not know the correct variable decalartions for Outlook (IE Setting Object required)

I hope this clarifies my position. I can see now that giving you code that is not working has confused things. When I try to run the delete sub I keep getting a mismatch error on declaring the Start date as both StartDate & StartTime. I do not actually understand why we cannot set the date and time separately.

[NeoPa]

It wasn't the code that confused things. It was the lack of any discernable question or even explanation of why the code is there at all. I'm sure I've linked you before to how to ask questions and what to do when posting code that isn't working as expected. The line number and error message are necessary as well as explaining what is going on. All these important elements are again missing from the question. You should also include the code that defines the procedure like the header so that when you refer to a 'Delete Sub' I don't go through all the code again just to find that nothing is identified as a 'Delete Sub'. I guess it's your first bit of code, but I frankly ought not to be spending my time guessing what you should be telling me. I guess also you're referring to line #19, but all the lines are numbered. If you use the numbers that are provided I don't need to try to read your mind.

Assuming then, that it is indeed line #19 of your first posted code that is causing some unspecified problem, I would suggest that you breakpoint your code at line #19 and see what the string representations are of [AP_Date] and [AP_Start_Time] (Debugging in VBA may help with this). I have no way of knowing what data you have in there so I can only guess. If they are a valid date and time stored in Date format though, you may get away with :

OutlookStartTime = AP_Date + AP_Start_Time

Remember, Include the procedure declaration when posting a procedure (Not always necessary for small snippets but when posting a whole procedure it's very important). Especially if you want to refer to the named procedure in your explanation.

[Cyd44]

Back to this problem having first worked out how to capture pre-edit data.

My problem here is with lack of knowledge on the syntax required to connect to Outlook and find an appointment for deletion. I have comented the code so that you can see what I am trying to achieve here. Am getting a complie error on GetdefaultFolder and I am not sure of my logic for the declarations surounding Outlook.

I hope this question makes sense as I think you can see what I am trying to do within the code. All the record field variables work fine and the values are as expected. My major problem is trying to understand the object requirements for OutLook.
My code is as follows:-

Delete_Old_Appointment:
' The Following variable assignments will pick-up the old booking data prior to the change in order
' to find and delete an Outlook booking. Variables have been caputured by the Sub Form_Current()procedure
' and declared Public in the basMyEmpID Standard Code Module.
 
    Dim goldLocation As String
    Dim goldStart As Date
    Dim goldDate As Date
    Dim goldName As String
 
    goldLocation = basMyEmpID.gstroldLocation
    goldStart = basMyEmpID.gdteoldStart
    goldDate = basMyEmpID.gdteoldDate
    goldName = basMyEmpID.gstroldName
 
    MsgBox (goldLocation & goldStart & goldDate & goldName) ' Take Out when fully developed
 
' Connection to Outlook Variables
 
    Dim objOlook As outlook.Application
    Dim objFolder As outlook.MAPIFolder
    Dim objOAppt As outlook.AppointmentItem
    Dim objOItems As outlook.Items
    Dim appStart As Variant
    Dim strFilter As String
 
    Set objOlook = CreateObject("Outlook.Application")
    Set objFolder = objOlook.GetNamespace("MAPI").GetDefaultFolder
    Set objOAppt = objFolder.Items
    Set objOItems = objOAppt.Item
 
 ' Set the Filter String to find Outlook Appointment
 
    appStart = goldDate & " " & goldStart ' Joins Date & Time together for OLook
    MsgBox (appStart)
    strFilter = "[Start]= " '" appStart & "'" & " And [Location]= " & "'" & goldLocation & "'" & " And [Subject]= " & "'" & goldName & "'"
 
 ' Find the outlook appointment based upon the above strFilter condition
 
    Set objOAppt = objOItems.Find(strFilter)
    MsgBox (strFilter)
    ' objOAppt.Delete
    '.Close (olSave)
    'Release the AppointmentItem object variable.
    Set objOAppt = Nothing
 
    'Release the Outlook object variable.
    Set objOlook = Nothing
    MsgBox (" Old Appointment Has been deleted in Outlook!")
    GoTo Add_New_Appointment
Exit Sub

Could you please advise how the outlook objects should look?

[NeoPa]

I'm still struggling to understand what you're asking for Cyd. Still no line numbers to refer to. However, my best guess is that you're asking about the code in lines #20 through #30, as these seem to declare and set the Outlook objects.

Lines #20 through #25 :
objOlook, objFolder, objOAppt, objOItems and strFilter all appear to be declared perfectly. appStart is declared as a Variant, no doubt in line with what's been said earlier, but the way it is assigned (Line #34) stops any possibility of its being used to determine if a value exists or not as a Null. Line #34 will always return a valid string - in all circumstances.

Lines #27 through #30 :
#27 and #28 look fine, but lines #29 and #30 appear to be assigning each other's values. IE They should probably read :

Set objOItems = objFolder.Items
Set objOAppt = objOItems(???)

Of course you probably don't know yet which item in objOItems() to select.

Line #36 (Bonus) :
It is not necessary to concatenate literal strings together using the format "xxx" & "yyy". The result is much easier and more logically achieved by simply using a single string "xxxyyy".

strFilter = "[Start]= '" & appStart & "' AND [Location]= '" & goldLocation & "' AND [Subject]= '" & goldName & "'"

[Cyd44]

Sorry again if I am confusing you. I am realy struggling with the logic of declaring Outlook objects when I want to find an appointment (rather than create one).

I do not understand how to declare the Set obj variables I am afraid.

I have connected successfully to create an appointment but do not know what I need to do to just open the calendar and find one.

Is there not a generic set of object setting to open appointments to find one.?

I suppose my question should be

How do you set the objects to connect to outlook in order to find an appointment to delete.? I have played around so much now I am totally confused. when I search the web I cannot find a diffinitive answer and have seen so many variations....non of which answer my basic problem.

I have noted where you suggest change and have done so but what would I set objOAppt to....I have not a clue I am afraid

[NeoPa]

It's so much easier to work with posts that are as clear as this one Cyd. Let's see what we can do with it.

First let's check out Application Automation for some of the basics. Frankly, your basics are already not too far off the mark, but it doesn't hurt to synchronise.

Assuming you're up on all the points from there then, your code seems to get the Outlook.Application object ok (Remember it is not visible at this stage). Next it gets the default MAPI folder (This will probably be the Inbox folder). If you want the Calendar folder specifically, then you can use either of :

Set objFolder = objOlook.GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar)
Set objFolder = objOlook.GetNamespace("MAPI").Folders("Calendar")

After that your new code is fine for setting the Items object but then I suspect you'd need to use its Find() method.

Set objOItems = objFolder.Items
Set objOAppt = objOItems.Find(strFilter)

Of course, your code doesn't currently have any strFilter set, so you should probably be looking to set it up first, to match your requirements. At this point I need to point you at the Context-Sensitive Help article again as you need to look for yourself. There is a (pretty large) list in there of the available fields that can be matched and it seems none of the values you are searching on have a match to any of the available fields.

[NeoPa]

It appears I misread the help page. The list was one of invalid properties to check rather than one of the only valid properties. Nevertheless, look through it yourself for most benefit. It gives some examples and how to avoid the more obvious errors.

NB. Choose (click on) Find method as it applies to the Items object.

[Cyd44]

Hi NeoPa,

Have looked at all of your recomendation and am still no wiser for my dilema I am afraid. I have already been successful in Creating an appointment on Outlook and there are many items on the web dealing with creation. However, I cannot find any items dealing specifically with deleting an appointment which I find strange as I would have thought this was as normal as Creating.

My Creation script does not bother with MAPI as we are on a single laptop. When I have tried to set MAPI variables for deletion it complains so I have left this out.

I have reached the stage where I have used the same 2 variables for Creating access to Outlook and Appointments as I have used when I add an appointment (the add works fine). This is the coding I have for the deletion now

 Dim objOlook As Outlook.Application
                Dim objOAppt As Outlook.AppointmentItem
                Dim objOItem As Outlook.AppointmentItem
                Dim appStart As Variant
                Dim strFilter As String
 
 
                Set objOlook = CreateObject("Outlook.Application")
                Set objOItem = objOlook.CreateItem(olAppointmentItem)
 
 ' Set the Filter String to find Outlook Appointment
 
                appStart = goldDate & " " & goldStart ' Joins Date & Time together for OLook
 
 
                strFilter = "[Start]= '" & appStart & "' AND [Location]= '" & goldLocation & "' AND [Subject]= '" & goldName & "'"
                If IsNull(strFilter) Then
                MsgBox ("No App")
                Else
                   If IsNull(objOAppt) Then
                   MsgBox ("No OL Appointement found")
                   Else
                   Set objOAppt = objOItem.Items.Find(strFilter)
                   'objOAppt.Delete
                   MsgBox (objOAppt)
                   End If
                End If
 

On running the code it complains at the
"Set objOAppt = objOItem.Items.Find(strFilter)" giving an error 438 - Object doesnt support this method or property

I have trapped the variables up to this line and they appear to be OK.

Would you have any ideas on this please. I still think it is my Syntax and, in particular, the use of the .Find statement. Had tried to use your suggestion earlier and it did not like this at all. The Context help does not really help I am afraid.

[ADezii]

Here is some that that I simplified and adapted to your general needs. It will DELETE All Outlook Appointments where the Subject = 'Corporate', the Location = 'HQ', and which occur after 11/15/2011. The Msgbox will then tell you how many Appointments were actually Deleted.

1. Set a Reference to the Microsoft Outlook X.X Object Library.
2. Execute the following Code substituting you own Values for Lines 11 to 13.
   

   Dim objOutlook As Outlook.Application
   Dim objNamespace As Outlook.NameSpace
   Dim objFolder As Outlook.MAPIFolder
   Dim objAppointment As Outlook.AppointmentItem
   Dim lngDeletedAppointements As Long
   Dim strSubject As String
   Dim strLocation As String
   Dim dteStartDate As Date
    
   '******************************** Set Criteria for DELETION here ********************************
   strSubject = "Corporate"
   strLocation = "HQ"
   dteStartDate = #11/15/2011#
   '************************************************************************************************
    
   Set objOutlook = Outlook.Application
   Set objNamespace = objOutlook.GetNamespace("MAPI")
   Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)
    
   For Each objAppointment In objFolder.Items
     If objAppointment.Subject = strSubject And objAppointment.Location = strLocation And _
        objAppointment.Start > dteStartDate Then
          objAppointment.Delete
            lngDeletedAppointements = lngDeletedAppointements + 1
     End If
   Next
    
   MsgBox lngDeletedAppointements & " appointment(s) DELETED.", vbInformation, "DETETE Appointments"

3. I'm sure that you will now be able to adapt it to your specific needs.

[Cyd44]

Hi ADezii

Once again, you are a star, this worked fine and did not find an appointment as I did not have one there. I am sure it will delete one once I have added it. Nonetheless your scipt proved spot on. I have been pulling my hair out over this as I could not work out the logic for the decalarations and the finding of an appointment that match.

I have set you as Best answer and thank you so much

[ADezii]

@Cyd44:
You are quite welcome. I actually pulled some Code off the Internet and modified it, so I do not deserve all the credit here. Glad it all worked out for you.

P.S. - NeoPa put much more time into this Thread than I did, he deserves the majority of the credit.

[Cyd44]

Oops Adezii, I spoke too soon. The script appears to look in the calendar but does not delete one that I created in line with the variables.
I suspect it something to do with the .Start Outlook variable. I have declared my dteStartDate as StartDate & " " & StartTime. I know that the variable comes back as (say) 04/10/2011 09:00:00 but I think that Outlook may be interpreting this in a different way because I have read somewhere that we need to supress seconds?
Is there a .Variable within Outlook for the Start Time or must we put Date & time together to creat .Start?

[ADezii]

'Extract the Date Component only
AND objApointment.Start = CDate(Format$(dteStartDate, "Short Date"))

[Cyd44]

Fraid not ADezii,

I am not getting any errors, have also tried taking out all reference to the .Start date and used just Subject & Location but it still comes back with Deleted 0 Appointments. This is crazy isnt it? Here is the code I am using which has been cahnged to the code you kindly gave me (I have made reference to .Start as a comment to see if this was the problem. Result of running the code is 0 Appointments deleted.

I have a calendar Appointment created for Room 9, Cyd and 4/10/2011 for 9;00 - 10:3O and a table appointment for the same room and name for 9AM to 10:30AM. I am trying to amend the booking so the above details are the details captured for the old booking and this appointment is to be deleted from outlook. It doesnt delet it however. With no error it is diffilcult to know what is wrong.

Delete_Old_Appointment:
 
' The Following variable assignments will pick-up the old booking data prior to the change in order
    ' to find and delete an Outlook booking. Variables have been caputured by the Sub Form_Current()procedure
    ' and declared Public in the basMyEmpID Standard Code Module.
 
            Dim goldLocation As String
            Dim goldStart As Date
            Dim goldDate As Date
            Dim goldName As String
 
            goldLocation = basMyEmpID.gstroldLocation
            goldStart = basMyEmpID.gdteoldStart
            goldDate = basMyEmpID.gdteoldDate
            goldName = basMyEmpID.gstroldName
 
 
 
' Connection to Outlook Variables
 
 
                Dim objOlook As Outlook.Application
                Dim objNamespace As Outlook.NameSpace
                Dim objFolder As Outlook.MAPIFolder
                Dim objAppointment As Outlook.AppointmentItem
                Dim objOAppt As Outlook.Items
                Dim lngDeletedAppointements As Long
                Dim strSubject As String
                Dim strLocation As String
                Dim dteStartDate As Date
 
'******************************** Set Criteria for DELETION here ********************************
                strSubject = goldName
                strLocation = goldLocation
                dteStartDate = goldDate & " " & goldStart ' goldDate is Short Date goldStart is medium time
 
                MsgBox (strSubject & dteStartDate & strLocation)
'************************************************************************************************
 
              Set objOlook = CreateObject("Outlook.Application")
             Set objNamespace = objOlook.GetNamespace("MAPI")
             Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)
             For Each objAppointment In objFolder.Items
 
 
            If objAppointment.Subject = strSubject And objAppointment.Location = strLocation Then _
            'objAppointment.Start = CDate(Format$(dteStartDate, "Short Date"))
 
 
 
             objAppointment.Delete
             lngDeletedAppointements = lngDeletedAppointements + 1
            End If
Next
 
MsgBox lngDeletedAppointements & " appointment(s) DELETED.", vbInformation, "DETETE Appointments"
 
'I 'm sure that you will now be able to adapt it to your specific
 
              GoTo Ender
              Exit Sub
 
 

[ADezii]

Add the following Debugging Code to verify Object and variable Values (Lines 2, 3, and 4):

For Each objAppointment In objFolder.Items
  Debug.Print "Subject: " & objAppointment.Subject & vbCrLf & "Location: " & _
               objAppointment.Location & vbCrLf & "Start: " & objAppointment.Start
  Debug.Print "Variable Values ==> " & " | " & strSubject & " | " & strLocation & " | " & dteStartDate
    If objAppointment.Subject = strSubject And objAppointment.Location = strLocation Then _
       objAppointment.Delete
         lngDeletedAppointements = lngDeletedAppointements + 1
    End If
  Debug.Print "************************************************************************"
Next

Sample OUTPUT:

Subject: Corporate
Location: HQ
Start: 12/26/2010
Variable Values ==>  | Corporate | HQ | 11/15/2011
*****************************************************
Subject: Corporate
Location: HQ
Start: 11/1/2011
Variable Values ==>  | Corporate | HQ | 11/15/2011
*****************************************************
Subject: Subject 1
Location: Location 1
Start: 11/18/2011
Variable Values ==>  | Corporate | HQ | 11/15/2011
*****************************************************
Subject: Subject 2
Location: Location 2
Start: 11/21/2011
Variable Values ==>  | Corporate | HQ | 11/15/2011
*****************************************************

[NeoPa]

@Cyd
It seems ADezii is helping you along now, which is fine. For me to proceed I would need you to junk most of what you'd done since my last post as I feel you have skipped important steps in the process of understanding what you're building. Trying out new things is fine, but skipping from one thing to another (which itself is not thoroughly tested and understood) within the thread of a logic process seems to me to be an unreliable approach. Only sound logic will lead you to your goal, and that does not include unfounded stabs in the dark. It can seem slow and laborious, but if you deviate from that path you often find yourself back at the start point.

I'll leave you in ADezii's very capable hands but I'll still monitor the thread in case I can help in any other ways.

[Cyd44]

Hi ADezii,

Panick over, I manged to get it working at last. I assigned the StartDate and Start time together outside of the If objAppointment statement and simply joined them as
dteStartDate = StartDate + StartTime then called them within the If stayemen as = dteStartDate. This worked like a treat.

I am sorry for all of the toing and froing, and NeoPa is quite correct, I was fumbling in the dark instead of standing back. The truth is, my problem was in understanding how to bind to Outlook properly to find and delete an appointment and there were no examples to be found anywhere.

I do thank you so much as your help as been invaluable.
I would also like to thank NeoPa and promise him I will think about my question in future before asking.

Thanks a bunch guys.

[NeoPa]

It's a long learning process Cyd, and you're clearly developing as time passes. Keep it up and I'll be asking you questions soon :-)

[mnorton123]

Have to agree, that's outstanding, very slick, works perfectly.