locked
Hyperlinks file path and Application.Selection RRS feed

  • Question

  • Hi,

    I cannot get my head around how to execute hyperlink(s) file path + how to get range of cells to execute.

    The problem is when hyperlink name (title/subject) is not a file path then returns error. To get this working hyperlink must be c:\folder\filename.pdf for each cell.

    Application.Selection - selected range of cells returns error for example A1:A5. I would like to use range rather than picking up each cells with CTRL as in future it might force me picking hundreds of them.


    In future I would like to use upload files into selected directory and insert hyperlink in selected cell ( if that make any sense)
    Please advice.

    Code:
    Function GetBoiler(ByVal sFile As String) As String'**** Kusleika
        Dim fso As Object
        Dim ts As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
        GetBoiler = ts.readall
        ts.Close
    End Function
    
    
    Sub hiperlink()
    
    
     Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
        Dim SigString As String
        Dim Signature As String
        Dim crange As String
        Dim selc As String
        Dim WorkRng As Range
        Dim cel As Range
     
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    
    
                                                                            Set OutApp = CreateObject("Outlook.Application")
                                                                            Set OutMail = OutApp.CreateItem(0)
                                                                        
                                                                            'Change only Mysig.txt to the name of your signature
                                                                            SigString = Environ("appdata") & _
                                                                             "\Microsoft\Signatures\default.htm"
                
                                                                            If Dir(SigString) <> "" Then
                                                                                Signature = GetBoiler(SigString)
                                                                            Else
                                                                                Signature = ""
                                                                            End If
                
                    
                    With OutMail
                    .Subject = "Trainings/certificates"
                    .HTMLBody = "Good afternoon," & "Please find attached as per your request.
    
    
    " & Signature
                                    
                    For Each cel In WorkRng
                        If cel Is Nothing Then
                        Else
                        On Error GoTo fo
                        .Attachments.Add WorkRng
                        End If
                    Next cel
                    
                    .Display   'or use .Display
                    End With
                    'wbNew.Close strFilename
    fo:
    MsgBox "Check if hyperlink(s) are valid... either hyperlink must be titled as filepath and start again", vbOKOnly, "Mr Rubber"
    
    
    End Sub
    • Moved by Chenchen Li Thursday, April 27, 2017 2:28 AM Excel VBA, NOT Office add-in
    Wednesday, April 26, 2017 6:36 PM

All replies

  • >>Application.Selection - selected range of cells returns error for example A1:A5. I would like to use range rather than picking up each cells with CTRL as in future it might force me picking hundreds of them.

    What is your issue with this? After you select the Range in worksheet, Application.Selection will return the selected Range.

    Here is a simple code to output the Selection.

    Sub test()
    Set WorkRng = Application.Selection
    For Each cel In WorkRng
       If cel Is Nothing Then
       Else
        Debug.Print cel.Value
       End If
    Next cel
    End Sub


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 27, 2017 8:06 AM