[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Code to Update a Meeting Request (Appointment) and Send Update to All Attendees

Posted on 2004-10-08
14
Medium Priority
?
2,498 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:cindyrod
  • 9
  • 5
14 Comments
 
LVL 1

Author Comment

by:cindyrod
ID: 12258520
I guess I will need to get a hold of the appointment object (a pointer or something like that) and then update it. Outlook should ask to send an update to the attendees or maybe there's some code to send the update.

Please help.

Thanks,
Cindy
0
 
LVL 13

Expert Comment

by:stefri
ID: 12260950
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
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12275219
Could you provide the code for me?

Thank you,
Cindy
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 1

Author Comment

by:cindyrod
ID: 12278291
please help
0
 
LVL 13

Expert Comment

by:stefri
ID: 12279847
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("MAPI")
  Set calFldr = objNS.GetDefaultFolder(olFolderCalendar)
  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(strRestrict)
    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
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12289474
Is olCOunt supposed to be calCount?
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12289660
How do I change the date of the appointment? calEntries.start = #something#? ???
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12290168
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).
0
 
LVL 13

Expert Comment

by:stefri
ID: 12290503
>>>>> 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
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12297162
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(strRestrict)

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?
0
 
LVL 13

Accepted Solution

by:
stefri earned 2000 total points
ID: 12300983
Mods are tagged with                 '<<<<<<<<<
Sub calDates()

Dim st As Date
Dim en As Date
st = #10/11/2004 8:00:00 PM#

en = #10/11/2004 9:30:00 PM#
lookForCal st, en, "calTest: this is a test"                '<<<<<<<<<

End Sub
Sub lookForCal(startD As Variant, endD As Variant, subj As String)
Dim calFldr As MAPIFolder
Dim calEntries As Object
Dim calN As Integer
Dim strRes As String
Dim st As String
Dim en As String
Dim mySubject As String                 '<<<<<<<<<

st = Format(startD, "dddddd Hh:Mm")
en = Format(endD, "dddddd Hh:Mm")
mySubject = addQuotes(subj)                  '<<<<<<<<<
strRes = "[Start] >=" & addQuotes(st) & " and [End] <=" & addQuotes(en) & " and [Subject] =" & mySubject                  '<<<<<<<<<
  Dim objNS As NameSpace
  Set objNS = Application.GetNamespace("MAPI")
  Set calFldr = objNS.GetDefaultFolder(olFolderCalendar)
  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

    calCount = 0

    Set theItems = theFolder.Items
    Set restrictItems = theItems.Restrict(strRestrict)
    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
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12311825
OK, it's working. How do I send the update now?
0
 
LVL 1

Author Comment

by:cindyrod
ID: 12312183
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).
0
 
LVL 13

Expert Comment

by:stefri
ID: 12323485
CurrentUSer: this is available in
Set myNameSpace = Application.GetNameSpace("MAPI")
MsgBox myNameSpace.CurrentUser.Name

THE organizer is in calentries.item(1).Organizer
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below errors for MS Exchange Server 2016 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
Today as you open your Outlook, you witness an error message: “Outlook is using an old copy of your Outlook Data File…”. Probably, Outlook is accessing an old OST file.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question