jmcclosk
asked on
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!
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!
ASKER
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
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?
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?
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!
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!
ASKER
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!
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!
ASKER
Isn't this line calling out when a new appointment item is opened?
If (TypeName(m_Inspector.Curr
If (TypeName(m_Inspector.Curr
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
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
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.Appl
Set objNS = objApp.GetNamespace("MAPI"
Set objSelection = objApp.ActiveExplorer.Sele
So something odd is going on with your attempts to post snippets!
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.Appl
Set objNS = objApp.GetNamespace("MAPI"
Set objSelection = objApp.ActiveExplorer.Sele
So something odd is going on with your attempts to post snippets!
ASKER
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
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
ASKER
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.
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.
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.Appl
'Set objNS = objApp.GetNamespace("MAPI"
'Set objSelection = objApp.ActiveExplorer.Sele
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\StikyN
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.
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.Appl
'Set objNS = objApp.GetNamespace("MAPI"
'Set objSelection = objApp.ActiveExplorer.Sele
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\StikyN
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.
So let's start with your test
If (TypeName(m_Inspector.Curr
That muddies the waters a bit.
We want the code to bail if we're NOT looking at an appointment item, period
If (TypeName(m_Inspector.Curr
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 ifIf Not m_Inspector.CurrentItem.Body = "" then
Set m_Inspector = Nothing
Exit Sub
End IfIf (m_Inspector.CurrentItem.Subject = "") Then
Set m_Inspector = Nothing
Exit Sub
End Ifmsg = MsgBox("Do you want an Agenda for this Calendar Item?", vbYesNo, "Agenda?")
If (msg = vbNo) Then
Set m_Inspector = Nothing
Exit Sub
End If
So with that done, I can run your code, and get your grief.
This test
If Not (TypeName(m_Inspector.Curr
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
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
With that, only an appointment with no body and no subject will cause code to fire
This test
If Not (TypeName(m_Inspector.Curr
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 IfWith that, only an appointment with no body and no subject will cause code to fire
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nick67,
This is perfect! It solves my problems and actually taught me some best practices at the same time! Thank you!
This is perfect! It solves my problems and actually taught me some best practices at the same time! Thank you!
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
I got your message and will be working with the code further.
Stay tuned
Nick67
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
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
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
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.Bo
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.Su
m_Inspector.CurrentItem.St
m_Inspector.CurrentItem.du
m_Inspector.CurrentItem.Bo
Chr(13) & Chr(10) & "Additional Instructions:" & Chr(13) & Chr(10) & addl_inst
m_Inspector.Display
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.Su
m_Inspector.CurrentItem.St
m_Inspector.CurrentItem.du
m_Inspector.CurrentItem.Bo
Chr(13) & Chr(10) & "Additional Instructions:" & Chr(13) & Chr(10) & addl_inst
m_Inspector.Display
ASKER
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.ClearFormatti
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.ClearFormatti
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
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
Much has been rearranged. I am not setting the Word objects until much much farther down in the code.
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
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
ASKER
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
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
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
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
ASKER
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!
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!
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
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
um,
Where is the code?