Merge - Hyperlink address different from displayed text

[Christophe]

Greetings from France:

As part of an email mail merge in MS Word 2003 ou 2007, is it possible
to merge data into the hyperlink address as opposed to only the
displayed text.

e.g. if in Excel in the field WEBTEXT I have "Google" and in the field
WEBADDRESS
I have "http://www.google.com" as a data can I obtain as a result of
the merge (email merge)
"Google" in the body of the email and http://www.google.com as the
address of this hyperlink.

Thank you in advance and best regards from Paris,Remerciements.


Christophe

 

[Peter Jamieson]

I think you have to use Word Mail Merge events for this. I have not tested
this recently, but for example:

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. This is why I do not particularly like using
Events, but even so,
I suspect that this is the easiest way to perform this particular task in
Word.

 

[Christophe]

Peter:

Thanks a lot. It is amaizing. It certainly goes far beyond my skills
but I will certainly give it a try.
If you don't mind I will get back to you if I have any questions;

Thank you again and best regards from Paris,

Christophe