Attach multiple files to email saved in different paths

[Bering]

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.

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]

Could you supply more code that shows how the files are being selected.

 

[Bering]

Could you supply more code that shows how the files are being selected.

Hi, thanks for looking into my question:

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]

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

 

[Bering]

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]

woops, looks like FolderPicker does not allow multi-select.

Did you know where the folders would be located?

 

[Bering]

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]

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

Select Multiple Folders

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

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