Nested mergefield in hyperlinks changes into static after mailmerg

V

Vetruvian Man

I'm using Word 2007 and I've created a mailmerge, using Access 2007 records.
One of the Access fields is a hyperlink wich changes for each message to a
defferent web page (20 webpages in total).
I've entered the following nested fields in my document:
{ HYPERLINK {MERGEFIELD "MYLINK"} \* MERGEFORMAT}
This works only the first time correctly. After I save the document and open
it again, Word treats the nested field as a static hyperlink. Pressing ALT+F9
does not return the nested field.
Any thoughts an that? I'm puzzled.
 
P

Peter Jamieson

From a recent message:

<<
The other problem is how to get Word's Display text to update.
Typically, when you /update/ a hyperlink field in Word, only the link
text ever changes. So if you have a column like this

myhl
http://www.mysitea.xxx
http://www.mysiteb.xxx

and you use a nested field like this;

{ HYPERLINK { MERGEFIELD myhl } }

then the link text will be updated but not the display text. That's fine
if you /want/ the display text to be the same in every record, e.g.
"Your documents"

However, I have looked at this again and the following seems to work in
Word 2007 - and perhaps earlier versions of Word. Let's suppose first
that the link text matches the display text in each record.

1. Insert the nested field as above (you need to use ctrl-F9 to insert
each pair of the special field braces {} )

2. Select the nested fields and use F9 to update the result.

3. Use Alt-F9 to dislpay the result. You should see a "display text",
typically underlined in blue. Let's say it is

www.mysitea.xxx

4. Click after the first character of the display text (if you have Word
set up so that hyperlinks are followed on an ordinary click rather than
the default ctrl-click, the link will be followed, but when you come
back to Word, the insertion point should be where you clicked).

5. Use ctrl-F9 to insert a pair of the special field braces { }

6. Between the braces, type MERGEFIELD, then the name of the field you
want to use for the display text., so you end up with e.g.

w{ MERGEFIELD myhl }ww.mysitex.xxx

7. Delete the old display text so you just end up with the { MERGEFIELD
myhl }

8. merge to a new document and test the new links.

If you need different display and link texts for each record, you will
need e.g.

{ HYPERLINK { MERGEFIELD mylinktextfield } }

and a separate display text field, e.g.

{ MERGEFIELD myhl }

or

{ MERGEFIELD mydisplaytextfield }

(substituting your own field names).

I have not tried this approach with versions of Word earlier than Word
2007, or with merges to email. I don't remember getting it to work
before - perhaps I did not do quite the right thing, or maybe something
has changed.

Otherwise, it does seem to work, and it even works after you save/close
and re-open the document (not always the case when you're dealing with
links in Word). To me, a significant problem is that it's not very
maintainable - it's difficult to see what you have done because when you
Alt-F9, you only get to see the nested [ HYPERLINK } field, not the
"display text" field.

I would be interested to hear if it works for you. If not, the only
other approach I know is to use VBA to maintain the complete link,
typically using Word's MailMerge events so that the VBA runs once for
each record in the data source. You can probably find examples of that
by searching groups.google.com for peter jamieson hyperlink
However, another problem may be the format of the data that comes from
an Access hyperlink field (which contains two parts - the link text, and
the display text). But maybe we can cross that bridge if you come to it,
or you can search for my previous messages on that subject.
Peter Jamieson

http://tips.pjmsn.me.uk
 
A

Amy E. Baggott

I'm actually having the opposite problem. The text changes, but the
underlying hyperlink does not. This is a problem because the link is
supposed to take the recipient to a personalized form on our web site for
them to submit their housing request.

When I right-click and click "update field" on an individual record, it
updates the underlying link, but that can be a pain in the butt when you have
a lot of them to send out.

Any ideas?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Peter Jamieson said:
From a recent message:

<<
The other problem is how to get Word's Display text to update.
Typically, when you /update/ a hyperlink field in Word, only the link
text ever changes. So if you have a column like this

myhl
http://www.mysitea.xxx
http://www.mysiteb.xxx

and you use a nested field like this;

{ HYPERLINK { MERGEFIELD myhl } }

then the link text will be updated but not the display text. That's fine
if you /want/ the display text to be the same in every record, e.g.
"Your documents"

However, I have looked at this again and the following seems to work in
Word 2007 - and perhaps earlier versions of Word. Let's suppose first
that the link text matches the display text in each record.

1. Insert the nested field as above (you need to use ctrl-F9 to insert
each pair of the special field braces {} )

2. Select the nested fields and use F9 to update the result.

3. Use Alt-F9 to dislpay the result. You should see a "display text",
typically underlined in blue. Let's say it is

www.mysitea.xxx

4. Click after the first character of the display text (if you have Word
set up so that hyperlinks are followed on an ordinary click rather than
the default ctrl-click, the link will be followed, but when you come
back to Word, the insertion point should be where you clicked).

5. Use ctrl-F9 to insert a pair of the special field braces { }

6. Between the braces, type MERGEFIELD, then the name of the field you
want to use for the display text., so you end up with e.g.

w{ MERGEFIELD myhl }ww.mysitex.xxx

7. Delete the old display text so you just end up with the { MERGEFIELD
myhl }

8. merge to a new document and test the new links.

If you need different display and link texts for each record, you will
need e.g.

{ HYPERLINK { MERGEFIELD mylinktextfield } }

and a separate display text field, e.g.

{ MERGEFIELD myhl }

or

{ MERGEFIELD mydisplaytextfield }

(substituting your own field names).

I have not tried this approach with versions of Word earlier than Word
2007, or with merges to email. I don't remember getting it to work
before - perhaps I did not do quite the right thing, or maybe something
has changed.

Otherwise, it does seem to work, and it even works after you save/close
and re-open the document (not always the case when you're dealing with
links in Word). To me, a significant problem is that it's not very
maintainable - it's difficult to see what you have done because when you
Alt-F9, you only get to see the nested [ HYPERLINK } field, not the
"display text" field.

I would be interested to hear if it works for you. If not, the only
other approach I know is to use VBA to maintain the complete link,
typically using Word's MailMerge events so that the VBA runs once for
each record in the data source. You can probably find examples of that
by searching groups.google.com for peter jamieson hyperlink
However, another problem may be the format of the data that comes from
an Access hyperlink field (which contains two parts - the link text, and
the display text). But maybe we can cross that bridge if you come to it,
or you can search for my previous messages on that subject.
Peter Jamieson

http://tips.pjmsn.me.uk

Vetruvian said:
I'm using Word 2007 and I've created a mailmerge, using Access 2007 records.
One of the Access fields is a hyperlink wich changes for each message to a
defferent web page (20 webpages in total).
I've entered the following nested fields in my document:
{ HYPERLINK {MERGEFIELD "MYLINK"} \* MERGEFORMAT}
This works only the first time correctly. After I save the document and open
it again, Word treats the nested field as a static hyperlink. Pressing ALT+F9
does not return the nested field.
Any thoughts an that? I'm puzzled.
 
P

Peter Jamieson

FWIW I just tried the technique I described in my previous message in a
merge to email using Word 2007 (SP2), Outlook 2007 and HTML format
e-mail, and it seemed to work. Unfortunately that is no guarantee that
the same thing will work with earlier versions of Office but
a. if you aren't using 2007, let me know which version and I will have
a look
b. if you are using 2007, how exactly are you inserting your hyperlink
fields at the moment?

Peter Jamieson

http://tips.pjmsn.me.uk
 
A

Amy E. Baggott

I am using Word 2007, merging from an Excel 2003 spreadsheet. The hyperlink
in question is
http://www.buildersshow.com/Exhibitors/ExhibitorHotelRequest.aspx?pin={
MERGEFIELD "CGI CompanyID" }. There is an alternate link for them to use if
they don't want rooms that is set up similarly. I just copied and pasted
them from last year's Word 2003 file. I'm not sure any more how I originally
set them up. When I merge to a new document or preview results, the display
text shows each individual's CGI CompanyID, but the link remains the same
(using the first company's CGI CompanyID, unless I right-click on the ID
number within the field and click Update Field for each link. It is
beginning to drive me bats.
 
P

Peter Jamieson

It is beginning to drive me bats.

Yes, lots of things in mailmerge are nothing like as simple as they
should be.

I have checked again and I think you will have to use some VBA to update
the link text before each email is sent. I'll get back to you on this
a.s.a.p. as the code I used to have needs to be revisited.


Peter Jamieson

http://tips.pjmsn.me.uk
 
P

Peter Jamieson

OK, I believe you will need to use VBA to solve this one.

One way to do it - not well tested at present - is to use Word's
MailMerge events to update the hyperlinks prior to sending each email.

The following sample code is intended to let you create the link texts
and display texts you want for a number of hyperlinks, as long as you
can create those texts using a combination of ordinary text and fields.

Let's say you want to generate hyperlinks using the pattern you mentioned:

http://www.buildersshow.com/Exhibitors/ExhibitorHotelRequest.aspx?pin={
MERGEFIELD "CGI CompanyID" }

To do that using this example you would need a Mail Merge Main document
with the following things in it:
a. a bookmark called BOOKMARK_link1 at the location where you want to
insert the Hyperlink (You can use Insert->Bookmark to insert this).
b. a SET field that creates the link text. This could be a nested
field like this:

{ SET LINK_link1
"http://www.buildersshow.com/Exhibitors/ExhibitorHotelRequest.aspx?pin={
MERGEFIELD "CGI CompanyID" }" }

b. a SET field that creates the display text. This could be a nested
field like this:

{ SET DISPLAY_link1
"http://www.buildersshow.com/Exhibitors/ExhibitorHotelRequest.aspx?pin={
MERGEFIELD "CGI CompanyID" }" }


So I suggest that you...

1. Copy your existing merge document. Connect the copy to your data
source, and the above fields and bookmark. You do not actually need a
Hyperlink field, but if you want, you can insert one and "bookmark" it
with the bookmark BOOKMARK_link1

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)
' The Hyperlinks we want to replace will
' have names starting with BOOKMARKPREFIX
' e.g. BMK_link1
Const BOOKMARKPREFIX = "BOOKMARK_"
' if we want to insert a display text for link1,
' create a bookmark called DISPLAY_link1
Const DISPLAYPREFIX = "DISPLAY_"
' if we want to insert a link text for link1,
' create a bookmark called LINK_link1
Const LINKPREFIX = "LINK_"
' Could also have bookmarks for the
' subaddress, screentip and target

Dim bmk As Word.Bookmark
Dim i As Integer
Dim j As Integer
Dim strSavedNames() As String
Dim hyp As Word.Hyperlink
Dim rng As Word.Range
Dim strRoot As String
' We need to use Variants for the Hyperlinks.Add
' method or it may crash
Dim varDisplay As Variant
Dim varLink As Variant

' Gather the names of the bookmarks
' we need to update.
j = 0
For Each bmk In Doc.Bookmarks
If UCase(Left(Trim(bmk.Name), Len(BOOKMARKPREFIX))) _
= UCase(BOOKMARKPREFIX) Then
j = j + 1
ReDim Preserve strSavedNames(j)
strSavedNames(j) = bmk.Name
End If
Next

' Now process the names
For i = 1 To j
strRoot = Trim(Mid(Trim(strSavedNames(i)), Len(BOOKMARKPREFIX) + 1))
If Len(strRoot) > 0 Then
Set bmk = Doc.Bookmarks(strSavedNames(i))
Set rng = bmk.Range
If rng.Fields.Count > 0 Then
rng.Fields(1).Delete
End If
varDisplay = ""
varLink = ""
If Doc.Bookmarks.Exists(DISPLAYPREFIX & strRoot) Then
With Doc.Bookmarks(DISPLAYPREFIX & strRoot).Range
' update any fields in the bookmark
' (this should update the SET field)
.Fields.Update
' NB we do not want the result of the SET field
' - we want the value of the bookmark that the SET creates
.TextRetrievalMode.IncludeFieldCodes = False
varDisplay = .Text
End With
End If
If Doc.Bookmarks.Exists(LINKPREFIX & strRoot) Then
With Doc.Bookmarks(LINKPREFIX & strRoot).Range
.Fields.Update
.TextRetrievalMode.IncludeFieldCodes = False
varLink = .Text
End With
End If

' Insert the new hyperlink
Set hyp = Doc.Hyperlinks.Add(Anchor:=rng, _
Address:=varLink, _
TextToDisplay:=varDisplay)
' re-insert the bookmark
hyp.Range.Bookmarks.Add Name:=strSavedNames(i)

' tidy up
Set rng = Nothing
Set bmk = Nothing
Set hyp = Nothing
End If
Next
End Sub

You will probably need to fix lines that have wrapped.

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. Also,
once you have enabled the events, they apply to any document until
either you or Word has disabled them again.

Now, if you need to generate more hyperlinks that need to be updated in
this way, you can insert a bookmark called BOOKMARK_link2 with SET
fields that create LINK_link2 and DISPLAY_link2, and so on.

As I say, it isn't well tested but it's probably a better starting point
than the code I used to have for this.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top