Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    559
    Thanks
    59
    Thanked 5 Times in 4 Posts
    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?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Gold Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,457
    Thanks
    7
    Thanked 213 Times in 202 Posts
    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

  4. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,815
    Thanks
    0
    Thanked 166 Times in 153 Posts
    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
    [MS MVP - Word]

  5. #4
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    559
    Thanks
    59
    Thanked 5 Times in 4 Posts
    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.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,815
    Thanks
    0
    Thanked 166 Times in 153 Posts
    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
    [MS MVP - Word]

  7. #6
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    559
    Thanks
    59
    Thanked 5 Times in 4 Posts
    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 ???
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,815
    Thanks
    0
    Thanked 166 Times in 153 Posts
    Hi Lou,

    Your:
    { { HYPERLINK { MERGEFIELD "Reunion_Web_Site" }} }
    should be:
    { HYPERLINK { MERGEFIELD "Reunion_Web_Site" }}
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #8
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    559
    Thanks
    59
    Thanked 5 Times in 4 Posts
    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.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  10. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,815
    Thanks
    0
    Thanked 166 Times in 153 Posts
    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
    [MS MVP - Word]

  11. #10
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    559
    Thanks
    59
    Thanked 5 Times in 4 Posts
    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?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  12. #11
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    559
    Thanks
    59
    Thanked 5 Times in 4 Posts
    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.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  13. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,815
    Thanks
    0
    Thanked 166 Times in 153 Posts
    Quote Originally Posted by Lou Sander View Post
    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
    [MS MVP - Word]

  14. #13
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    559
    Thanks
    59
    Thanked 5 Times in 4 Posts
    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.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  15. #14
    4 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    559
    Thanks
    59
    Thanked 5 Times in 4 Posts
    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.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  16. #15
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,815
    Thanks
    0
    Thanked 166 Times in 153 Posts
    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
    [MS MVP - Word]

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •