[[email protected]]

Please can you assist with the following query.

I've got a Mailmerge working between an excel data source and MS
Word. This works fine.

However, one of the fields within the data source is actually a
hyperlink (which is fine in Excel). However when this transfers over
it loses the 'hyperlink' properties. Is it possible to force the
attribute back on again when it transfers to word? Or to select the
column in the table in word when the macro is running and format in
some way as a hyperlink?

thanks in advance for your help/time.

Cheers, Al.

[Peter Jamieson]

A. If you want the same "display text" for every hyperlink, say "my display
text", then you should be able to do the following. If not, skip to B.
Let's assume the hyperlinks in Excel are in a column called mylink
a. in your mail merge main document, use ctrl-F9 to insert a pair of field
code braces {}
b. type HYPERLINK "my display text" between the braces.
c. Select the field and press F9 to execute it. The result should be a
hyperlink showing "my display text"
d. display the field code again using Alt-F9 and delete "my display text"
leaving
{ HYPERLINK "" }
e. put the insertion point between the "", then either insert the
mergefield for mylink (e.g. from the dropdown in the Mail Merge toolbar) or
use ctrl-F9 to insert another pair of field braces, but this time type
MERGEFIELD mylink

The whole thing should look like

{ HYPERLINK "{ MERGEFIELD mylink }" }

Then select the fields and execute them again. If you merge to a new
document, you will get the same display text for every link, but the link
text should come from Excel. However, you will probably also have to select
the new document (control-A) then re-execute al the fields using F9 to see
them properly.

B. If you want the display text to be the same as the link text, it's much
more complicated because there is no "switch" in the HYPERLINK field that
lets you specify the display text. You may be able to do the merge using VBA
and "Word Events", e.g. as follows (I haven't tested this recently):

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 mylink to be used for both
lt = Doc.MailMerge.DataSource.DataFields("mylink")
' set up the display text that you want. If it should be the same as the
link text, do that:
dt = lt

' 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.

Peter Jamieson

wrote in message
ps.com...
Please can you assist with the following query.

I've got a Mailmerge working between an excel data source and MS
Word. This works fine.

However, one of the fields within the data source is actually a
hyperlink (which is fine in Excel). However when this transfers over
it loses the 'hyperlink' properties. Is it possible to force the
attribute back on again when it transfers to word? Or to select the
column in the table in word when the macro is running and format in
some way as a hyperlink?

thanks in advance for your help/time.

Cheers, Al.