Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How do I run a macro to have a Pop up window msg or Prompt only when I create a meeting from a specific Outlook Calendar (a shared calendar)?

Posted on 2014-07-29
9
381 Views
Last Modified: 2014-09-05
Hi I want to run this macro in VBA outlook (see below) but only in a specific share calendar folder. How do I do that?

The macro works perfectly but as it is, it runs in every calendar that I have on my computer, and I want this prompt only to pop up when I send the meeting from a shared calendar.

 Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
   
    Dim olkAppt As Outlook.AppointmentItem
    If Item.Class = olMeetingRequest Then
        Set olkAppt = Item.GetAssociatedAppointment(False)
        If olkAppt.Categories = "" Then
            If MsgBox("You didn't select a category.  Do you still want to submit the meeting request?", vbOK, "Categorize Meeting Request") = vbOK Then
                Cancel = True
                Item.ShowCategoriesDialog
            End If
        End If
    End If
    Set olkAppt = Nothing
End Sub

Thanks
0
Comment
Question by:MIA Alonso
  • 4
  • 3
9 Comments
 
LVL 14

Expert Comment

by:Alexei Kuznetsov
ID: 40227621
You can try to check olkAppt's Parent property like this:
If olkAppt.Parent.Name = "SharedCalendarName" Then
...
End If

Open in new window

Be aware that olkAppt.Parent may be Appointment again for recurrent appointments. In this case you need to check olkAppt.IsRecurring property and get olkAppt.Parent.Parent.
0
 

Author Comment

by:MIA Alonso
ID: 40229144
Not working...

The purpose of this is that each time a person creates an appointment in the "Sharedcalendar" before sending the appointment, if they did not include a category, that a meg pop ups and include one category.

With the code above I got the prompt but it also runs when I send an invite from my personal calendar.

With your suggestion when I run the code i got a bug in this line"If olkAppt.Parent.Name = "AAA Calendar".

The structure of my calendar is as shown below:

My Calendar
      Anna
      AAA Calendar

Shared Calendars
      AAA Calendar

And this is the code:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim olkAppt As Outlook.AppointmentItem

If olkAppt.Parent.Name = "AAA Calendar" Then
    If Item.Class = olMeetingRequest Then
        Set olkAppt = Item.GetAssociatedAppointment(False)
        If olkAppt.Categories = "" Then
            If MsgBox("You didn't select a category.  Do you still want to submit the meeting request?", vbOK, "Categorize Meeting Request") = vbOK Then
                Cancel = True
                Item.ShowCategoriesDialog
            End If
        End If
    End If
    Set olkAppt = Nothing

End If

End Sub
0
 
LVL 14

Expert Comment

by:Alexei Kuznetsov
ID: 40229200
I see. You have the same calendar name. So, you need to check if "AAA Calendar" is the shared calendar. You can do that by checking Store property on olkAppt.Parent like this:
If olkAppt.Parent.Store Is Nothing And olkAppt.Parent.Name = "AAA Calendar" Then
...

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:MIA Alonso
ID: 40229237
Thanks thims, it still not working.

It is not that it has the same calendar name, is that I am the full owner of the calendar so I see it in both folders.

This is the bug that I got:
"Run-time error'91':
Object variable or with block variable not set"

To be more precise this is how the folders looks:

My Calendars
      Calendar - anna@...
      Calendar - AAA_Calendar@...

Shared Calendars
      AAA Calendar


This is how the code looks:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
Dim olkAppt As Outlook.AppointmentItem

If olkAppt.Parent.Store Is Nothing And olkAppt.Parent.Name = "AAA Calendar" Then
    If Item.Class = olMeetingRequest Then
        Set olkAppt = Item.GetAssociatedAppointment(False)
        If olkAppt.Categories = "" Then
            If MsgBox("You didn't select a category.  Do you still want to submit the meeting request?", vbOK, "Categorize Meeting Request") = vbOK Then
                Cancel = True
                Item.ShowCategoriesDialog
            End If
        End If
    End If
    Set olkAppt = Nothing

End If

End Sub
0
 
LVL 14

Accepted Solution

by:
Alexei Kuznetsov earned 500 total points
ID: 40229311
You placed the "If" statement before olkAppt initialization. You need to move it below the "Set olkAppt = ..." line:
If Item.Class = olMeetingRequest Then
    Set olkAppt = Item.GetAssociatedAppointment(False)
    If olkAppt.Parent.Store Is Nothing And olkAppt.Parent.Name = "AAA Calendar" Then
    ...

Open in new window

0
 

Author Comment

by:MIA Alonso
ID: 40229748
Thanks thims,
Ok, Now I do not have a bug but when I create the  meeting in the calendar (and purposely do not include the category) the window won't pop up.... and will send the invite without the category.
0
 
LVL 14

Expert Comment

by:Alexei Kuznetsov
ID: 40231620
Maybe your calendar name is not exactly "AAA Calendar". To find its name, you can insert this line:
If Item.Class = olMeetingRequest Then
    Set olkAppt = Item.GetAssociatedAppointment(False)
    MsgBox olkAppt.Parent.Name
    ...

Open in new window

This will show the message box with exact calendar name. Use this name instead of "AAA Calendar".
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40305460
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
Find out what you should include to make the best professional email signature for your organization.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

789 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