Solved

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

Posted on 2004-10-08
14
2,018 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
Comment Utility
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
Comment Utility
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
Comment Utility
Could you provide the code for me?

Thank you,
Cindy
0
 
LVL 1

Author Comment

by:cindyrod
Comment Utility
please help
0
 
LVL 13

Expert Comment

by:stefri
Comment Utility
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
Comment Utility
Is olCOunt supposed to be calCount?
0
 
LVL 1

Author Comment

by:cindyrod
Comment Utility
How do I change the date of the appointment? calEntries.start = #something#? ???
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:cindyrod
Comment Utility
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
Comment Utility
>>>>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
OK, it's working. How do I send the update now?
0
 
LVL 1

Author Comment

by:cindyrod
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
Outlook Free & Paid Tools
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now