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
352 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Accepted Solution

by:
Alexei Kuznetsov earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

743 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

10 Experts available now in Live!

Get 1:1 Help Now