Outlook VBA app references XL-Runtime error 9

[expect_ed]

Created a set of Macros to file messages, then drive them with ALT keys from
toolbar buttons, each button with it's own code. I'm trying to distribute
this to co-workers, so I put the code into a word file and give instructions
on how to add a new module in Outlook and copy this code:

Sub Move2XL_GPointr()
Dim xl As Excel.Application
Dim TopFldr As String
Dim TargetFldr As String

Set xl = GetObject(, "Excel.Application")
TopFldr = xl.Workbooks("Index.xls").Sheets(1).Range("TopFldrG").Value
TargetFldr = xl.Workbooks("Index.xls").Sheets(1).Range("TrgtFldrG").Value
'Move current item to designated folder
Dim msg As Object

For Each msg In ActiveExplorer.Selection
msg.Move _
GetNamespace("MAPI").Folders(TopFldr).Folders(TargetFldr)
Next 'msg

Set msg = Nothing

End Sub

I have them open Reference and turn on the Excel Object Library. When they
try to run the macro from ALT-F8 menu they get a Runtime Error 9 - Script Out
of Range. Googling found an issue in Excel with manual copying of VBA
modules but I can't quite grasp the issue or be certain it is similar to my
problem.

Any help apprected.
TIA
ed

 

[Ken Slovak - [MVP - Outlook]]

Based on the out of range error I'd expect the error to be occurring either
in the For loop or the lines where the values from the Excel sheet are being
read. It would be of help to know exactly what line the users are getting
the error on.

Are the users getting the correct values from the Excel sheet? Do they have
Excel open and loaded with that sheet? Do they have the correct folder names
there in their Outlook's? Are they selecting at least one item in current
folder?

As an aside, distributing VBA code is not a recommended way to go. For
distribution you really should be writing a COM addin.

 

[expect_ed]

Hi Ken,
Thanks for the quick response.

The error comes at the line: TopFldr =
xl.Workbooks("Index.xls").Sheets(1).Range("TopFldrG").Value

I've double checked the spelling, spaces, range name, etc.. Excel is open
and the relevant sheet is active and a message is selected.

As far as the distribution, although I've done a fair amount of amateur
programming, writing a COM addin sounds a bit over my head. Any suggestions
for where to go for guidance on trying that?

 

[Ken Slovak - [MVP - Outlook]]

I'm far from an Excel coding expert, and you might get better results in an
Excel forum, but does it make a difference if you use a range of cells
instead of a named range in that line? Something like C4:C5 or whatever?

 

[Alan Moseley]

I can reproduce this error if I do not have a workbook called Index.xls open
at the time of running the macro. Are you sure that the user isn't making
this same mistake.

Also, does the range TopFldrG refer to one cell or more than one cell?

 

[expect_ed]

Alan / Ken
Thanks for the response. I have gone to the users machine and confirmed
that the workbook is open. It is in fact the same workbook I forwarded to
her, with the exception that she has added entries into the cells to match
her folder names rather than mine. I also re-entered the TopFldrG name to
confirm that it is correct and confirmed that the range is still named the
same.

TopFldrG only refers to the cell that holds the name of that folder. I can
not get access to her machine today but I will try replacing the range with a
cell reference tomorrow. Not sure why that would make a difference?

Wondering if there is a way to break down that line of code to better
understand where within it specifically the problem lies??

Thanks again for any help.
ed

 

[Ken Slovak - [MVP - Outlook]]

TopFldr = xl.Workbooks("Index.xls").Sheets(1).Range("TopFldrG").Value


Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range

Set xlBook = xl.Workbooks("Index.xls")
Set xlSheet = xlBook.Sheets(1)
Set xlRange = xlSheet.Range("TopFldrG")

I'd also be adding tests for each object as it was instantiated to make sure
it wasn't Nothing, and possibly debugging code to show the values of each
object.

 

[Alan Moseley]

This is the same code but broken down into smaller steps

Dim xl As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim TopFldr As String
Set xl = GetObject(, "Excel.Application")
If Not xl Is Nothing Then
Set wb = xl.Workbooks("Index.xls")
If Not wb Is Nothing Then
Set ws = wb.Sheets(1)
If Not ws Is Nothing Then
TopFldr = ws.Range("TopFldrG").Value
Else
MsgBox "No Reference To Sheet(1)"
End If
Else
MsgBox "No Reference To The Workbook Index.xls"
End If
Else
MsgBox "No Reference To Excel"
End If

 

[expect_ed]

Thanks to both of you for your help. I will try this tomorrow on the
offending machine and let you know the results.
Thanks again.
ed

 

[expect_ed]

Excuse if this is a duplicate post - I thought I posted this an hour ago but
it does not appear:
Now I am thoroughly confused. I tried Alan's code on the other machine and
it worked without an error. So I substituted his breakdown steps in my
original code and it also worked fine. I believe I now have a working
application, but the question is:

Why does this work:
Sub Move2XL_G2Pointr()
Dim xl As Excel.Application
Dim wb As Workbook
Dim ws as Worksheet
Dim TopFldr As String
Dim TargetFldr As String

Set xl = GetObject(, "Excel.Application")
Set wb = xl.Workbooks("Index.xls")
Set ws = wb.Sheets(1)
TopFldr = ws.Range("TopFldrG").Value

TargetFldr = ws.Range("TrgtFldrG").Value
'Move current item to designated folder
Dim msg As Object

For Each msg In ActiveExplorer.Selection
msg.Move _
GetNamespace("MAPI").Folders(TopFldr).Folders(TargetFldr)
Next 'msg

Set msg = Nothing

End Sub

And this does not work, but gives either a run time error 9 or -2147221233

Sub Move2XL_GPointr()
Dim xl As Excel.Application
Dim TopFldr As String
Dim TargetFldr As String

Set xl = GetObject(, "Excel.Application")
TopFldr = xl.Workbooks("Index.xls").Sheets(1).Range("TopFldrG").Value
TargetFldr = xl.Workbooks("Index.xls").Sheets(1).Range("TrgtFldrG").Value
'Move current item to designated folder
Dim msg As Object

For Each msg In ActiveExplorer.Selection
msg.Move _
GetNamespace("MAPI").Folders(TopFldr).Folders(TargetFldr)
Next 'msg

Set msg = Nothing

End Sub

Is it as simple as that the second code does not include DIM statements for
the workbook and worksheet? But why then does the second code work fine on
my original machine?

Very confusing.
Thanks for any enlightenment you can provide.
ed