Subscript out of Range Q

[John]

I have the folloing coding trying to e-mail to addresses in AA6 & AB6 but
I'm getting a 'Sunscript out of Range' debug message. It says the MyArr is
empty yet its not I have e-mail addresses in both AA6 and AB6

My code is as follows, any help appreciated

Sub Mail_Reports()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h.mm")
Application.ScreenUpdating = False
Sheets("E-Mys Shop").Visible = True
Sheets("E-Mys Shop").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""
MyArr = Sheets("Home").Range("AA6:AB6")
.SendMail MyArr, Sheets("Home").Range("X15").Value

.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Sheets("E-Mys Shop").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Home").Select
Range("A1").Select
End Sub

 

[Nick Hodge]

John

My guess is you don't have a sheet called home. Even if you had nothing in
the cells the array will still fill with empty elements

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[John]

I do actually Nick, only a amateur user of macros however, when I go into
debug and 'float' over MyArr it shows 'MyArr=Empty'

 

[Nick Hodge]

John

I shortened it to just two lines when I tested it

Sub Mail_Reports()
Dim MyArr As Variant
MyArr = Sheets("Home").Range("AA6:AB6")
End Sub

Worked fine for me

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Jake Marx]

Hi John,

I have the folloing coding trying to e-mail to addresses in AA6 & AB6
but I'm getting a 'Sunscript out of Range' debug message. It says the
MyArr is empty yet its not I have e-mail addresses in both AA6 and AB6

The only thing I can think of is that you've got some mixed references to
your workbooks.

Sheets("E-Mys Shop").Copy
Set wb = ActiveWorkbook

Here, you set wb up to be a reference to the new workbook (containing only 1
sheet - "E-Mys Shop").

With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""

Here, you are saving the new workbook.

MyArr = Sheets("Home").Range("AA6:AB6")

Assuming the executing code does not reside in a worksheet module, this code
actually references the new workbook, which probably doesn't have a "Home"
worksheet in it. Not sure why it's not raising a runtime error here, unless
you are using On Error Resume Next.

Even if this isn't an accurate assessment, you may want to set a reference
to your original workbook. Then, use the fully-qualified references when
referring to workbook and worksheet-level objects. That way, you remove all
ambiguity and possibility of error relating to unqualified references.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[John]

Jake you have cracked it, MyArr = Sheets("Home").Range("AA6:AB6") - this
code references the new workbook, not the workbook where "Home" is located,
so by setting a ref to original workbook, it worked

Thanks all for taking the time to advise

Hi John,

I have the folloing coding trying to e-mail to addresses in AA6 & AB6
but I'm getting a 'Sunscript out of Range' debug message. It says the
MyArr is empty yet its not I have e-mail addresses in both AA6 and AB6

The only thing I can think of is that you've got some mixed references to
your workbooks.

Sheets("E-Mys Shop").Copy
Set wb = ActiveWorkbook

Here, you set wb up to be a reference to the new workbook (containing only 1
sheet - "E-Mys Shop").

With wb
.SaveAs ThisWorkbook.Name _
& " Sent on" & " " & strdate & ""

Here, you are saving the new workbook.

MyArr = Sheets("Home").Range("AA6:AB6")

Assuming the executing code does not reside in a worksheet module, this code
actually references the new workbook, which probably doesn't have a "Home"
worksheet in it. Not sure why it's not raising a runtime error here, unless
you are using On Error Resume Next.

Even if this isn't an accurate assessment, you may want to set a reference
to your original workbook. Then, use the fully-qualified references when
referring to workbook and worksheet-level objects. That way, you remove all
ambiguity and possibility of error relating to unqualified references.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]