Solved

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

Posted on 2004-10-08
14
2,097 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 500 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Find out what you should include to make the best professional email signature for your organization.
In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

778 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