excelvbafilehyperlinkexplorer

Create Hyperlinks from multiple files chosen in Explorer


I am using the following code, which I have cobbled together from different sources, to insert a file link in an email granting everyone access to the file. The sub will only let me choose one file and insert fpath in the email with fname as its name. I want to be able to choose multiple files if I need to and insert that list of hyperlinks into the email body with the access taken care of.

Sub CreateHyperLink()
    username = Environ$("USERNAME")
    Dim fd As Object, objShell As Object, FSO As Object, permissionCommand As String
    Set objShell = CreateObject("Wscript.Shell")
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = "standard directory etc."     
        .InitialView = msoFileDialogViewDetails
        .AllowMultiSelect = False 'change this to True
        .Title = "Please select a file."
        .Filters.Clear
        .Filters.Add "Video Files", "*.cva,*.mp4,*.webm"
        .Filters.Add "All Files", "*.*"

I need to loop the following for each file chosen

        If .Show = True Then
            fPath = fd.SelectedItems(1)
            fName = Right(fPath, Len(fPath) - InStrRev(fPath, "\"))
        Else
            Exit Sub
        End If
    End With
    permissionCommand = " /grant Everyone:(r)"
    If FSO.FileExists(fPath) Then
        objShell.Run "cmd /c icacls.exe " & fPath & permissionCommand
    End If
End Sub

Any suggestions? I am not very familiar with running a section as a loop.


Solution

  • In case anyone else needs this, the following code is what I can up with.

    Sub CreateHyperLink()
        username = Environ$("USERNAME")
        Dim fd As Object, objShell As Object, FSO As Object, permissionCommand As String, newLink As String, i As Integer
        Set objShell = CreateObject("Wscript.Shell")
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        permissionCommand = " /grant Everyone:(r)"
        Dim vrtSelectedItem As Variant
        i = 80
    Restart:
        With fd
            .InitialFileName = "C:\Users\" & username & "Default Path" 
                                 'default folder path MUST  end with \
            .InitialView = msoFileDialogViewDetails
            .AllowMultiSelect = True
            .Title = "Please select up to 3 files."
            .Filters.Clear
            .Filters.Add "Video Files", "*.cva,*.mp4,*.webm"
            .Filters.Add "All Files", "*.*"
            If .Show = -1 Then
                If fd.SelectedItems.Count > 3 Then
                    MsgBox "Choose up to 3 files ONLY", vbOKOnly
                    SendKeys "{ESC}", True
                    GoTo Restart
                End If
                For Each vrtSelectedItem In .SelectedItems
                    fPath = vrtSelectedItem
                    fName = Right(fPath, Len(fPath) - InStrRev(fPath, "\"))
                    If FSO.FileExists(vrtSelectedItem) Then
                        objShell.Run "cmd /c icacls.exe " & vrtSelectedItem & permissionCommand
                    End If
                    newLink = ("<a href=" & """" & fPath & """" & ">" & fName & "</a>")
                    ActiveSheet.Range("H" & i).Value = newLink
                    i = i + 1
                Next
            Else
                Exit Sub
            End If
        End With
        
        Set fd = Nothing
    End Sub