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

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!
jmccloskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
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
jmccloskAuthor Commented:
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
Nick67Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Commented:
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
jmccloskAuthor Commented:
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
jmccloskAuthor Commented:
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
Nick67Commented:
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
Nick67Commented:
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
jmccloskAuthor Commented:
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
jmccloskAuthor Commented:
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
Nick67Commented:
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
Nick67Commented:
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
Nick67Commented:
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
Nick67Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmccloskAuthor Commented:
Nick67,

This is perfect!  It solves my problems and actually taught me some best practices at the same time!  Thank you!
0
Nick67Commented:
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
Nick67Commented:
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
Nick67Commented:
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
Nick67Commented:
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
jmccloskAuthor Commented:
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
Nick67Commented:
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
Nick67Commented:
Much has been rearranged.  I am not setting the Word objects until much much farther down in the code.
0
Nick67Commented:
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
jmccloskAuthor Commented:
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
Nick67Commented:
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
jmccloskAuthor Commented:
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
Nick67Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.