# VBA to create emails with an attachment based on path (folder)



## Jonnny (Dec 19, 2022)

ABCDEFRow 1To:ccSubjectBody TextPath of Attachment folderAttachment nameRow 2abc@email1.comabc@email1.comtest email 1For your information...C:\Users\ABC\Desktop\SavedFolder\Folder112192022Invoice12.pdf

Hi, 
I want to display emails with an attachment from a certain folder. It will be used by multiple users so the path is different and the users must enter their path in row 2, column E. 
In the folder, there will be multiple documents with all different name. If users enter the file name in row 2, column F, VBA create an email with the attachment.

Below is the code I currently use. 


```
Private Sub CommandButton1_Click()

Dim strbody As String
    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
        .To = ""
        .CC = Rows(2).Columns(4).Value
        .BCC = Rows(2).Columns(3).Value
        .Subject = Rows(2).Columns(5).Value
        .Body = Rows(2).Columns(6).Value
        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
```


----------



## breynolds0431 (Dec 20, 2022)

Assuming you have a way to call the code (either in a button or manually running), the below should work for you...


```
Private Sub CommandButton1_Click()

Dim strbody As String
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)

'add path and file to variables
Dim aPath As String: aPath = Cells(2, 7).Value & "/"
Dim aFile As String: aFile = Cells(2, 8).Value

With xMailOut
    .To = ""
    .CC = Cells(2, 4).Value
    .BCC = Cells(2, 3).Value
    .Subject = Cells(2, 5).Value
    .Body = Cells(2, 6).Value
    'attaches file using path and file variables
    .attachments.Add aPath & aFile
    .Display
End With
Set xMailOut = Nothing
Set xOutApp = Nothing
Application.ScreenUpdating = True
End Sub
```


----------



## Jonnny (Jan 5, 2023)

Thank you so much. It works perfectly.!

Is it also possible to attach multiple attachments? 


Below is my current macro.


```
Private Sub CommandButton1_Click()

Dim strbody As String
Dim xStrFile As String
Dim xFilePath As String
Dim xFileDlg As FileDialog
Dim xFileDlgItem As Variant
Set xOutApp = CreateObject("Outlook.Application")
Set xMailOut = xOutApp.CreateItem(olMailItem)
Application.ScreenUpdating = False


'add path and file to variables
Dim aPath As String: aPath = Cells(2, 6).Value & "/"
Dim aFile As String: aFile = Cells(2, 7).Value

With xMailOut
    .To = Cells(2, 1).Value
    .CC = Cells(2, 2).Value
    .BCC = Cells(2, 3).Value
    .Subject = Cells(2, 4).Value
    .Body = Cells(2, 5).Value
    'attaches file using path and file variables
    .attachments.Add aPath & aFile
    .Display
End With
Set xMailOut = Nothing
Set xOutApp = Nothing
Application.ScreenUpdating = True
End Sub
```


----------



## breynolds0431 (Jan 5, 2023)

It's possible to loop through the specified folder and attach multiple files from it. Would you want all files in the folder attached to the email? Or are there specific ones?


----------



## Jonnny (Yesterday at 3:39 AM)

I would like to attach multiple selected files from the specified folder. 
E.g) I have several check boxes and only checked files to be attached from the specified folder. Would it be possible?
Thank you so much in advance your advice.


----------



## breynolds0431 (Yesterday at 5:57 PM)

Will there be a set number of checkboxes for each row? Or will all rows use the same checkbox collection? 

If you can provide a visual of how it will look, that would be helpful.


----------

