Excel VBA Macro: Send Email with All Files (in a Specific Folder) Attached

#ExcelVBA #ExcelMacroExcel VBA Macro: Attach All Files (in a Specific Folder) to an Email and Send. In this video, we write code in VBA to add all files in a folder to an email using a Do While Loop.

Code (YouTube doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):

Sub email_all_files_in_folder()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim myFldr As String
Dim myFile As String

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

myFldr = "C:\Users\greggowaffles\Documents\Youtube Videos\Test\Sample Data Files\"
myFile = Dir(myFldr)

strbody = "LT BODY style = font-size:11pt; font-family:Arial GT" & _
"Hi Team, LT p GT Please see file(s) attached. LT p GT" & _
"Thanks, LT br GT Greg"

On Error Resume Next
With OutMail
.CC = ""
.BCC = ""
.Subject = "Daily File(s) " & Format(Date, "mm/dd/yyyy")
.Display
.HTMLBody = strbody & .HTMLBody

Do While myFile LT GT ""

.Attachments.Add myFldr & myFile

myFile = Dir

Loop

End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

#ExcelVBA #ExcelMacro

[SongDictionary]

could you please let me know how to make this file path dynamic.

[drewgossage]

Another great little piece of code! Thanks for sharing man👍👍

[sachinkariappa]

Thank you so much, it’s a great video 👍🏻

Is there way to attach files based on a specified modified date instead of attaching all the files in the folder?

[skphotoskills-zrii]

Hello thats awesome, i was looking for the same. can you help if i have a file name changes every week as per the current week but the start name of the file remain constant. I have many excel files in a folder but i want only file name starts with servicenow. Can you pls help.

[kevshen]

Great video, very helpful....is it possible to add a cell value to the string body? for example I try to add Range("C3").Value. I can add it as part of the email subject, but cant seem to integrate info from cells into the email body/strbody. Sorry I'm a noob, but I've looked everywhere and tried all sorts with no success.

[tokatemika]

How can you use the subpath, which contains the files as the subject line instead?

[carloscostafonte]

Excelent Video . Can you do it for two folders at same time ?

[ngoisaolon]

Thank you very much, Is it can show files names in body of email?

[BrianaTowery]

How could I modify it to send all files but send them individually (one file per email)

[austinshofner]

At 6:40 you change the folder path to "Some Files"... Is there not away to just copy the above code and keep the macro running for ALL folders. That way you can send out all the attachments across all folders without having to stop and change the path?

Im picturing an End result that allows me to send multiple emails going out to different recipients containing all attachments for their specific folder.

Is this possible?

I feel like i would just copy the above code 15x. With each section the code is listed i would give a a new "myfldr" path along with a different recipients address on the "to." section?

Im super new to coding and this will save me SO much time at my job. Your videos are some of the best ive seen.

[tanyaaggarwal]

Hello, I would like to know how to make folder dynamic since i have list of folder names in excel

[isommallory]

Great stuff. Is there a way to have the email auto send instead of waiting on user input?