Note: The other languages of the website are Google-translated. Back to English
Log in Register

How to send email with multiple attachments attached in Excel?

This article is talking about sending an email through Outlook with multiple attachments attached in Excel.

Send email with multiple attachments attached in Excel with VBA code

Send email with multiple attachments attached in Excel with VBA code

Please do as follows to send an email with multiple attachments attached in Excel.

1. Please insert a command button by clicking Developer > Insert > Command Button (ActiveX Control). See screenshot:

2. After inserting the Command Button, please right click it and select View Code from the context menu.

3. In the opening Microsoft Visual Basic for Applications window, please click Tools > References as below screenshot shown.

4. In the References – VBAProject dialog box, please find and check the Microsoft Outlook Object Library option, and then click the OK button.

5. Then replace the original code in the Code window with below VBA code.

VBA code: Send email attached with multiple attachments in Excel

Private Sub CommandButton1_Click()
    Dim xStrFile As String
    Dim xFilePath As String
    Dim xFileDlg As FileDialog
    Dim xFileDlgItem As Variant
    Dim xOutApp As Outlook.Application
    Dim xMailOut As Outlook.MailItem
    Application.ScreenUpdating = False
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailOut = xOutApp.CreateItem(olMailItem)
    Set xFileDlg = Application.FileDialog(msoFileDialogFilePicker)
    If xFileDlg.Show = -1 Then
        With xMailOut
            .BodyFormat = olFormatRichText
            .To = "happy.xuebi@163.com"
            .Subject = "test"
            .HTMLBody = "test"
            For Each xFileDlgItem In xFileDlg.SelectedItems
                .Attachments.Add xFileDlgItem
            Next xFileDlgItem
            .Display
        End With
    End If
    Set xMailOut = Nothing
    Set xOutApp = Nothing
    Application.ScreenUpdating = True
End Sub

Note: please specify your email recipient, subject, and body by changing the variate in .To = happy.xuebi@163.com, .Subject = "test" and .HTMLBody = "test" lines in the code.

6. Press the Alt + Q keys together to exit the Microsoft Visual Basic for Applications window.

7. Click Developer > Design Mode to turn off the Design Mode. See screenshot:

8. Click the Command Button to run the code. In the popping up Browse window, select the files you need to attach in the email, and then click the OK button. See screenshot:

9. Then an email is created with specified fields and attachments listed out. Please click the Send button to send it. See screenshot:

Note: The VBA code is only working when you use Outlook as your email program.

Easily send email through Outlook based on fields of created mailing list in Excel:

The Send Emails utility of Kutools for Excel helps users sending email through Outlook based on created mailing list in Excel.
Download and try it now! ( 30-day free trail)

Related articles:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (13)
No ratings yet. Be the first to rate!
Laurel Granger
Laurel Granger
This comment was minimized by the moderator on the site
I am able to "Quickly send email with multiple attachments attached in Excel with Kutools for Excel" using your steps above, but would like to take it one step further and personalize each email by inserting their names (from a column in the Excel sheet) into the body of the email. While in the body of the email, I try selecting the field and clicking on "Insert Placeholder" but nothing happens. Any idea why or how I can fix this? Thank you!
Reply
Prashant Soni
Prashant Soni
This comment was minimized by the moderator on the site
What if above files are located in sub-folders? How to look for a file in folder and sub-folders?
Reply
Report
Rizwan Siddiqui
Rizwan Siddiqui
This comment was minimized by the moderator on the site
What if multiple different user and different attachment to be send with different subject line.
Reply
Regina
Regina
This comment was minimized by the moderator on the site
the above code works perfectly thanks!! but my email automatic signature seem to disappear with this code. any idea why?
Reply
Kailas Naik
Kailas Naik
This comment was minimized by the moderator on the site
HI Team ....i am unable to see the attachment in mail so if i follow the your step...can you pls help
Reply
Report
udayakumar
udayakumar
This comment was minimized by the moderator on the site
Superb...... Works perfectly.
Reply
Bigfoot17
Bigfoot17
This comment was minimized by the moderator on the site
I have enjoyed working through this solution for adding multiple attachments to an email from within Excel VBA. Thank you. I know that a lot of us benefit and then want some aspect customized for exactly what we are trying to do. I am no different, but for step 8. Can we set the path that the Browse window will open with? I can browse over to another folder at a different location but if I could set the path that would be a time saver.
Reply
Alberto
Alberto
This comment was minimized by the moderator on the site
Hola,
Me gustaría saber como quedaría la macro si quisiera enviar en un mismo correo a varias personas o más y con varios ficheros o más.
Muchas gracias.
Reply
Report
Alex de Graaf
Alex de Graaf
This comment was minimized by the moderator on the site
Is het ook mogelijk om een filter te zetten op de selectie van de bijlagen, zodat er alleen pdf-bestanden aan de mail kunnen worden toegevoegd?
Reply
crystal
crystal     Alex de Graaf
This comment was minimized by the moderator on the site
Hi Alex de Graaf,
The code directly calls the Microsoft's file selection window. This makes it easy for VBA users to select needed files.
Reply
Report
Alex de Graaf
Alex de Graaf     crystal
This comment was minimized by the moderator on the site
Hi Crystal,

Yes, I understand that. However I want the users to only be able to select pdf-files.
Is this possible?
Reply
crystal
crystal     Alex de Graaf
This comment was minimized by the moderator on the site
Hi Alex de Graaf,
The following VBA code lists only the PDF files in the Browse window. Please give it a try.

Private Sub CommandButton1_Click()
'Updated by Extendoffice 20220714
    Dim xStrFile As String
    Dim xFilePath As String
    Dim xFileDlg As FileDialog
    Dim xFileDlgItem As Variant
    Dim xOutApp As Outlook.Application
    Dim xMailOut As Outlook.MailItem
    Application.ScreenUpdating = False
    Set xOutApp = CreateObject("Outlook.Application")
    Set xMailOut = xOutApp.CreateItem(olMailItem)
    Set xFileDlg = Application.FileDialog(msoFileDialogFilePicker)
    xFileDlg.Filters.Add "PDF", "*.pdf", 1
xFileDlg.FilterIndex = 1
    If xFileDlg.Show = -1 Then
        With xMailOut
            .BodyFormat = olFormatRichText
            .To = "happy.xuebi@163.com"
            .Subject = "test"
            .HTMLBody = "test"
            For Each xFileDlgItem In xFileDlg.SelectedItems
                .Attachments.Add xFileDlgItem
            Next xFileDlgItem
            .Display
        End With
    End If
    Set xMailOut = Nothing
    Set xOutApp = Nothing
    Application.ScreenUpdating = True
End Sub
Reply
Report
Alex de Graaf
Alex de Graaf     crystal
This comment was minimized by the moderator on the site
Hi Crystal,
Yes, I do understand that. However, is it possible to restrict users to only select pdf-files?
Reply
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations