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.
Any assistance would be so much appreciated.
Thank you.
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.