Solved: Email multiple attachments using a column range in excel

[Shazam]

Hi Everyone,


I would like to see if anyone could provide a code that will send multiple attachments in a given range. For example: In my sample workbook below in cells A2:A9 Their are the file path and file names that I would like to email out.

I tried to modify the code below but no luck.



Sub Test()

Dim objol As New Outlook.Application
Dim objMail As MailItem
Dim MyArr As Variant

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

MyArr = Sheets("Sheet1").Range("A2:A9")

.To = ("Nobody@home.com") ' Name of distribution list in Contacts
.Subject = Range("A1")
.Body = ""
.NoAging = True

.Attachments.Add MyArr

.Display
End With
Set objMail = Nothing
Set objol = Nothing


End Sub

[Zack Barresse]

Hey Shazam, maybe you could try iterating through a loop..

Sub Test()

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:A9").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, 1), vbNormal) <> "" Then .Attachments.Add MyArr
Next i
.Display
End With

End Sub

[Shazam]

Thank You for the reply firefytr!



When I ran the code the body of the email appears but no attachments.

[Zack Barresse]

Oops, this line has a slight change (included)...

If Dir(MyArr(i, 1), vbNormal) <> "" Then .Attachments.Add MyArr(i, 1)

HTH

[johnske]

Also, because the New keyword is used you should put Set objol = Nothing before the End Sub :)

[Zack Barresse]

Outlook will be set to nothing already when the End Sub line is processed.

[Shazam]

Oops, this line has a slight change (included)...

If Dir(MyArr(i, 1), vbNormal) <> "" Then .Attachments.Add MyArr(i, 1)
HTH
Hi firefytr,


I replaced the line but still the body of the email only appears not the attachments.


Sub Test()

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:A9").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, 1), vbNormal) <> "" Then .Attachments.Add MyArr(i, 1)
Next i
.Display
End With

End Sub



This is how it looks in cells A2:A9

C:\Book 01-08-2007
C:\Book 01-09-2007
C:\Book 01-10-2007

Am I missing something?

[Zack Barresse]

Try adding the extension. It works for me...

If Dir(MyArr(i, 1) & ".xls", vbNormal) <> "" Then .Attachments.Add MyArr(i, 1) & ".xls"

HTH

[Shazam]

Try adding the extension. It works for me...

If Dir(MyArr(i, 1) & ".xls", vbNormal) <> "" Then .Attachments.Add MyArr(i, 1) & ".xls"
HTH


You're the man!!


Thank You!:bow:

[excelliot]

Good.

Actually i have multiple email id & multiple receipent i dont want to come to know email id used by outlook, instead i want send mail using my yahoo or gmail id. is it possible to have one more cell in excel file which mentions email id from where i have sent email

[Zack Barresse]

Hi excelliot,

Yes, you can do that as well. Just setup your array as two dimensional and use one column of the array for the recipients and the other column for the attachment locations. Although I'm not sure how to use yahoo or gmail to send though. I know there are CDO, SendMail and Outlook methods, but I'm not sure how to do it how you are asking. Although if you wanted to start another thread you may get different results. :)