Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Mailmerge and Fax / November 2007

Tip: Looking for answers? Try searching our database.

Merge - Hyperlink address different from displayed text

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Christophe - 26 Nov 2007 15:50 GMT
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 - 26 Nov 2007 16:12 GMT
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.

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> Greetings from France:
>
[quoted text clipped - 12 lines]
>
> Christophe
Christophe - 26 Nov 2007 20:52 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.