Solved

Why are Sticky Notes causing a problem with my VB Code in Microsoft Outlook?

Posted on 2014-10-29
27
390 Views
Last Modified: 2014-11-13
I have a chunk of VB code that I use in Microsoft Outlook that is solely to recognize when a new Appointment Item is opened and, if so, the code prompts me to ask if I want to create an Agenda for the meeting.  The code should only activate when I open a new Appointment Item from my calendar.

Currently, it does not execute when: (1) a new/existing E-mail item is opened, (2) a new/existing Task is opened, (3) a new/existing Contact is opened, (4) an existing Calendar item is opened.

However, it does execute when a new Calendar item is opened.  

Up to this point, everything is great and it is doing exactly what I want it to!

But, here is the rub... for some reason it tries to execute whenever a new or existing Sticky Note is opened.  When I open a Sticky Note, it also hits me with a Run Time error 91:Object Variable or With Block variable not set.  

Here is the start of my code, with a remark on the line that is highlighted when I get the error:

 
Can someone give me any suggestions on how to keep this code from executing when a new or existing Sticky Note is opened?  Thanks!
0
Comment
Question by:jmcclosk
  • 18
  • 9
27 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40428614
Here is the start of my code, with a remark on the line that is highlighted when I get the error:

um,
Where is the code?
0
 

Author Comment

by:jmcclosk
ID: 40428670
Sorry.  Looks like the code may have been removed when the Admin reassigned it.  Here is what I originally had:

Public WithEvents m_Inspectors As Outlook.Inspectors
Public WithEvents m_Inspector As Outlook.Inspector

Private Sub Application_Startup()
  Set m_Inspectors = Application.Inspectors
End Sub

Private Sub m_Inspectors_NewInspector(ByVal Inspector As Outlook.Inspector)
  Set m_Inspector = Inspector
End Sub

Private Sub m_Inspector_Activate()

Dim objItem As Object

Set oContact = objItem
Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
Set objSelection = objApp.ActiveExplorer.Selection

Dim objWord As Word.Application   ' This is where the error occurs
Dim objDoc As Word.Document
Dim objSel As Word.Selection

Set objDoc = m_Inspector.WordEditor
Set objWord = objDoc.Application
Set objSel = objWord.Selection

If (TypeName(m_Inspector.CurrentItem) = "AppointmentItem") And (m_Inspector.CurrentItem.Body = "") Then

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40428711
Ok,
I'm trying to get your code going, to blow it up in similar fashion to you, but I can't get it to compile
Private Sub m_Inspector_Activate()

Dim objItem As Object

Set oContact = objItem


What is missing after Dim objItem As Object?
You've Dimmed it, but you haven't set it prior to setting oContact equal to it.
What's missing in what you posted?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40428746
And really, nothing in the code you posted so far shows how it detects if a NEW appointment item is being opened.
Define NEW
And define how the code detects newness.

The folks at slipstick.com are very good at this kind of thing
Have a look at their code here
http://www.slipstick.com/developer/copy-new-appointments-to-another-calendar-using-vba/

You seem to be doing something else, but the code isn't complete.
You are trapping when an Inspector object is activated.
You'd then need to check if the item the Inspector is displaying is an olAppoinmentItem, and then see if it is 'new'
and THEN do something.

None of that code is posted yet though!
0
 

Author Comment

by:jmcclosk
ID: 40428778
Well, there is nothing missing.  I pasted the code exactly as I have it in Outlook.  However, after you brought that to my attention, I searched through the code to find where this was being used and I think these two lines were absent-mindedly left over from early stages of developing this script.  I can't find anywhere they are used and, when I rem them out, the code still works exactly the same.  Sorry.  Sometimes it takes another set of eyes to call out my sloppiness.  

One other note that may be a concern for you.  I had to add the reference under the Tools menu for Microsoft Word 12.0 Object Library.  You probably already know this.  But, I just thought I would put it out there.  Thanks!
0
 

Author Comment

by:jmcclosk
ID: 40428782
Isn't this line calling out when a new appointment item is opened?

If (TypeName(m_Inspector.CurrentItem) = "AppointmentItem") And (m_Inspector.CurrentItem.Body = "") Then
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40428824
Ok,
This code, for me, fires if and only if I open an appointment that has a blank body.
It does not fire for StickyNotes

Option Explicit
Public WithEvents m_Inspectors As Outlook.Inspectors
Public WithEvents m_Inspector As Outlook.Inspector

Private Sub Application_Startup()
  Set m_Inspectors = Application.Inspectors
End Sub

Private Sub m_Inspectors_NewInspector(ByVal Inspector As Outlook.Inspector)
  Set m_Inspector = Inspector
End Sub

Private Sub m_Inspector_Activate()
Dim response As Integer
Dim objItem As Object


If Not (TypeName(m_Inspector.CurrentItem) = "AppointmentItem") Then Exit Sub
If (m_Inspector.CurrentItem.Body = "") Then
    response = MsgBox("Do you want to add an agenda?", vbYesNo, "Add agenda?")
Else
    Exit Sub
End If

If response = vbNo Then Exit Sub

'Your code to add the agenda would go here

End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40428833
You may not have successfully posted all your code.
Look at the snippet.
It doesn't end with End Sub so it has clearly been truncated
Also, these three items are set before they are Dimmed
Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
Set objSelection = objApp.ActiveExplorer.Selection


So something odd is going on with your attempts to post snippets!
0
 

Author Comment

by:jmcclosk
ID: 40428843
Ok.  So, for me, and others in my company, it fires for only two events... (1) when an appointment is opened that has a blank body (which is what I want it to do) and, (2) when either a new sticky note is created or an existing sticky note is double-clicked to be opened.

It happens on multiple computers, so it is not just a single PC that is messed up.  And, it happens on computers running Vista with Office 2007 as well as computers running Windows 7 with Office 2010.

I've attached the entire body of code if you'd like to check it out in it's entirety.  But, I don't think that's going to make a difference.
OutlookAgenda.txt
0
 

Author Comment

by:jmcclosk
ID: 40428857
I only posted the top of my code originally.  I didn't think anything past the IF statement had any bearing on why a sticky note would affect it.  I've attached the entire code in my last post.  I'm not a formally trained programmer.  So, if some things are out of order, it is because I teach myself how to set things up.  If it works, I assume it is right.  Please feel free to correct anything you feel is not a good layout.  

The code is long because most of it is formatting specific pieces of text submitted by the end user... and there can be a lot submitted.

Like I explained before, the code does exactly what it is supposed to do, except when a sticky note is called.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40429572
Ok,

I have your whole code now.
One of the VERY FIRST THINGS you should always do is put Option Explicit at the top of any code module.
This forces you to DIM any variables that you create.  Much evil can then be averted..
In this case
'Set oContact = objItem
'Set objApp = CreateObject("Outlook.Application")
'Set objNS = objApp.GetNamespace("MAPI")
'Set objSelection = objApp.ActiveExplorer.Selection

Are all unDim'med
Looking at your code, they do not get used, they only get set to nothing
objItem suffers the same fate, so let's comment them out.

Now, your first big problem is where you test for a needed agenda
That comes AFTER you've already fired up the Word editor, set a bunch of objects and Dim'med a whole pile more.
That's NOT efficient.
And it blows up my machine with ANY object I open.

So, first, by Sticky Notes, you don't mean the Sticky Notes application c:\windows\system32\StikyNot.exe
You mean Outlook Notes, correct?

You want to do all that testing off the nose, and bail on the subroutine if the conditions aren't met.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40429623
So let's start with your test
If (TypeName(m_Inspector.CurrentItem) = "AppointmentItem") And (m_Inspector.CurrentItem.Body = "") Then
That muddies the waters a bit.
We want the code to bail if we're NOT looking at an appointment item, period
Dim msg As Integer
If Not (TypeName(m_Inspector.CurrentItem) = "AppointmentItem") then
    Set m_Inspector = Nothing
    Exit Sub
End if

Open in new window

'so we can only get here if we're on an appointment.  Next we want to test if the body is blank
If Not m_Inspector.CurrentItem.Body = "" then
    Set m_Inspector = Nothing
    Exit Sub
End If

Open in new window

'We can't get here without a blank body.  Next we want to test for a blank subject
If (m_Inspector.CurrentItem.Subject = "") Then
    Set m_Inspector = Nothing
    Exit Sub
End If

Open in new window

'Then we'll ask if an agenda needs to be set
msg = MsgBox("Do you want an Agenda for this Calendar Item?", vbYesNo, "Agenda?")
If (msg = vbNo) Then
    Set m_Inspector = Nothing
    Exit Sub
End If

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40429683
So with that done, I can run your code, and get your grief.
This test
If Not (TypeName(m_Inspector.CurrentItem) = "AppointmentItem") Then
    Set m_Inspector = Nothing
    Exit Sub
End If


Does not do what we expect.  Double-clicking a note does NOT fail this test.  Looking into the help for TypeName shows why
TypeName Function    
Returns a String that provides information about a variable.
Syntax
TypeName(varname)
The required varname argument is a Variant containing any variable except a variable of a user-defined type.
Remarks

The string returned by TypeName can be any one of the following:
String returned Variable
object type An object whose type is objecttype
Byte Byte value
Integer Integer
Long Long integer
Single Single-precision floating-point number
Double Double-precision floating-point number
Currency Currency value
Decimal Decimal value
Date Date value
String String
Boolean Boolean value
Error An error value
Empty Uninitialized
Null No valid data
Object An object
Unknown An object whose type is unknown
Nothing Object variable that doesn't refer to an object

If varname is an array, the returned string can be any one of the possible returned strings (or Variant) with empty parentheses appended. For example, if varname is an array of integers, TypeName returns "Integer()".

We don't want to test for what variable type m_Inspector.CurrentItem is, we need to test for what kind of object it is.  So our initial testing block will be
Dim msg As Integer
'is this item and appointment?
If Not m_Inspector.CurrentItem.Class = OlObjectClass.olAppointment Then
    Set m_Inspector = Nothing
    Exit Sub
End If

'is the body blank?
If Not m_Inspector.CurrentItem.Body = "" Then
    Set m_Inspector = Nothing
    Exit Sub
End If

'is the subject blank?
If Not (m_Inspector.CurrentItem.Subject = "") Then
    Set m_Inspector = Nothing
    Exit Sub
End If
'ask if we want and agends
msg = MsgBox("Do you want an Agenda for this Calendar Item?", vbYesNo, "Agenda?")
If (msg = vbNo) Then ' no, then bail
    Set m_Inspector = Nothing
    Exit Sub
End If

Open in new window


With that, only an appointment with no body and no subject will cause code to fire
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40429726
Of course, the code still blows up at
Set objWord = objDoc.Application
but at least it's only blowing up when I try to create an agenda.

I also suspect it's going BANG! because I am on Outlook 2003, and that does not use Word as the editor for Appointment objects.  I'll have a go at it on an Outlook 2010 machine and see how it goes.  Other badness:
Dim st_tim0, st_tim1, st_tim2, st_tim3, st_tim4, st_tim5, st_tim6, st_tim7, st_tim8, st_tim9 As String
You might think that all those variables are strings.
They are NOT
Only the last one is, all the others will be of type Variant
You can do this
Dim st_tim0 as String, st_tim1 as String, ... st_tim9 As String
But you can't do what you've done and get all strings.
Which can be evil and inefficient

Code cleaned up to this point is attached.

Nick67
Agenda.txt
0
 

Author Closing Comment

by:jmcclosk
ID: 40432849
Nick67,

This is perfect!  It solves my problems and actually taught me some best practices at the same time!  Thank you!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40433272
I haven't tested it yet under Win7/Outlook 2010 to see if there are more bombs in the code, yet.
I got your message and will be working with the code further.

Stay tuned

Nick67
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40433381
Two things.
I get the code to blow up in the same way you do.
At the very top of the code we set
Set objSel = objWord.Selection
But at that point it equals Nothing.
(there is no selection yet or something else, like Word isn't actually invoked is wrong?)
And it never gets set to anything later.  Hence the BANG!
I am investigating.

Second:
A lot of this code seems to be duplication.
Up to 10 activities and up to 3 line items per activity.
There are cleaner ways that use less copy-and-pasted code to do that, if that is the case.
Let me know.

Nick67
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40433474
Ok, it looks like the functional aspects of your code came from here
http://www.slipstick.com/developer/word-macro-apply-formatting-outlook-email/
But the exact instructions weren't fulfilled
Select a block of text while composing a message and run the macro.
So, we need to use VBA to select the block of text written in m_Inspector.CurrentItem.Body and then set objSel
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40433562
Ok,
It turns out that the code goes BANG! because the Agenda Items needs to be displayed before the objSel gets set.
Add this one line in down 3/4's of the way.  Line to be added is italicized

            m_Inspector.CurrentItem.Subject = subj
            m_Inspector.CurrentItem.Start = DateValue(Strt_Date) & " " & TimeValue(Strt_Time)
            m_Inspector.CurrentItem.duration = Val(duration)

            m_Inspector.CurrentItem.Body = bodMsg & Chr(13) & Chr(10) & "----------------------------------------------------------------------------------------" & _
                                                    Chr(13) & Chr(10) & "Additional Instructions:" & Chr(13) & Chr(10) & addl_inst
           
            m_Inspector.Display
0
 

Author Comment

by:jmcclosk
ID: 40433613
Hi Nick67,

I still get the same "error 91: Object variable or with block variable not set" on Win7/Office 2010 machines for the exact same line of code "objSel.Find.ClearFormatting".  Code still works fine on Vista/Office 2007 machines.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40433749
Ok,

Attached is the code as I would do it.
Loops within loops combined with arrays make for neat ways of doing repetitive tasks.
Your poorly Dim'med string variables I have changed to arrays of strings.
Instead of blocks of repetitive code, you get
for i = 0 to something
   SomeStringArray(i) = somevalue
   for n = 1 to SomeOtherValue
       SomeOtherString(n,i) = SomeValueToo
    next n
next i
This code would have no need of an arbitrary limit to Activities and Line Items, as the loops can take as much as you can throw at them.

I have left most of your code intact and commented out so you can mostly see what I replaced.
Document and save your own code for comparison.

The only thing not working to my satisfaction is the activity end time.
That defaults to the formatting string for some reason
nearly-complete.txt
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40433759
Much has been rearranged.  I am not setting the Word objects until much much farther down in the code.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40434245
Ok,

Here is the final code.
I didn't clean up the stuff after vbNo
I think this is has everything in order.

Nick67
Final-code.txt
0
 

Author Comment

by:jmcclosk
ID: 40441092
Nick67,

Sorry I did not respond earlier, as I was away for business.  You've gone above and beyond on your help here.  You've solved my errors as well as re-engineered my code to be shorter and more efficient!  No more errors using it with Win7 either!

Thank you very much for everything you did!

clusker2
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40441123
No Problem.
Back in the day when I started out, others did the same for me.
Did you understand what I did and why?
Does it make sense to you?

I didn't comment the code much -- I spent my time making it work.
But that may have left you in the dark about why I did some of the things I did.

I replace your 95 Select Cases loop with a structure that loads any values that exist into an array.
A loop then uses the values loaded into the array to apply formatting.  That was the biggest poorly documented change.
And I didn't do the same change below vbNo.  That I left to you to do.

Nick67
0
 

Author Comment

by:jmcclosk
ID: 40441150
Yes, it makes sense.  Although I have no training in programming, I've learned enough to understand arrays and how they function.  I just don't always think to use them.  My knee jerk reaction to coding is to "If-Then", "Do-While", and "For-Next" as much as possible because I can see the progression of the code as I work.  But, when I have the ability to discuss with others, I learn more efficient ways to do things... like here.

Leaving the bottom of the code for me will give me a real life challenge to work on doing the same thing.  And, that is good because I gain better understanding of things when I can do them myself.  So, kudos to all that you've done... and not done!  It was a great mix!  Thanks!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40441254
The ideal way to code loops is to think about the outer loop as something you're only going to walk down once
With your Select Case, it was walking down that 95 times, evaluating each case, each time -- and many of the times, the value didn't exist.  The new array gets loaded, and then the formatting gets applied.

What I didn't do -- because that would have been waaaaaaaaaaaaaaaaay too much work was thoroughly optimize the code.  Right now, in pseudo code, you
-create the values for the main items
-run through loops to create the recurring items
-nuke the existing formats
-Run through the created items to load them up for a loop
-run though a loop to apply formats.

Where you are creating or appending bodMsg is really where you should be applying the formatting

:)

Here's one of the best things I learned to escaped nested if..then hell
Select Case True

Nick67
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

760 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

18 Experts available now in Live!

Get 1:1 Help Now