Attach multiple files to email saved in different paths

Bering

Board Regular
Joined
Aug 22, 2018
Messages
132
Office Version
  1. 2016
Platform
  1. Windows
Hello,
the macro below allows the user to select multiple attachments and works great but only if the files are all located in the same path .
Is there a way to select attachments saved in different locations? Is it possible to perahps pause the macro execution while an array containing all the user-selcted attachments gets created??

Please note that the attachments could be saved in completely different, random locations, so looping through a list of paths would not be a suitable option.

Many thanks for any help.

VBA Code:
If Not IsArray(wbFullName) Then
        If wbFullName = False Then
            Exit Sub
        End If
    End If

        If IsArray(wbFullName) Then
              For i = LBound(wbFullName) To UBound(wbFullName)
                .Attachments.Add wbFullName(i)
            Next i
        Else
            .Attachments.Add (wbFullName)
        End If
    End If
 
Last edited:

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
Could you supply more code that shows how the files are being selected.
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
132
Office Version
  1. 2016
Platform
  1. Windows
Could you supply more code that shows how the files are being selected.
Hi, thanks for looking into my question:

VBA Code:
If MsgBox("Please Select Attachments", vbYesNo) = vbNo Then Exit Sub

    wbFullName = Excel.Application.GetOpenFilename("All Files (*.*), *.*", MultiSelect:=True)

    If Not IsArray(wbFullName) Then
        If wbFullName = False Then
            Exit Sub
        End If
    End If

        If IsArray(wbFullName) Then
      
              For i = LBound(wbFullName) To UBound(wbFullName)
                .Attachments.Add wbFullName(i)
            Next i
        Else
            .Attachments.Add (wbFullName)
        End If
    End If
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
I am thinking of using a folder picker first, then in a userform list all the files from the selected folders.
 
Solution

Bering

Board Regular
Joined
Aug 22, 2018
Messages
132
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I am thinking of using a folder picker first, then in a userform list all the files from the selected folders.

Thanks Dave, I will try and see what I can do based on your suggestion.
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
woops, looks like FolderPicker does not allow multi-select.

Did you know where the folders would be located?
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
132
Office Version
  1. 2016
Platform
  1. Windows
woops, looks like FolderPicker does not allow multi-select.

Did you know where the folders would be located?
Unfortunately not, the files could really be located anywhere in the drive.
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
132
Office Version
  1. 2016
Platform
  1. Windows
Thanks Dave, your suggestion got me started. I managed to achieve what I wanted by combaning a FolderPicker with a File Picker and with the help of the Function in this thread, works like a charm (y)

Select Multiple Folders

I post the relevant bits of the code for anyone interested:

VBA Code:
Dim sFolder As String
Dim aFolders() As Variant
Dim iFolderCount As Integer

 iFolderCount = 0
 sFolder = GetFolderName

Do While sFolder <> ""
    
        ReDim Preserve aFolders(1 To iFolderCount + 1)
        aFolders(iFolderCount + 1) = sFolder
        .Attachments.Add sFolder
        iFolderCount = iFolderCount + 1
        sFolder = GetFolderName
    
    Loop
    
    
    Function GetFolderName(Optional OpenAt As String) As String
    Dim lCount As Long
    Dim wbFullName As Variant
    
    GetFolderName = vbNullString
    
    With Excel.Application.FileDialog(msoFileDialogFolderPicker)
    wbFullName = Excel.Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = OpenAt
        
        .Show
        
        For lCount = 1 To .SelectedItems.Count
        
            GetFolderName = .SelectedItems(lCount)
        Next lCount

    End With
Debug.Print GetFolderName
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,127,663
Messages
5,626,166
Members
416,166
Latest member
Archimed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top