In Excel for Microsoft 365 on Windows, a VBA macro can let you choose a folder, attach its files to an Outlook email, and display the draft for review. The example below uses classic Outlook; it does not send automatically unless you deliberately change one setting.
What the macro does
The macro creates one email and attaches each file found directly inside the folder you select. It does not search subfolders, create a separate email for each file, or send the email by default. That approach is useful when several related documents should go to the same recipient together, such as a set of monthly reports.
Choosing a folder at run time avoids hard-coding a path that might differ between computers. The recipient, subject, and message text are still set in the VBA code, so you can edit them before running the macro. The draft appears in Outlook, where you can check the recipients and attachments and then click Send.
Requirements and Outlook compatibility
This example uses Excel VBA and Outlook’s Windows desktop automation interface. It requires desktop Excel for Windows and classic Outlook for Windows. The newer Outlook for Windows does not support the same COM automation used by this macro, so installing it does not guarantee that the code can create an Outlook message. If Outlook is unavailable or automation is blocked by an organization’s policies, the macro may show an error instead.
Excel for the web cannot run VBA macros. Excel for Mac can run VBA, but this Windows-specific Outlook automation code is not a Mac-compatible way to create an Outlook message. For a Mac workflow, use a supported mail integration or prepare the message and attachments manually. Outlook and Excel versions, account configuration, and company security rules can all affect automation.
Before running any macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z. Keep this workbook in a trusted location and only enable macros in files whose source you trust.
VBA macro to attach a folder’s files
In the code, replace the example recipient, subject, and body with your own details. The folder picker selects the source folder. The macro skips folders inside it and adds the files it finds to one Outlook draft. It displays the draft rather than sending it, which gives you a chance to review the message.
Sub EmailFolderContents()
Dim folderPath As String
Dim fileName As String
Dim fileCount As Long
Dim outlookApp As Object
Dim mail As Object
Dim folderPicker As FileDialog
Dim sendNow As Boolean
On Error GoTo ErrHandler
' Keep False while testing; change only when automatic sending is intended.
sendNow = False
Set folderPicker = Application.FileDialog(msoFileDialogFolderPicker)
With folderPicker
.Title = "Choose the folder to attach"
If .Show <> -1 Then Exit Sub
folderPath = .SelectedItems(1)
End With
If Right(folderPath, 1) <> Application.PathSeparator Then
folderPath = folderPath & Application.PathSeparator
End If
Set outlookApp = CreateObject("Outlook.Application")
Set mail = outlookApp.CreateItem(0)
With mail
.To = "recipient@example.com"
.Subject = "Files from the selected folder"
.Body = "Hello," & vbCrLf & vbCrLf & _
"Please see the attached files." & vbCrLf
End With
fileName = Dir(folderPath & "*.*")
Do While Len(fileName) > 0
If (GetAttr(folderPath & fileName) And vbDirectory) = 0 Then
mail.Attachments.Add folderPath & fileName
fileCount = fileCount + 1
End If
fileName = Dir()
Loop
If fileCount = 0 Then
MsgBox "No files were found in that folder.", vbInformation
Exit Sub
End If
If sendNow Then
mail.Send
Else
mail.Display
End If
Exit Sub
ErrHandler:
MsgBox "The email could not be prepared: " & Err.Description, vbExclamation
End Sub
The entities shown for symbols such as ampersands and angle brackets are HTML encoding; in the VBA editor, enter the normal VBA characters. For example, the comparison is written as <> in the page but should be typed as <> in the editor as the actual VBA operator, not as an HTML entity. In practice, paste the code from a plain-text version if your publishing system encodes code samples.
Install and run the macro
- Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). A standard .xlsx file cannot store VBA code.
- Open the VBA editor with Alt+F11 on Windows. Choose Insert > Module, then paste the macro into the new module.
- Change the email fields in the code and save the workbook. Close the editor.
- Run the macro with Alt+F8, select
EmailFolderContents, and choose Run. Select the folder when prompted. - Review the Outlook draft, including the recipient, subject, message, and every attachment. Send it manually only after confirming it is correct.
On a Mac, Option+F11 and Option+F8 are common equivalents for opening the VBA editor and macro dialog; some keyboards require holding Fn as well. Those shortcuts do not make this particular Outlook automation code Mac-compatible. Do not use Excel for the web to run it.
Customize the recipient and attachment behavior
Set .To to the intended recipient or recipients. Outlook accepts multiple addresses separated by semicolons. You can also set .CC or .BCC, and change .Subject and .Body. The body in this example is plain text. Outlook’s .HTMLBody property can be used for HTML-formatted content, but the HTML must be formed carefully and tested in the recipient’s mail client.
The code attaches all eligible files found at the folder’s top level. If the folder contains drafts, old versions, or unrelated documents, they may be included too. A safer workflow is to create a dedicated folder containing only the intended attachments. To restrict file types, add a test before mail.Attachments.Add; for instance, a condition based on the filename extension can include only PDF files. Make sure the filter matches the actual files you expect before sending.
To enable sending without displaying the draft, change sendNow = False to sendNow = True. That is a significant change: the macro will call Outlook’s Send method, which may send immediately or place the message in the Outbox depending on Outlook and account behavior. Test with a controlled recipient first. Keeping the draft-review mode is safer for routine use.
One email or one email per file?
This example makes one message with multiple attachments. That is generally appropriate when the files belong together and share a recipient and explanation. If you need one email per file, the logic must create a new Outlook item inside the file loop and set its recipient, subject, and body for each item. Do not simply move the existing attachment line and assume that it creates separate messages: an Outlook mail item is still one message until another item is created.
Separate messages can help when files have different recipients, but they increase the risk of sending a document to the wrong person. For that kind of workflow, use a worksheet or another structured source to map each filename to a recipient, validate that every match exists, and display drafts for review. Sending a folder wholesale is not a substitute for checking who is authorized to receive each file.
Common problems and practical limits
- Outlook cannot be created: Confirm that classic Outlook for Windows is installed and configured. The new Outlook app does not provide the same COM automation interface.
- The macro is blocked: Check Excel’s Trust Center settings and your organization’s policy. Do not lower security protections globally just to run an unfamiliar workbook.
- No files are attached: Verify that you selected the folder containing the files, not its parent or a folder with only subfolders. This macro does not search recursively.
- The message will not send: Check Outlook’s account connection, Outbox, and any warning or security prompt. Your organization may require approval or restrict programmatic sending.
- The email is too large: Mailbox and organization attachment limits vary. The total message can be larger than the files’ combined size because email encodes attachments. Use an approved shared link or file-sharing service when the files exceed the applicable limit.
Also consider filenames and paths. A folder on a disconnected network drive or removable disk may become unavailable between selection and attachment. Files can be locked, deleted, or changed while the macro runs. For important batch emails, verify the attachment count and open the draft’s attachment list before sending; the code reports when it finds no files but does not independently verify that recipients can open them.
Safety, privacy, and alternatives
Attachments can disclose personal, confidential, or commercially sensitive information. Check the contents as well as the filenames, confirm the recipient address character by character, and follow your organization’s data-handling rules. The macro does not scan files for sensitive information or encrypt them. If the documents require access controls, a managed sharing link may be more suitable than sending copies as attachments.
For a one-time email, manually attaching the files in Outlook may be simpler and easier to audit. For repeatable business processes, consider Power Automate or an organization-approved workflow that can log activity, apply permissions, and handle failures. These alternatives have their own licensing, configuration, and policy requirements; VBA is useful when a small, local Excel task needs a straightforward draft but is not a complete email-distribution system.
Frequently Asked Questions
Can Excel send files from a folder without opening a draft?
Yes. In the example, set sendNow to True to use Outlook’s Send method. Test carefully first, because the message may be sent without a final review.
Does this macro include files in subfolders?
No. It checks files directly in the selected folder. Searching subfolders requires additional VBA that traverses each directory.
Can I attach only PDF files?
Yes. Add a filename-extension condition before the attachment line so other file types are skipped, and test it on a folder containing both PDFs and non-PDFs.
Will the macro work with the new Outlook for Windows?
Not as written. It uses Outlook COM automation intended for classic Outlook for Windows; the new Outlook does not support that same automation method.
Can I run this macro in Excel for the web?
No. Excel for the web does not run VBA. Use desktop Excel on Windows with classic Outlook for this specific example.
Related
- How to Create Goal Seek in Excel: A Practical Guide
- Excel Hangman Solver: Build a Word-Guessing Tool
- Goal Seek in Excel: A Practical Sample
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel UserForm: Change Font Size at Design Time or in VBATo change an Excel VBA UserForm’s font size, select the form or a control in the Visual Basic…
- Add Controls to a VBA UserForm: Steps and CodeTo add controls to a VBA UserForm, open the form in the Visual Basic Editor and drag a…
- Excel VBA Range Find Exact Match with xlWhole ExplainedTo find a cell whose entire contents match a value in Excel VBA, call Range.Find with LookAt:=xlWhole, and…
- Excel VBA Variant: What It Is and When to Use ItIn Excel VBA, a Variant is a flexible data type that can hold different kinds of values, including…
- VBA AutoFilter Criteria Array: Syntax and ExamplesTo filter a field for several allowed values in VBA, pass a Variant array to Criteria1 and set…
- Excel VBA: List All Sheets in a WorkbookTo list every sheet in the workbook that contains your VBA code, loop through its Sheets collection and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.