VBA to add hyperlink in Word document - help needed

kckay

Board Regular
Joined
Nov 8, 2010
Messages
134
I have an Excel macro that generates a letter using a Word template, to populate fields and data from Excel (source) into Word (target). The letter is then edited and sent to the customer.

Part of the body of the letter may contain URL text. I edited the Word document from Excel during creation to change the font color to blue and to underline it. This worked wonderfully when we were sending the letter as a hard copy and no hyperlink was needed. We are transitioning to electronic mail with the letters. I would like to add the hyperlink to the URL text in the letter when the letter is created (and blue'd/underlined). I have been struggling and have not been able to add the hyperlink through the macro.

Attached, please see the code used to edit and change the color of a URL text. (yes, the replacement seems odd, but we are re-doing web addressing and am trying to be ready to go when it happens)

Also, included is the line of code I have tried for the hyperlink. This is just one of multiple edits I get a "Run-Time error '424' Object required" type message when I run the code.

VBA Code:
IN EXCEL MACRO:
.
.
.
    With appWD.ActiveDocument.Content.Find
        .ClearFormatting
        .Font.Bold = False
        With .Replacement
            .ClearFormatting
            .Font.Underline = wdUnderlineSingle
            .Font.Color = RGB(0, 0, 255)
        End With
        .Execute FindText:="http://www.colorado.gov/cdphe/ccwp-colorado-certified-water-professionals", ReplaceWith:="http://www.colorado.gov/cdphe/ccwp-colorado-certified-water-professionals", _
            Format:=True, Replace:=wdReplaceAll
    End With
   
    anchor_string = "http://www.colorado.gov/cdphe/ccwp-colorado-certified-water-professionals"
    appWD.ActiveDocument.Hyperlinks.Add Anchor:=anchor_string, Address:="http://www.colorado.gov/cdphe/ccwp-colorado-certified-water-professionals", ScreenTip:="CCWP"
.
.
.

Any assistance would be so much appreciated.

Thank you.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Watch MrExcel Video

Forum statistics

Threads
1,122,980
Messages
5,599,162
Members
414,295
Latest member
Dolenhil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top