Attach files in a folder to one email

So you want to send a bunch of attachments to someone. Well, here's a technique you might use. This VBA code sample attaches all the files in a folder to a single email. You can specify the type of file you want to attach, or just leave the parameter blank and it will attach all files in the folder.

You will, however, have to specify the folder where the attachments are kept.

This code is based on the code I posted in Save attachments and send clean emails in that it loops through a folder and attachs all the files to an email.

Sub AttachFiles(strFolder As String, Optional FileType As String = "*.*")

' check if folder exists
If Dir(strFolder, vbDirectory) = "" Then
  GoTo ExitProc
End If

' check if folder contains specified items
Dim strFileN As String
strFileN = Dir(strFolder & FileType)
If Len(strFileN) = 0 Then
  GoTo ExitProc
End If

' create mailitem
Dim olApp As Outlook.Application
Dim Msg As Outlook.MailItem
Dim olAttach As Outlook.attachments
Set olApp = Application
Set Msg = olApp.CreateItem(olMailItem)
Set olAttach = Msg.attachments

' loop through folder and add attachments
' if we got this far, strFileN will already contain the
' name of the first file in the dir
Do While Len(strFileN) > 0
  olAttach.Add strFolder & strFileN
  strFileN = Dir
Loop

Msg.Display

ExitProc:
Set olApp = Nothing
End Sub

Usage:

Sub test()

  Call AttachFiles("C:\", "*.txt")

End Sub

My ideal usage would be as part of a larger routine, creating toolbar buttons that attach different types of files to an email.

For example, if you had a server kicking out csv and xls files that needed to be emailed somewhere, you could write two macros that call the AttachFiles macro. Each of these would be attached to a different toolbar button.

Sub SendCSV()

  Call AttachFiles("C:\", "*.csv")

End Sub
Sub SendXLS()

  Call AttachFiles("C:\", "*.xls")

End Sub

Or combine them into one:

Sub SendFiles()

  Call AttachFiles("C:\", "*.xls")
  Call AttachFiles("C:\", "*.csv")

End Sub

If there are no files of that type in the specified folder, no email will be created.

Update: Jon Peltier just announced the Advanced Excel Conference for this year. He and Bob Umlas will be teaching a few courses in Excel, VBA and userforms.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 1 Comment(s) on Attach files in a folder to one email:

  1. Just wanted to say thanks for sharing this. Helped me out a lot!

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel