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.
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