Hyperlinks in a Mail Merge Document – Word 2003

Topic

New Reply

I have a mail merge Directory of about 200 items. It gets its data from an Excel worksheet. Two of the twelve fields on the worksheet are live hyperlinks (one email address, one URL). I’d like them to be live hyperlinks in the final mail merge document.

Is there a way?

Reply | Quote

Replies

You could put the raw code for a field (the data shown when you press Alt F9) and merge the data? Not tested, just a thought.

cheers, Paul

Reply | Quote

Hi Lou,

Simple – enclose your mailmerge field in a HYPERLINK field, thus:
{HYPERLINK «Link»}
where ‘Link’ is the name of the data field containing the hyperlink.

Note: The field brace pairs (ie ‘{ }’) for the above example are created via Ctrl-F9 – you can’t simply type them or copy & paste them from this message. Likwise, you can’t type or copy & paste the chevrons (ie ‘« »’) – they’re part of the actual mergefields, which you can insert from the mailmerge toolbar.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I’m reasonably familiar with inserting mail merge fields, less familiar with inserting Word fields, and I can’t get this to work. There are no chevrons in any of my fields. Maybe they are some sort of Aussie thing.

If I’m in my mail merge document (after ALT+F9 has been pressed to show the details of the fields) and try, from Word’s menu bar, Insert > Field… > Hyperlink [OK], I get { HYPERLINK * MERGEFORMAT }, but it lacks the gray background that all my mail merge fields have.

When I press ALT+F9 to return to “plain text” view, I get Error! Hyperlink reference not valid. in the place where I’ve inserted the field described above.

Give me some more specific instructions if you can, please.

Reply | Quote

Hi Lou,

I guess you didn’t read the note at the end of my post …

After inserting the hyperlink field and getting:
{ HYPERLINK * MERGEFORMAT },
you can delete the ‘* MERGEFORMAT’. Then, insert the mergefield for the hyperlink data into the hyperlink field so that you get either:
{ HYPERLINK «Link»}
or
{ HYPERLINK { MERGEFIELD Link } }
Having done that, you can update the field. You may see a field error message at this stage, but that’s unimportant – it’ll only reflect the fact that a mergefield, per se, doesn’t represent a valid hyperlink. After running your mailmerge, the active hyperlinks should appear in the output file.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I read the note, but the chevrons confused me, and things did not behave as expected.

Here’s what I did just now, all after pressing ALT+F9 so I can see the fields in detail:

Press CTRL+F9. That gives, with a gray background, { } with two spaces between the curly brackets, and the cursor blinking between the two spaces.

Insert > Field… > Hyperlink [OK]. That gives, with a gray background, { { HYPERLINK * MERGEFORMAT } }

Delete * MERGEFORMAT

In its place, use the MailMerge toolbar to insert the mail merge field ‘Reunion Web Site’. That gives, with a gray background,

{ { HYPERLINK { MERGEFIELD “Reunion_Web_Site” }} }

Upon pressing ALT+F9, that entire line disappears. Using the MailMerge toolbar to skip through the records, both in “show the field name” mode and “show the contents of the field” mode, the line does not return.

Upon using the MailMerge toolbar to Merge to a New Document, the ‘Reunion_Web_Site’ data does not appear, either as plain text or as hyperlinks.

Obviously something isn’t right, but I don’t know what it might be. Maybe I’m missing something. Maybe your Word doesn’t work like my U.S. Word 2003, or ???

Reply | Quote

Hi Lou,

Your:
{ { HYPERLINK { MERGEFIELD “Reunion_Web_Site” }} }
should be:
{ HYPERLINK { MERGEFIELD “Reunion_Web_Site” }}

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

I can get it to look that way, but the stuff between the curly brackets doesn’t have the gray background. Merging to a new document puts nothing at all into the document.

I’m getting the “HYPERLINK” etc. by using Insert > Field > Hyperlink, then inserting the MailMerge field in place of the MERGEFORMAT stuff. (There is no previous use of the CTRL+F9 in your note. If I start with CTRL+F9, I get the extra curly brackets.)

In non-mailmerge use of Word’s Insert > Field > Hyperlink process, there is a dialog box that one gets that asks for both the Text to display and the Address. I’m not using that dialog here. Maybe that’s the trouble.

I can make hyperlinks work fine in those cases. What I’m so far totally unable to do is to get Word to pick up the hyperlink data from the MailMerge source, rather than what I type into that dialog box.

In the non-mailmerge case, ALT+F9 displays a non-gray { HYPERLINK “http://www.foo.com” } instead of the ‘hyperlink text’ in hyperlink character formatting (in my case, blue with an underline). From that I can see that one doesn’t necessarily see everything by using ALT+F9.

What I want to do is to have my mailmerge document show http://www.foo.com in hyperlink character formatting, and when one presses CTRL and clicks it, one is taken to http://www.foo.com, with the http://www.foo.com (or whatever) picked up from the mail merge data source.

I don’t see what I’m doing wrong here. I suspect that the “missing chevrons” have something to do with it. You talk about ’em, but I don’t see ’em. I seem to recall that there’s a setting in Word that controls how fields are displayed; maybe I don’t have it set correctly for what we are trying to do.

Reply | Quote

Hi Lou,

Unlike most other fields, HYPERLINK fields don’t display as grey when the field code is exposed.

Try this approach:
1. Start over by creating a new pair of nested fields, by simply pressing Ctrl-F9 twice. You should see: { { } }.
2. Fill in the fields so that you end up with: { HYPERLINK { MERGEFIELD “Reunion_Web_Site” } }
3. Select the filed and press F9 to update it. If you still see the field code, Press Alt-F9 to toggle the display.
4. Run your mailmerge.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

How do I put that ‘HYPERLINK’ in there? Can I just type it in, or do I have to do something different?

Edited: I’ve done as you asked (or at least as I understand it), and I still get nothing at all in the merged document.

I do all my editing with ALT+F9 pressed, so the fields show in detail. I hope that’s OK.

When I press CTRL+F9 twice, I do see the nested fields.

Then I’ve just typed in ‘HYPERLINK’ and ‘MERGEFIELD “Reunion_Web_Site”‘. Maybe that’s not the right way to do it.

I am still concerned about the absence of chevrons. You haven’t commented on it. Is that perhaps what’s wrong here?

Reply | Quote

How do I put that ‘HYPERLINK’ in there? Can I just type it in, or do I have to do something different?

Edited: I’ve done as you asked (or at least as I understand it), and I still get nothing at all in the merged document.

Hi Lou,

Yes, you type ‘HYPERLINK’

OK, since nothing is showing, try putting quotes around the mergefield:
{ HYPERLINK “{ MERGEFIELD “Reunion_Web_Site” }” }

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Sheesh! I just figured out about the chevrons. They are there when the ALT+F9 toggle is OFF. I’ve seen ’em dozens of times.

The mystery is starting to clear, but I still have no success in my merged document. It must have something to do with how I’m getting the word HYPERLINK in there.

Reply | Quote

Absolutely nothing. I DID notice that the merged document is considerably different, depending on the state of ALT+F9 when I merge to it.

I’m starting to think that the state of ALT+F9 and the state of the ‘ABC’ button on the MailMerge toolbar might have a lot to do with this.

There are some other suspicious things going on here. I have considerable experience making mail merge directories with Excel worksheets as their data source. I do it routinely, and my system behaves routinely.

With this project, for some reason the mail merge document has trouble finding its data source. I’ll get it set up to use the Excel worksheet, and all is well. But sometimes when I re-open it, instead of immediately linking to Excel, it gives me a dialog box that seems to want to find an Access database. I click through it a time or two, and things ultimately settle down, and the document uses the Excel worksheet as its data source. This is NOT something that happens with other documents, though I may have seen it a time or two.

Reply | Quote

With this project, for some reason the mail merge document has trouble finding its data source. I’ll get it set up to use the Excel worksheet, and all is well. But sometimes when I re-open it, instead of immediately linking to Excel, it gives me a dialog box that seems to want to find an Access database. I click through it a time or two, and things ultimately settle down, and the document uses the Excel worksheet as its data source. This is NOT something that happens with other documents, though I may have seen it a time or two.

A couple of things caught my attention. The first is that you said that the Excel document is actually a GoogleDocs document. I presume you are working from a local copy of the Google document. In any case, might it be that some slight incompatibility between GoogleDocs and genuine Excel documents is at the root of the problem?

The second thing I noticed is that you said that Word sometimes gets disconnected from the Excel document. Assuming that you actually have Excel, even if the document in question is maintained on Google, I have had much better luck with connections to Excel documents staying connected (and working flawlessly) when I choose DDE, instead of ODBC. DDE may be “old hat,” but it still works, and isn’t going away any time soon.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

I just started from scratch with a new, Excel workbook. One sheet, three records of four fields each. Also did a new mail merge document that contains all four fields, plus an attempt at a Hyperlink from one of the fields whose text is like ‘http://www.foo.com’

Same result: The non-hyperlink fields print fine, but there’s nothing at all from the attempted hyperlink field.

I’m off to bed now. It is 2:32 AM here. Thanks for sticking with me. Maybe morning will see a solution.

Reply | Quote

Hi Lou,

Perhaps you could attach a copy of your mailmerge main document (sanitized if necessary), plus a cut-down copy of your Excel data source.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Attached is a ZIP of both of them.

The Excel file is exported from a GoogleDocs file, and some of the cell formats are a bit unusual, but overall it seems to work.

Reply | Quote

Hi Lou,

The field code {HYPERLINK “{ MERGEFIELD “Reunion_Web_Site” }”} does indeed create a hyperlink, but you need to update the fields afterwards (eg Ctrl-A, then F9).

However, although the correct addresses are embedded in the HYPERLINK fields, they always resolve to the first merged hyperlink address. It seems there’s something terribly flakey about the way Word processes the field code.

Sorry.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Oh, well — we gave it an intercontinental try.

That whole situation is flakey, and there are too many variables affecting what you see and how you see it. Can’t complain, though — Word gives us a tremendous mail merge capability.

Thanks for your help.

In the end, I’ll only have a few hundred records max, each of which is good for a year or so. I’ve got a keyboard macro program that can help me manually turn those addresses into links.

Reply | Quote

Hi Lou,

After the merge, you can format both the email addresses and web addresses as hyperlinks with:

Sub ApplyHyperlinks()
ActiveDocument.Range.AutoFormat
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Tried it. It worked. It’s a miracle! 😉

Thanks!

Reply | Quote

Even miracles have their drawbacks. Autoformat changes the formatting of EVERYTHING, not just the hyperlinks. That’s bad in my application.

How do I exercise more control over Autoformatting? I’ve consulted a reference book about the Tools > AutoCorrect… menu item in word, and experimented a bit, but I still don’t have perfect control. Maybe there’s something in VBA. I can probably puzzle it out, but I use this stuff infrequently, so any tips from others are invaluable.

I’d welcome any guidance you may have on gaining good control of the Autoformat part of the macro above.

Reply | Quote

How about something like:

Code:

Sub ApplyHyperlinks()

Dim fld As Field
    
    For Each fld In ActiveDocument.Fields
        If fld.Type = wdFieldMergeField Then fld.Result.AutoFormat
    Next fld

End Sub

I haven’t tested this, so please use with caution.

Reply | Quote

It also looks as though the original “simple” version above doesn’t reformat paragraphs that aren’t in the ‘Normal’ format.

So far, I’m thinking that the info I’ve picked up on my own and from the lounge will be enough to make my project do what I want it to do.

Truly, we live in a time of miracles.

Reply | Quote

The data source document IS in Excel 2003. Its data originates with a Google Docs online form, though. When someone posts a new entry to the online form, it ends up in a Google Docs worksheet. I periodically export the Google worksheet to my desktop, then copy the new lines to an Excel worksheet where I can edit them (and they won’t be overwritten by a subsequent export). Once they’re edited, I use the Excel worksheet as the data source for my Word mailmerge ‘directory’. That’s when the (non-repeatable) data connection problem happens.

If the unwanted (and mysterious, to me) dialog box about Access connections comes up, I just keep dismissing it until it goes away and lets me choose my Excel data source. In future, I’ll look for a DDE choice there.

Experimentally I’ve learned that the unwanted dialog box only seems to come up when I have other Excel worksheets open. At least so far, if I close all Excel worksheets (including my data source), I don’t get the unwanted dialog box.

Reply | Quote

The data source document IS in Excel 2003. Its data originates with a Google Docs online form, though. When someone posts a new entry to the online form, it ends up in a Google Docs worksheet. I periodically export the Google worksheet to my desktop, then copy the new lines to an Excel worksheet where I can edit them (and they won’t be overwritten by a subsequent export). Once they’re edited, I use the Excel worksheet as the data source for my Word mailmerge ‘directory’. That’s when the (non-repeatable) data connection problem happens.

Thanks for clarifying that point, and, thereby, eliminating Google from the equation.

If the unwanted (and mysterious, to me) dialog box about Access connections comes up, I just keep dismissing it until it goes away and lets me choose my Excel data source. In future, I’ll look for a DDE choice there.

After you indicate that your data source is an Excel document, you should be offered a choice of connecting to Excel via ODBC or DDE. I’ve always chosen DDE, and been delighted with its fast and reliable performance.

Experimentally I’ve learned that the unwanted dialog box only seems to come up when I have other Excel worksheets open. At least so far, if I close all Excel worksheets (including my data source), I don’t get the unwanted dialog box.

I believe you just identified the key. I know this is true if the connection type is ODBC. However, I don’t think this happens if the connection is via DDE, though I can’t remember for sure, and it’s been several months since I did a mail merge with an Excel document.

David A. Gray

Designing for the Ages, One Challenge at a Time

Reply | Quote

I looked the last time it happened, and I wasn’t offered a DDE choice. If it happens again, I’ll post what I AM offered.

Reply | Quote