Word macro to find all hyperlinks, cut them, delete everything else, and paste back into document?

Question

I've written a script in Powershell that saves me a great deal of time with an otherwise monotonous daily task of opening unread e-mails in an inbox and downloading files from the hyperlinks in those e-mails. My script reads all of the e-mails, saves the body of each to a .txt file, opens the organization page and logs in, and then uses Invoke-Webrequest to download all links from the pages in the .txt file of specified types (.docx, .doc, .pdf, etc.). It then renames them in the proper format and moves them into the corresponding folder for each file.

The problem I'm encountering is with the .txt file--the body of each e-mail usually reads "Items to view e-mailed from" and then the name of the sender and the link itself. To get this to play nicely with Powershell, I have to manually open the .txt file in Word, use advanced find to search for "http*ittach" (a pattern which all the links and only the links match), close the find dialog, cut the selected text, select everything else, delete, paste in the links, and then save. It takes almost no time at all, but ideally I'd like to fully automate this process so it doesn't require any user interaction at all and can just run invisibly on a schedule every day.

I've tried using Word's macro recorder to record my exact actions when modifying the .txt file, but every time I try to run the macro on a document, it returns "Runtime Error 4605: This Method or Property is not available because the object is empty." I would suspect that when running the macro as opposed to going through the motions manually, it doesn't preserve the selection when closing out of the find dialog. Code below:

Sub GetLinks()
'
' GetLinks Macro
'
'
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "http*ittach"
        .Replacement.Text = "; "
        .Forward = True
        .Wrap = wdFindAsk
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
    End With
->  Selection.Cut
    Selection.WholeStory
    Selection.TypeBackspace
    Selection.PasteAndFormat (wdFormatOriginalFormatting)
    ActiveDocument.Save
End Sub

The error occurs at the ->.

user13803 user13803 · Accepted Answer · 2014-08-08T19:04:11

I'm still kind of a novice with VBA, but I believe calling Execute will select the first match of the Find operation.

With Selection.Find
    .Text = "http*ittach"
    .Replacement.Text = "; "
    .Forward = True
    .Wrap = wdFindAsk
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = True
    .Execute
End With

I can't find the reference I had open for this, but I still have the following page open, as it seems to cover Find quite thoroughly: http://gregmaxey.mvps.org/word_tip_pages/words_fickle_vba_find_property.html

EDIT:

As mentioned above, .Execute only selects the first match, but further research shows it returns a boolean when called (True if a match is found, False if not), so you should be able to use it as a while loop condition. So one solution would be to loop through the matches, copy the selection to a variable, then paste it all out at the end. Something like this untested bit of code:

Dim linkArray() As String
Dim idx As Integer

idx = -1
With Selection.Find
    <snip settings>
    While .Execute
        idx = idx + 1
        ReDim Preserve linkArray(idx) As String
        linkArray(idx) = Selection.Text
    Wend
End With
Selection.WholeStory
Selection.TypeText(Join(linkArray, VbCrLf))

Hopefully that works, but either way, the While .Execute idiom should be useful in coming up with a solution.

Word macro to find all hyperlinks, cut them, delete everything else, and paste back into document?

1 Answers