Expand column range in code

[Shazam]

Hi everyone,


I have this code below it will attach multiple files that are listed in column A from a excel worksheet. What I'm trying to do is to expand the range from column A to column D. So I tried to adjust the code from this:

Range("A2:A9").Value

into this

Range("A2:D9").Value

but it did not work it will only look in column A. Any help?

Here is a link where I got the code from.

http://www.vbaexpress.com/forum/showthread.php?t=11058


Sub Email()

Dim objol As New Outlook.Application, objMail As MailItem
Dim MyArr As Variant, i As Long

Set objol = New Outlook.Application
Set objMail = objol.CreateItem(olMailItem)

With objMail
MyArr = Sheets("Sheet1").Range("A2:D9").Value
.To = ("Nobody@home.com") ' Name of distribution list in Contacts
.Subject = Range("A1")
.Body = ""
.NoAging = True
For i = LBound(MyArr) To UBound(MyArr)
If Dir(MyArr(i, 4) & ".xls", vbNormal) <> "" Then .Attachments.Add MyArr(i, 1) & ".xls"
Next i
.Display
End With

End Sub

[Norie]

I think you should take a look at MyArr in the Watch Window.

I think you'll find it has more dimensions than you expect.:bug:

[Shazam]

I think you should take a look at MyArr in the Watch Window.

I think you'll find it has more dimensions than you expect.:bug:


Hi Norie,

I'm sorry I dont understand what you're saying.

[mudraker]

I see nothing that would cause the code to fail

This works for me giving an array of 1 to 8, 1 to 4


MyArr = Sheets("Sheet1").Range("A2:D9").Value


To use the watch window
rightclick the varriable MyArr select Add Watch. This will open a new window where you can view the cantents of MyArr

[JimmyTheHand]

Hi Shazam :hi:

This is the line that adds the attachments:
If Dir(MyArr(i, 4) & ".xls", vbNormal) <> "" Then .Attachments.Add MyArr(i, 1) & ".xls" The red 4 means that the code checks wheter column D contains "*.xls", then, as a result of the red 1, it adds the files listed in column A.

If you want to attach all 8*4=32 items, you need to use a nested loop, or something like this:

Sub Email()

Dim objol As New Outlook.Application, objMail As MailItem
Dim Rng As Range, c As Range

Set objol = New Outlook.Application
Set objMail = objol.CreateItem(olMailItem)

With objMail
Set Rng = Sheets("Sheet1").Range("A2:D9")
.To = ("Nobody@home.com") ' Name of distribution list in Contacts
.Subject = Range("A1")
.Body = ""
.NoAging = True
For Each c In Rng
If Dir(c.Value & ".xls", vbNormal) <> "" Then .Attachments.Add c.Value & ".xls"
Next
.Display
End With

End Sub

Jimmy

[mdmackillop]

Looks OK to me

[Shazam]

Hi mdmackillop, JimmyTheHand, mudraker,

Thanks for the help. The reason I'm so late to respond becasue my computer was down. I tried both of your codes and it works great. Thanks for all the help.:beerchug: