none
Merge field as hyperlink in MS Word RRS feed

  • Question

  • Hi,

    I have a word doc template file which is used as a source for an email attachment in my web application.

    I have some merge fields in the template doc. All the fields work fine as simple/plain text. My concern is, I have an email adress field and a website link field and I want those to work as hyperlinks. How can I do this ? I did try something but they dont show the text I need :(

    My Fields:

    «EmailValue»

    «WEBSITE»

    My trial:

    «EmailValue»  - {HYPERLINK "mailto:\«EmailValue\»"}

    http://«website»/  - {HYPERLINK "http://\«website\»/"http://«WEBSITE»}

    Thanks,

    santosh

    Friday, December 10, 2010 3:19 PM

Answers

  • You may be able to use Word Mailmerge events to maintain the hyperlinks. Her'e some text from an old message of mine - it may be possible to simplify this using the Document's Hyperlinks collection, but I think I had reasons for doing it this way:

    1. Create a new document, connect it to your data source, and insert one
    merge field and a bookmark named "mybm"

    2. Open up the VBA Editor and
     a. insert a class module.
     b. name it EventClassModule in the properties box
     c. Copy the following code into the module:

    Public WithEvents App As Word.Application

    Private Sub App_MailMergeBeforeRecordMerge(BYVal Doc As Document, Cancel As
    Boolean)
    Dim dt as String
    Dim lt as String
    Dim h as Hyperlink
    Dim r as Range

    ' set the range variable to our placeholder bookmark
    Set r = Doc.Bookmarks("mybm").Range

    ' delete any existing text (this is needed for records after record 1)
    r.Text = ""

    ' construct the link text that you want. I'm assuming your data source
    ' has a field called WEBADDRESS for the link. NB, these field names
    ' are case-sensitive.
    lt = Doc.MailMerge.DataSource.DataFields("WEBADDRESS")
    ' set up the display text that you want. I assuming you have a field
    ' called WEBTEXT
    dt = Doc.MailMerge.DataSource.DataFields("WEBTEXT")

    ' insert the hyperlink you want
    Set h = Doc.Hyperlinks.Add(Anchor:=r, Address:=lt, TextToDisplay:=dt)

    ' Set mybm to "cover" the inserted link so it is easy to delete the old
    hyperlink

    Doc.Bookmarks.Add Name:="mybm", Range:=h.Range

    Set r = Nothing
    Set h = Nothing

    End Sub

    3. Insert an ordinary module (the name does not matter) and insert the
    following code:

    Dim x As New EventClassModule

    Sub autoopen()
    Set x.App = Word.Application
    End Sub

    4. Save and close the document. Open it to trigger the autoopen, then
    perform a test merge.

    NB, if you start changing the code you may find that you need to re-run your
    autoopen code again, and/or save/close/open the document. You should realise
    that
    once you have enabled the events, they apply to any document until either
    you or
    Word has disabled them again.


    Peter Jamieson
    Friday, December 10, 2010 10:40 PM
  • Hi Santosh,

    The simplest approach to merging hyperlinks is to do the merge as plain text, then use:

    ActiveDocument.Range.AutoFormat

    Note: you'll need to check that your document's Style usage is appropriate, otherwise the above code might have other, undesirable effects.


    Cheers

    macropod MS MVP - Word

    Monday, December 13, 2010 10:51 PM

All replies

  • You may be able to use Word Mailmerge events to maintain the hyperlinks. Her'e some text from an old message of mine - it may be possible to simplify this using the Document's Hyperlinks collection, but I think I had reasons for doing it this way:

    1. Create a new document, connect it to your data source, and insert one
    merge field and a bookmark named "mybm"

    2. Open up the VBA Editor and
     a. insert a class module.
     b. name it EventClassModule in the properties box
     c. Copy the following code into the module:

    Public WithEvents App As Word.Application

    Private Sub App_MailMergeBeforeRecordMerge(BYVal Doc As Document, Cancel As
    Boolean)
    Dim dt as String
    Dim lt as String
    Dim h as Hyperlink
    Dim r as Range

    ' set the range variable to our placeholder bookmark
    Set r = Doc.Bookmarks("mybm").Range

    ' delete any existing text (this is needed for records after record 1)
    r.Text = ""

    ' construct the link text that you want. I'm assuming your data source
    ' has a field called WEBADDRESS for the link. NB, these field names
    ' are case-sensitive.
    lt = Doc.MailMerge.DataSource.DataFields("WEBADDRESS")
    ' set up the display text that you want. I assuming you have a field
    ' called WEBTEXT
    dt = Doc.MailMerge.DataSource.DataFields("WEBTEXT")

    ' insert the hyperlink you want
    Set h = Doc.Hyperlinks.Add(Anchor:=r, Address:=lt, TextToDisplay:=dt)

    ' Set mybm to "cover" the inserted link so it is easy to delete the old
    hyperlink

    Doc.Bookmarks.Add Name:="mybm", Range:=h.Range

    Set r = Nothing
    Set h = Nothing

    End Sub

    3. Insert an ordinary module (the name does not matter) and insert the
    following code:

    Dim x As New EventClassModule

    Sub autoopen()
    Set x.App = Word.Application
    End Sub

    4. Save and close the document. Open it to trigger the autoopen, then
    perform a test merge.

    NB, if you start changing the code you may find that you need to re-run your
    autoopen code again, and/or save/close/open the document. You should realise
    that
    once you have enabled the events, they apply to any document until either
    you or
    Word has disabled them again.


    Peter Jamieson
    Friday, December 10, 2010 10:40 PM
  • Heyy Peter,

    Thank you so much for your reply. It's a huge KT for me, esp for VBA.

    I should have been more specific in my post. My word doc is a template which generates an attachment acc to some in-built email code. So, the macro should run when an instance of the template doc is created/generated and before it gets attached to the email object.

    My question now is, will AutoOpen() suffice to my requirement ? I tried AutoOpen(), AutoExec() and AutoNew() but none of them work. I am still having plain text in my email attachment doc and not a hyperlink.

    Thanks,

    santosh.


    Thanks, Santosh
    Monday, December 13, 2010 7:17 PM
  • Of the top of my head, I think AutoNew should be enough, assuming you are starting from a .dot or .dotm and are creating a "tear-off" document that is then the mail merge main document.

    Because there are so many parts involved, it may be that the event handling code is running but just isn't doing what you expect. I would probably start by verifying that each part of my system was actuallly running, e.g. ensure that the mailmergbeforerecordmerge event code is running by putting a msgbox method call in there and testing with a small record count.


    Peter Jamieson
    Monday, December 13, 2010 10:19 PM
  • Hi Santosh,

    The simplest approach to merging hyperlinks is to do the merge as plain text, then use:

    ActiveDocument.Range.AutoFormat

    Note: you'll need to check that your document's Style usage is appropriate, otherwise the above code might have other, undesirable effects.


    Cheers

    macropod MS MVP - Word

    Monday, December 13, 2010 10:51 PM
  • Heyy Peter and Macropod,

    Thank you so much. KUDOS to you both !! Got my stuff working :)

    Included the below code in my Module code:

    Sub AutoOpen()
    ActiveDocument.Range.AutoFormat
    End Sub

    best regards,

    santosh

     


    Thanks, Santosh
    Tuesday, December 14, 2010 3:34 PM