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
Amy E. Baggott wrote:
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.