[cjshaffer]

I have active hyperlinks in my Excel spreadsheet that I am using as a data
source for a mail merge. Is there any way to create the hyperlinks in Excel
that they will carry through the mail merge process?
Thanks,
cj

[Peter Jamieson]

As far as I know there is no way to insert a complete hyperlink in Excel
into Word during a mailmerge using the out-of-the-box mailmerge,
assuming that the "link text" (the URL you want to link to) and the
"display text" can be different from each other. The main problem there
is that only the display text reaches Word, no matter which "connection
method" you use (OLE DB, DDE, ODBC) to get your Excel data.

So, if you want separate link text and display text, you will need a
column for each one in your Excel sheet.

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

Peter Jamieson

http://tips.pjmsn.me.uk

cjshaffer wrote:
I have active hyperlinks in my Excel spreadsheet that I am using as a data
source for a mail merge. Is there any way to create the hyperlinks in Excel
that they will carry through the mail merge process?
Thanks,
cj