cindyrod
asked on
VBA Code to Update a Meeting Request (Appointment) and Send Update to All Attendees
I have an Access application where a user can enter information for a new meeting (deadline really). They enter the subject, the due date and the due time, and then the VBA code creates an appointment in their calendar in Outlook and sends it to all the attendees.
One of the users may receive notification that the deadline has changed, so the user will need to be able to change the due date and/or time within the Access application and then have some VBA code update the appointment in Outlook and send the update to all the attendees. How would I do this last part (update date/time for an existing meeting request and send update to attendees)? Please write full code and explain.
Thank you,
Cindy
One of the users may receive notification that the deadline has changed, so the user will need to be able to change the due date and/or time within the Access application and then have some VBA code update the appointment in Outlook and send the update to all the attendees. How would I do this last part (update date/time for an existing meeting request and send update to attendees)? Please write full code and explain.
Thank you,
Cindy
I am afraid that only the meeting request organizer is able to send a meeting request update.
Apart this consideration, theoritically you have to (if hte organizer sends the update:
look for the user's Calender folder
use folder items search using Restrict/find method using start, end time and if possible subject to get the appointment item
modify the start/end timing then send update
As a workaround, if the user is not the organizer, you can look for the appointement item, look for Organizer property. If the current user is not the organizer, send organizer a standard mail requesting a meeting change.
Stefri
Apart this consideration, theoritically you have to (if hte organizer sends the update:
look for the user's Calender folder
use folder items search using Restrict/find method using start, end time and if possible subject to get the appointment item
modify the start/end timing then send update
As a workaround, if the user is not the organizer, you can look for the appointement item, look for Organizer property. If the current user is not the organizer, send organizer a standard mail requesting a meeting change.
Stefri
ASKER
Could you provide the code for me?
Thank you,
Cindy
Thank you,
Cindy
ASKER
please help
May be this bit of code will help you
I have created an appointment starting today at 8:00 PM ending today at 9:30PM with calTest as Subject
calDates is a dummy function to pass args to lokkForCal sub with starting, ending and subject appointment
getCalEntries returns a collection of calendar entries (should be one entry only)
Sub calDates()
Dim st As Date
Dim en As Date
st = #10/11/2004 8:00:00 PM# ' this is today according to my locale
en = #10/11/2004 9:30:00 PM#
lookForCal st, en, "calTest"
End Sub
Sub lookForCal(startD As Variant, endD As Variant, subj As String)
Dim calFldr As MAPIFolder
Dim calEntries As Object
Dim objNS As NameSpace
Dim calN As Integer
Dim strRes As String
Dim st As String
Dim en As String
st = Format(startD, "dddddd Hh:Mm")
en = Format(endD, "dddddd Hh:Mm")
strRes = "[Start] >=" & addQuotes(st) & " and [End] <=" & addQuotes(en) & " and [Subject] =" & subj
Set objNS = Application.GetNamespace("
Set calFldr = objNS.GetDefaultFolder(olF
Set calEntries = getCalEntries(calFldr, strRes, calN)
End Sub
Function addQuotes(data)
addQuotes = Chr(34) & data & Chr(34)
End Function
Function getCalEntries(theFolder, strRestrict, calCount)
Dim i
Dim itmCount
Dim messageClass
Dim theItems
Dim localItms, restrictItems
olCOunt = 0
Set theItems = theFolder.Items
Set restrictItems = theItems.Restrict(strRestr
Set localItms = restrictItems
itmCount = restrictItems.Count
If itmCount > 0 Then
For i = 1 To itmCount
messageClass = localItms(i).messageClass
If InStr(1, messageClass, "IPM.Appointment", 1) <> 0 Then
calCount = calCount + 1
End If
Next
End If
Set getCalEntries = restrictItems
End Function
As working with Restrict Method is very touchy when dates are involved, I suggest you to go to http://www.outlookcode.com/d/finddate.htm
Stefri
I have created an appointment starting today at 8:00 PM ending today at 9:30PM with calTest as Subject
calDates is a dummy function to pass args to lokkForCal sub with starting, ending and subject appointment
getCalEntries returns a collection of calendar entries (should be one entry only)
Sub calDates()
Dim st As Date
Dim en As Date
st = #10/11/2004 8:00:00 PM# ' this is today according to my locale
en = #10/11/2004 9:30:00 PM#
lookForCal st, en, "calTest"
End Sub
Sub lookForCal(startD As Variant, endD As Variant, subj As String)
Dim calFldr As MAPIFolder
Dim calEntries As Object
Dim objNS As NameSpace
Dim calN As Integer
Dim strRes As String
Dim st As String
Dim en As String
st = Format(startD, "dddddd Hh:Mm")
en = Format(endD, "dddddd Hh:Mm")
strRes = "[Start] >=" & addQuotes(st) & " and [End] <=" & addQuotes(en) & " and [Subject] =" & subj
Set objNS = Application.GetNamespace("
Set calFldr = objNS.GetDefaultFolder(olF
Set calEntries = getCalEntries(calFldr, strRes, calN)
End Sub
Function addQuotes(data)
addQuotes = Chr(34) & data & Chr(34)
End Function
Function getCalEntries(theFolder, strRestrict, calCount)
Dim i
Dim itmCount
Dim messageClass
Dim theItems
Dim localItms, restrictItems
olCOunt = 0
Set theItems = theFolder.Items
Set restrictItems = theItems.Restrict(strRestr
Set localItms = restrictItems
itmCount = restrictItems.Count
If itmCount > 0 Then
For i = 1 To itmCount
messageClass = localItms(i).messageClass
If InStr(1, messageClass, "IPM.Appointment", 1) <> 0 Then
calCount = calCount + 1
End If
Next
End If
Set getCalEntries = restrictItems
End Function
As working with Restrict Method is very touchy when dates are involved, I suggest you to go to http://www.outlookcode.com/d/finddate.htm
Stefri
ASKER
Is olCOunt supposed to be calCount?
ASKER
How do I change the date of the appointment? calEntries.start = #something#? ???
ASKER
I used:
For Each myItem In calEntries
myItem.start = Me.Bid_Due_Date.value
Next
but nothing is happenning. I'm not getting any error messages, but the appointment item is still in the same place (date).
For Each myItem In calEntries
myItem.start = Me.Bid_Due_Date.value
Next
but nothing is happenning. I'm not getting any error messages, but the appointment item is still in the same place (date).
>>>>> Is olCOunt supposed to be calCount?
Yes
>>>>>but the appointment item is still in the same place (date).
You have to save them....
For Each myItem In calEntries
myItem.start = Me.Bid_Due_Date.value
' if the date is valid....save the myItem
myItem.save
Next
Yes
>>>>>but the appointment item is still in the same place (date).
You have to save them....
For Each myItem In calEntries
myItem.start = Me.Bid_Due_Date.value
' if the date is valid....save the myItem
myItem.save
Next
ASKER
The code worked fine until I changed the subject to:
subject = "new item: " & Me.[Item Name].Value
Then I got the following error:
Unable to parse condition. Error at "".
When I clicked "Debug," the following line was highlighted:
Set restrictItems = theItems.Restrict(strRestr
I tried changing the subject to:
subject = "new item: " then it worked fine.
I tried changing the subject to:
subject = "new item: the hard coded name of the item" and I got the same error.
I have no idea what's wrong. Is there a limit of words or something when using the restrict method?
subject = "new item: " & Me.[Item Name].Value
Then I got the following error:
Unable to parse condition. Error at "".
When I clicked "Debug," the following line was highlighted:
Set restrictItems = theItems.Restrict(strRestr
I tried changing the subject to:
subject = "new item: " then it worked fine.
I tried changing the subject to:
subject = "new item: the hard coded name of the item" and I got the same error.
I have no idea what's wrong. Is there a limit of words or something when using the restrict method?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, it's working. How do I send the update now?
ASKER
Also,
1) How do I test if the user is organizer or not?
2) How do I send a "propose new time" message if user is not organizer? Does this have to be done manually?
I really appreciate all of your help. Although I've worked for about 4-5 months with VBA in Access, the objects in Outlook are just weird to me (I just need to learn the logic, that's all :-D). Your help is worth to me more than 500 points (or 2000 really because you're definitely getting an A).
1) How do I test if the user is organizer or not?
2) How do I send a "propose new time" message if user is not organizer? Does this have to be done manually?
I really appreciate all of your help. Although I've worked for about 4-5 months with VBA in Access, the objects in Outlook are just weird to me (I just need to learn the logic, that's all :-D). Your help is worth to me more than 500 points (or 2000 really because you're definitely getting an A).
CurrentUSer: this is available in
Set myNameSpace = Application.GetNameSpace("
MsgBox myNameSpace.CurrentUser.Na
THE organizer is in calentries.item(1).Organiz
Returns a String representing the name of the organizer of the appointment. Read-only.
expression.Organizer
expression Required. An expression that returns an AppointmentItem object.
it is just a question of testing CurrentUser.Name against Organizer: it could fire the Outlook security popup: a program is trying to access your addresbook....
I hope some other experts have ideas and they participate to this thread as the only idea I had was to create a standard mail to be sent to the original organizer with the new schedule, he will then update manually the meeting..
Not really elegant...
About Outlook Model, just go to VBA help and Object Browser (as in Access): well done and well documented, otherwise many books available. Also www.slipstick.com and its developper counterpart www.outlookcode.com is a very interesting source of information and examples.
To get rid of Outlook Security warning, visit http://www.dimastr.com/redemption/home.htm (this a must have)
Stefri
Set myNameSpace = Application.GetNameSpace("
MsgBox myNameSpace.CurrentUser.Na
THE organizer is in calentries.item(1).Organiz
Returns a String representing the name of the organizer of the appointment. Read-only.
expression.Organizer
expression Required. An expression that returns an AppointmentItem object.
it is just a question of testing CurrentUser.Name against Organizer: it could fire the Outlook security popup: a program is trying to access your addresbook....
I hope some other experts have ideas and they participate to this thread as the only idea I had was to create a standard mail to be sent to the original organizer with the new schedule, he will then update manually the meeting..
Not really elegant...
About Outlook Model, just go to VBA help and Object Browser (as in Access): well done and well documented, otherwise many books available. Also www.slipstick.com and its developper counterpart www.outlookcode.com is a very interesting source of information and examples.
To get rid of Outlook Security warning, visit http://www.dimastr.com/redemption/home.htm (this a must have)
Stefri
ASKER
Please help.
Thanks,
Cindy