Converting All Plain Text Links to Hyperlinks

[kckay]

I have an Excel workbook that we input data into. once complete I run a macro that performs a mailmerge and some formatting of the text including changing plain text link addresses to blue/underline. This process worked great as we were a hard-copy shop. Due to the coronavirus, we are converting to electronic letter. I need to convert the plain text link to an active and correct link. I was unable to get any code to work in an Excel macro against the Word document, so now call this macro (thank you Techkie007) in the Word template (.dotm):

Sub Hyperlinker()
Dim Rng As Range

Set Rng = ActiveDocument.Range
With Rng.Find
    Do While .Execute(findText:="www.", Forward:=False) = True
    Rng.MoveEndUntil (" ")
        ActiveDocument.Hyperlinks.Add _
            Anchor:=Rng, _
            Address:=Rng.Text, _
            SubAddress:="", _
            ScreenTip:="", _
            TextToDisplay:=""
        Rng.Collapse wdCollapseStart
    Loop
End With

End Sub

This code does convert the plain text link to a hyperlink, but does so in the wrong information. It will place the full path directory of the Word document and append the link address into the hyperlink. For example: incorrect link is C:\this letter directory\wqcdcompliance.com where the correct link should be \wqcdcompliance.com.

How can I modify the code to only use the correct link?

Thank you for your assistance.

I did try:

Sub Hyperlinker()
    Word.Options.AutoFormatReplaceHyperlinks = True
   Selection.Range.AutoFormat
End Sub

It reformatted a couple of headings and a couple of sentences in the body, but the hyperlinks were correct.

 

[Macropod]

So where do the instructions say anything about "copy all of the text from the Excel cells" or "search the document for the 'plain text links'" or "replace the 'plain text link' with brackets and HYPERLINK and the correct hyperlink "??? have you actually done anything those instructions say to do??? At which point do you get stuck???