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 GetdefaultFolde r 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....n on 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.Applica tion 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.