amend or delete appointment from outlook using access

[philwalker531]

Thanks for looking at this and i hope someone can help.

I have a fairly basic database using one table - from a main form i can create an appointment in outlook using event date and event time and unique ref number. ( i got the code from this site as i have no programming experience to speak of!)

the appointment creating works perfectly however i have now found that when i need to change details on the database i need to reflect the changes in outlook calendar. i was hoping using someones code i could delete the original appointment and replace it with the new edited/amended one. is this possible?

i have tried several examples from the internet but none seem to work with what i have already and i do not know enough ( anything!) to change the examples to make it work with my database.

here is the code i used for the appointment creator if this means anything!? thanks

Private Sub btnAddApptToOutlook_Click()

' Save the Current Record
If Me.Dirty Then
Me.Dirty = False
End If

' Exit the procedure if appointment has been added to Outlook.
If Me.chkaddedtooutlook = True Then
MsgBox "This appointment has already added to Microsoft Outlook.", vbCritical
Exit Sub

Else
' Add a new appointment.
' Use late binding to avoid the "Reference" issue
Dim olApp As Object 'Outlook.Application
Dim olappt As Object 'olAppointmentItem

' This is how we would do it if we were using "early binding":
' Dim outobj As Outlook.Application
' Dim outappt As Outlook.AppointmentItem
' Set outobj = CreateObject("Outlook.Application")
' Set outappt = outobj.CreateItem(olAppointmentItem)

If isAppThere("Outlook.Application") = False Then
' Outlook is not open, create a new instance
Set olApp = CreateObject("Outlook.Application")
Else
' Outlook is already open--use this method
Set olApp = GetObject(, "Outlook.Application")
End If

' Create the New Appointment Item
Set olappt = olApp.createitem(1) ' olAppointmentItem = 1

' Add the Form data to the Appointment Properties
With olappt
' If There is no Start Date or Time on
' the Form use Nz to avoid an error

' Set the Start Property Value
.Start = Nz(Me.Event_date, "") & " " & Nz(Me.Event_time, "")


.duration = Nz(180, 0)

' vbNullString uses a little less memory than ""
.subject = Nz(Me.Venue, "vbnullstring") & " suite " & Nz(Me.Suite, vbNullString) & " REF " & Nz(Me.Reference, vbNullString) & " " & Nz(Me.Bride, vbNullString)

.body = Nz(Me.Chair_covers, vbNullString) & " " & Nz(Me.Description, vbNullString) & " --------(table linen)-------- " & Nz(Me.Description_2, vbNullString) & " --------- Additional Items ----------- " & Nz(Me.Description3, vbNullString) & " other info " & Nz(Me.Other_inf, vbNullString) & " address " & Nz(Me.Address_line1, vbNullString) & " " & Nz(Me.Address_line2, vbNullString) & " " & Nz(Me.Town, vbNullString) & " " & Nz(Me.Postcode, vbNullString) & " tel nos " & Nz(Me.Tel_no, vbNullString) & " " & Nz(Me.Alt_tel_no, vbNullString)
.Location = Nz(Me.Venue, vbNullString) & " " & Nz(Me.Suite, vbNullString)


' Save the Appointment Item Properties
.Save
End With
End If

' Release the Outlook object variables.
Set olappt = Nothing
Set olApp = Nothing

' Set chkAddedToOutlook to checked
Me.chkaddedtooutlook = True

' Save the Current Record because we checked chkAddedToOutlook
If Me.Dirty Then
Me.Dirty = False
End If

' Inform the user
MsgBox "Appointment Added!", vbInformation

End Sub

 

[darbid]

( i got the code from this site as i have no programming experience to speak of!)

I didn't either about 18 months ago. Welcome to a steep learning curve.

i was hoping using someones code i could delete the original appointment and replace it with the new edited/amended one. is this possible?

Yes you can.

I have not checked your code below as I assume that it is working. What you need to do is set a unique ID number in your appointment so that you can find it again. What I do is add the recordID from my access table to the appointmentItem. Many people use the mileage field.

        With olappt
                .Mileage = recordID
        End With

Then to find this AppointmentItem again you use the "Find" method.

objFolder will need to be your calendar object either your default calendar or a shared one eg below I am showing how to get a shared folder. Here strName is someones email address. You most likely only need to get the defaultcalendar.

'add your code for outlook to be objApp
Set objNS = objApp.GetNamespace("MAPI")

Set objRecip = objNS.CreateRecipient(strName)
Set objFolder = objNS.GetSharedDefaultFolder(objRecip, olFolderCalendar)

sFilter = "[Mileage] = " & recordID & ""

If Not objFolder Is Nothing Then

    Set objAppt = objFolder.Items.Find(sFilter)

    If objAppt Is Nothing Then
        MsgBox "appointment not found"
    Else
        With objAppt
           'here you can get or change any of the properties of the appointmentItem
        end with
    End if

else
  msgbox "problem getting the calendar"
End if

 

[philwalker531]

thanks for that, will have a play.

do i need to add a separate button for the 'find' function?

does it 'find' it in outlook ie does it open the appointment to edit it there? or is it edited through access?

the mileage property entry do i put that in my original code or do i put it as a separate entry?

i only need the default calendar not a shared one. thanks

sorry for being dim!

 

[darbid]

thanks for that, will have a play.

That is a good way to learn.

do i need to add a separate button for the 'find' function?

That depends on how when and why you need to change an original appointment that someone originally added. The way I do it is all my dates of an appointment that has been made are in a combolist. When a user chooses a date - I then run a quick check to see if I can find the appointmentitem. If it finds it then I make a button visible to delete and/or to change.

does it 'find' it in outlook ie does it open the appointment to edit it there? or is it edited through access?

what i gave you could be done without the users ever seeing outlook. With microsofts stupid knee jerk reaction to viruses there are stupid security windows you will get in Office 2003 when you go to save and thus resend an update to the appointment. You will also face these if you try and read the recipients or body of the appointment item you will get it as well.

the mileage property entry do i put that in my original code or do i put it as a separate entry?

yes you have to add it to your original code so that your unique ID is saved in the appointment item.

i only need the default calendar not a shared one. thanks

Everyone has code on how to get that so just do a little google. It is not much different to what I have given.

by the way if you are really learning VBA you are taking on a dam hard thing. Making the appointments is one thing, but then making them editable from access again is not easy. Good Luck.

 

[philwalker531]

Thanks for all your help, shame you don't live down the road, you sound as though you know what you are doing!!

will give it a blast and see how we get on

 

[pflipper]

Hi all,

I found this thread, and it was a big help in figuring out how to delete appointments. I would have been digging for quite a while to come up with the use of the mileage property, but it works like a charm.

Almost...

That's why I'm here... I've have some additional functionality in my code that allows does not seem to allow the mileage property to be utilized. Here's what I do: I created code in my app that allows people to set an appointment for something. If, however, they are processing an item for someone else (i.e. they are not the lead salesperson for that client), I've created a process that allows the person editing the record to generate and email to send to the lead salesperson, and this email includes an .ics file of that appointment that the lead can download into their calendar.

Problem... I find, though, that when the .ics file is created and emailed to the person, it does not contain the mileage field that can be used for later deletion of this appointment.

Is this expected functionality when using an .ics file? Is there another type of file that I can attach that will utilize the mileage property, yet allow the appointment to be saved by the lead salesperson?

Thanks for any advice!

 

[darbid]

Problem... I find, though, that when the .ics file is created and emailed to the person, it does not contain the mileage field that can be used for later deletion of this appointment.

Hi. An .ics file is part of the iCalendar standard which you can read here - http://en.wikipedia.org/wiki/ICalendar. Please please check yourself but I am of the opinion that the standard does not support this Mileage field (compenent property), but you can now also check. Plus even if the standard did, Outlook does not fully support the standard anyway. You could try it with a "X-mileage". Which is 3.8.8.2 of the standard here http://tools.ietf.org/html/rfc5545

As far as I know there is no other way of "file sending" available. You have a number of second best ideas.

1. Shared calendars - one user can see and make appointments for another person's calendar.
2. Make your own .ics type system. Create some VBA that watches the emails that come in and in particular the mileage field. You could then put a special code in the mileage field of these emails. This would then set in process on the receivers computer some code which reads the email and makes the calendar item. The best way to do this would be just like you currently are making a text file with the code and calling it an .ics file. You could create your own file format like a "invite.phil", then in Outlook VBA it sees the .phil attachement and parses all the information for a new calendar item.
3. Do you have a database as well. The maker makes the appointment in access for the receiver. As soon as it is made, the database then makes the calendar on the receivers computer. (I use this one, but if I had my time again I might use 2).

I will help you if you like the above, just ask specific questions.

 

[pflipper]

Thanks for the feedback, darbid,

These are some great ideas. I'm away from my main office at this moment, so I'm not working on my app today, but I'll be back at in on Monday, take a full read of your suggestions, and let you know what works or what I decide to do.

Appreciate the help!

 

[Maverex]

I didn't either about 18 months ago. Welcome to a steep learning curve.


Yes you can.

I have not checked your code below as I assume that it is working. What you need to do is set a unique ID number in your appointment so that you can find it again. What I do is add the recordID from my access table to the appointmentItem. Many people use the mileage field.

        With olappt
                .Mileage = recordID
        End With

Then to find this AppointmentItem again you use the "Find" method.

objFolder will need to be your calendar object either your default calendar or a shared one eg below I am showing how to get a shared folder. Here strName is someones email address. You most likely only need to get the defaultcalendar.

'add your code for outlook to be objApp
Set objNS = objApp.GetNamespace("MAPI")
 
Set objRecip = objNS.CreateRecipient(strName)
Set objFolder = objNS.GetSharedDefaultFolder(objRecip, olFolderCalendar)
 
sFilter = "[Mileage] = " & recordID & ""
 
If Not objFolder Is Nothing Then
 
    Set objAppt = objFolder.Items.Find(sFilter)
 
    If objAppt Is Nothing Then
        MsgBox "appointment not found"
    Else
        With objAppt
           'here you can get or change any of the properties of the appointmentItem
        end with
    End if
 
else
  msgbox "problem getting the calendar"
End if

Thank you for this piece of code. But i am stuck. Since English is not my native language i do not understand what you mean by : 'add your code for outlook to be objApp

What code do you mean?
I have looked through the code on this page but i cannot figure this out.

 

[darbid]

Since English is not my native language i do not understand what you mean by : 'add your code for outlook to be objApp.

I am not sure that is a language problem. Other people also my not understand it.

What I mean is that you need to set objApp to be your main Outlook.Application object.

 

[Maverex]

OK....and how would i do that?

(This is my first attempt to interact with software outside access.)

Thanks for the fast reply btw.

 

[darbid]

OK....and how would i do that?

(This is my first attempt to interact with software outside access.)

Thanks for the fast reply btw.

Have a look at the first post http://www.access-programmers.co.uk/forums/showpost.php?p=935336&postcount=1

you can see in there an example of this (he talks about a concept of early and late binding). It is also a good idea to do a little bit of looking around, non of this thread is suitable for a cut and copy job, you are going to have to understand things at some time.

 

[Maverex]

I am trying to Darbid.

thanks for the quick responses.

I will look in to it some more.

Thanks for the help