Macro for adding multiple attachments to an email in outlook

[saurabhlotankar]

Hello,

I have created a macro for sending maiils through my outlook account with attchments. In that macro i have given path of a folder on my desktop. Ideally i want the macro to attach all the excel files in the folder, but it's picking up only one excel file and attaching it to the mail. Please go through the code below and suggest me wht changes should i do in the below mentioned code so that it will attchment all the files in the folder path i have given. Here is the code:


Sub macro1()


Dim sFolder As String

Dim Folder As Object

Dim file As Object

Dim fso As Object

Dim i As Long

Dim a As String


i = 3


Set fso = CreateObject("Scripting.FileSystemObject")

sFolder = "C:UsersabcdDesktopjoy new"

Set Folder = fso.GetFolder(sFolder)

For Each file In Folder.Files


a = file.Name


If LCase(file) Like "*.xls" Then

i = i + 2

Range("A" & i).Value = a

End If

Next file


Columns("A:A").Select

Selection.Replace What:=".*", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False

Range("A1").Select


Dim count As Long

Dim n As Integer

Dim filename As String


Range("A1").Select

Selection.End(xlDown).Select

count = Selection.Row


For n = 2 To count


Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(olMailItem)


filename = Range("A" & n).Value


With OutMail

.to = "joy.khanna@outlook.com"

.Subject = "Hello"

.Body = "Hello Joy"

.Attachments.Add ("C:UsersabcdDesktopjoy new" & filename & ".xls")


.Send 'or use .Display

End With


Next n


End Sub

 

[SirJB7]

Hi, saurabhlotankar!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Despite of this and not having tested nor checked your whole code, the issue appears to be here:

.Attachments.Add ("C:UsersabcdDesktopjoy new" & filename & ".xls")

where it's adding only the filename at column A row n, i.e., the actual filename in the range, regarding value of n.


That's to say that you're sending several mails, each one with one attached file, as you have in the same loop cycle the attachment and the send instruction.


If you want to send only one mail with all the files attached depending on range A2:Axx range, you should do something like this (I say something as I haven't tested your code, I'm only analyzing it):

-----

[pre]

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail
.to = "joy.khanna@outlook.com"
.Subject = "Hello"
.Body = "Hello Joy"

For n = 2 To count

filename = Range("A" & n).Value
.Attachments.Add ("C:UsersabcdDesktopjoy new" & filename & ".xls")

Next n

.Send 'or use .Display
End With

[/pre]
-----


Regards!