Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Macro in Outlook 2013

Posted on 2013-12-04
15
Medium Priority
?
879 Views
Last Modified: 2013-12-12
Hi

Approximately 3 years ago, a brilliant expert BlueDevilFan helped me with a Macro I needed for Outlook which worked with Outlook 2007 and Outlook 2010 althought it was originally written by him for Outlook 2007.

So here is the background of what it does right now.

A user has Outlook profile configured normally but has a few subfolders under the main calendar folder and these subfolders are also calender folders.

Now, the main calendar and the subfolders all contain appointments and many of them are recurring appointment. Many of the appointments are only for a few occurrences for example 5 or 10 or 20 etc.

When you normally do a search for any appointment in Outlook, if it matches a recurring appointment, it will only be shown as one result in the search result with the little circle with arrows over it suggesting it is a recurring appointment.

However, for the same recurring appointment, the user actually want to see individual occurrences of the appointment so that they can open any particular one directly from the search result.

So the macro is used this way. Once the search result shows the recurring appointment entry, you highlight on it and run the macro. The Macro will bring up a box showing the various occurrences of the appointment that was selected and from within that box, you can select any particular occurrence and double click on it to open just that one particular occurrence of the appointment.

I have attached the files I was given which I simply had to import in Outlook.

The problem I have is that the code is not working in Outlook 2013 and I need it working in Outlook 2013.

Ask me any questions you have about this as I know this is quite complex.
ShowRecurring.zip
0
Comment
Question by:alex110109
[X]
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
  • 9
  • 6
15 Comments
 

Author Comment

by:alex110109
ID: 39695880
Also, here is the link to the question from 3 years ago.

http://www.experts-exchange.com/Software/Internet_Email/Email/Email_Clients/Q_26229789.html

I am sorry its a long one but you know how things go when you are doing things like this.

I hope genius Mr BlueDevilFan himself can help me again !!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39697859
Hi, alex110109.

I tried the solution and got the same error.  I traced it to the ShowRecurring subroutine.  It looks like two lines of code have typos.  Those two lines are

    strSubject = Application.ActiveExplorer.Selection(1)

and
   
    strSubject = Application.ActiveInspector.CurrentItem

They should read

    strSubject = Application.ActiveExplorer.Selection(1).Subject

and

    strSubject = Application.ActiveInspector.CurrentItem.Subject

I looked back at the original question and see that they're wrong there too.  I'll see what I can do about fixing them for anyone else who reads this question.

Once I corrected them, then code worked fine.
0
 

Author Comment

by:alex110109
ID: 39697889
Hi Blue

I will try this and get back to you asap.

I am out of the office today so I will check it tomorrow morning.

Thanks again for your help.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 76

Expert Comment

by:David Lee
ID: 39697922
No worries.  You're welcome!
0
 

Author Comment

by:alex110109
ID: 39701617
Hi Blue

I think we are almost there but in the test I did, its still coming up with an error.

We have certainly made progress compared to how it was before.

So I have attached a zip file with screenshots which are named in order.

If you follow the files in that order, you see what happens.

Is there unnecessary code in the frmShowRecurring form?

The first three or four lines I mean?
0
 

Author Comment

by:alex110109
ID: 39701622
oops...as usual, forgot the attachment.
macro.zip
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39708178
How did that one line of code end up outside of a subroutine?  Delete it and try running the macro again.
0
 

Author Comment

by:alex110109
ID: 39708681
Hi Blue

Thanks for that.

I did it and still got an error which I think is different from the other one.

May be I didn't delete the right entry.

See the error message and the code.
macro7.png
0
 

Author Comment

by:alex110109
ID: 39708686
Or, may be you can export the code from your end as it is working fine for you?

Whichever way is easier for you.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39708927
You need to delete the line that has the highlight on it.  The one after the End Sub line.
0
 

Author Comment

by:alex110109
ID: 39708976
Hi Blue

Sorry to be a pain but now there is a dialogue box with just the word "error" in the title and nothing in the box itself. You can see it in the attached screenshot.

I have also attached a text file containing the current code, in case I have deleted the wrong line compared to the code you have working on your end.

The code is for "frmShowRecurring" form.

Interestingly though, this time when you click on Ok to the error message, it doesn't open the Visual Basic editor like it used to do with errors before.
macro8.png
code.txt
0
 
LVL 76

Accepted Solution

by:
David Lee earned 2000 total points
ID: 39711209
No worries.

Here's the code as I have it.  It's working for me.

Dim strSubject As String

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdOpen_Click()
    Dim olkCal As Outlook.Items, _
        olkApt As Outlook.AppointmentItem, _
        olkFld As Outlook.MAPIFolder, _
        arrTemp As Variant, _
        datSelected As Date
    arrTemp = Split(Me.lstInstances.List(Me.lstInstances.ListIndex), vbTab)
    datSelected = arrTemp(1)
    Set olkFld = Application.ActiveExplorer.CurrentFolder
    Set olkCal = olkFld.Items
    olkCal.Sort "Start"
    olkCal.IncludeRecurrences = True
    Set olkApt = olkCal.Find("[Subject] = '" & strSubject & "' AND [Start] = '" & Format(datSelected, "ddddd h:nn AMPM") & "'")
    If TypeName(olkApt) = "Nothing" Then
        MsgBox "Error"
    Else
        Me.Hide
        olkApt.Display
    End If
    Set olkCal = Nothing
    Set olkApt = Nothing
    Set olkFld = Nothing
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    ShowRecurring
End Sub

Sub ShowRecurring()
    Dim olkCal As Outlook.Items, _
        olkApt As Outlook.AppointmentItem, _
        olkFld As Outlook.MAPIFolder, _
        intCounter As Integer
    intCounter = 1
    Select Case TypeName(Application.ActiveWindow)
        Case "Explorer"
            strSubject = Application.ActiveExplorer.Selection(1).Subject
        Case "Inspector"
            strSubject = Application.ActiveInspector.CurrentItem.Subject
    End Select
    Set olkFld = Application.ActiveExplorer.CurrentFolder
    Set olkCal = olkFld.Items.Restrict("[Subject] = '" & strSubject & "'")
    olkCal.Sort "Start"
    olkCal.IncludeRecurrences = True
    For Each olkApt In olkCal
        Me.lstInstances.AddItem intCounter & vbTab & olkApt.Start
        intCounter = intCounter + 1
        If intCounter > 50 Then Exit For
    Next
    Set olkFld = Nothing
    Set olkCal = Nothing
    Set olkApt = Nothing
End Sub

Open in new window

0
 

Author Comment

by:alex110109
ID: 39713702
Mr BlueDevilFan

You are a star as always.

Got it working although on some appointments it still comes up with the same error.

But that doesn't matter because we only need to see the list of occurrences and if a particular occurrence opens up, that's a bonus !!

Can't thank you enough !!
0
 

Author Closing Comment

by:alex110109
ID: 39713783
Excellent help provided !!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39713864
Thanks, alex110109, and you're welcome.  I'm glad I was able to help.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 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