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
360 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 13

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 13

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
 

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

 
LVL 13

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 13

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Cannot expand the folder in Outlook 6 54
Hide vba in gp 7 80
vbModal 12 31
Changing settings for Outlook from NTLM to Negotiate. Are there any pitfalls? 2 49
What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
If you don't know how to downgrade, my instructions below should be helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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: …

914 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

21 Experts available now in Live!

Get 1:1 Help Now