Attach files in a folder to one email
March 27, 2009 • JP • No Comments • Rate This Article
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.
' 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:
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.
Call AttachFiles("C:\", "*.csv")
End Sub
Call AttachFiles("C:\", "*.xls")
End Sub
Or combine them into one:
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.
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
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 if you want to learn more about VBA. Keep Reading »
Previous Post: Alternative lookup formulas
Next Post: Extract GAL members to Excel
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].