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.