none
MailMerge with Hyperlink question

    Question

  • Good Evening All,

    I am attempting to do a mail merge document that has a hyperlink embedded.  The link is HUGE and UGLY and I would like to show something like 'Click Here'.  I am getting the link from Excel but when I use the shorter 'Click Here' that I created in Excel (VBA using the Hyperlink Function) what I expect to be a link is just plain text.  Any ideas about how I can either create the shorter link in the MailMerge or get my shorter link from Excel to show as a link?  I am using Office 2007.

    Thanks in Advance for your help,

    Sam

    Monday, March 14, 2011 11:40 PM

Answers

  • Hi Diannk,

    Here's how you can do it, quite simply:
    1. Disregarding mergefield issues for the moment, insert a hyperlink into the document in the normal way, choosing whatever 'Click Here' text you want in the 'Text to display' box.
    2. Select the inserted hyperlink and press Shift-F9 to expose its field code
    3. Replace everything in the field after 'HYPERLINK' with your mergefield.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    • Marked as answer by Bessie Zhao Friday, April 08, 2011 9:32 AM
    Thursday, March 31, 2011 8:54 PM

All replies

  • Hi Sam,

    Thanks for posting in the MSDN Forum.

    Would you clarify some questions?

    1.                  What dose “MailMerge” mean? Do it mean that you want to put two different mail item’s content into one mail item?

    2.                  What is the relation between the excel hyperlink and MailMerge?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 16, 2011 9:05 AM
    Moderator
  • Tom,

    I am using WORDs MailMerge to get infomration from Excel and send emails.  I can get all the information but the url is very long - almost 300 characters and I would like to send a shorter url (maybe something like 'Click Here'.  I thougt I could use the Hyperlink in Word but each e-mail will contain a different url and having to manually change the link each time is not a good process.  (There are over 300 emails to be sent.).   

    Thursday, March 31, 2011 1:32 PM
  • Hi Diannk,

    Here's how you can do it, quite simply:
    1. Disregarding mergefield issues for the moment, insert a hyperlink into the document in the normal way, choosing whatever 'Click Here' text you want in the 'Text to display' box.
    2. Select the inserted hyperlink and press Shift-F9 to expose its field code
    3. Replace everything in the field after 'HYPERLINK' with your mergefield.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    • Marked as answer by Bessie Zhao Friday, April 08, 2011 9:32 AM
    Thursday, March 31, 2011 8:54 PM
  • Hey Paul,

    I need to do the same thing as Diannk, however, my hyperlink still shows the demo link I put in. Here's what I am doing.

    My mail merge template has a FILLIN field, asking for the URL to be put in the email.

    I need to put this URL (entered through FILLIN) as a link for, say, Click Here.

    As I understand from your post, I first insert a hyperlink, for which I need to give a dummy URL or something. Here's the problem, after step 3 from your solution, the hyperlink still shows the dummy URL when hovered on, but on Shift +F9 shows FILLIN URL.

     

    Any help would be greatly appreciated.

    Wednesday, June 01, 2011 3:35 PM
  • I have a similar problem:

    I would like to take a long hyperlink (from the excel spreadsheet) that is different for each letter and after merging have each appear as "ClickHere".

    It sounds like the directions above intend that as the outcome however I can see to get it to work.I thought I had - but the link was the same url for every letter - NOT the different link from each record in the excel file.

     

    Also when I do "shift F9" nothing happens?

     

    Wednesday, June 01, 2011 8:54 PM
  • I need to do the same thing as Diannk, however, my hyperlink still shows the demo link I put in. Here's what I am doing.

    My mail merge template has a FILLIN field, asking for the URL to be put in the email.

    I need to put this URL (entered through FILLIN) as a link for, say, Click Here.

    As I understand from your post, I first insert a hyperlink, for which I need to give a dummy URL or something. Here's the problem, after step 3 from your solution, the hyperlink still shows the dummy URL when hovered on, but on Shift +F9 shows FILLIN URL.


    Hi gb3bg,

    Did your original hyperlink display the 'Click Here' prompt? That's what the technique I described produces. Your 'real' URL should then display when the 'Click Here' prompt is hovered over - after the mailmerge has completed.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    Thursday, June 02, 2011 2:30 AM
  • It sounds like the directions above intend that as the outcome however I can see to get it to work.I thought I had - but the link was the same url for every letter - NOT the different link from each record in the excel file.

    Also when I do "shift F9" nothing happens? 

    Hi ViewsFromAbroad,

    That suggests all you've done is create a HYPERLINK field with a MERGEFIELD inside it, without first creating the 'Click Here' prompt as described in the directions I posted.

    Shift-F9 should work in either document if you first select the HYPERLINK field.


    Cheers
    Paul Edstein
    [MS MVP - Word]
    Thursday, June 02, 2011 2:52 AM
  • Thanks! I'll try again in the morning!

    I appreciate the help!

    VFB

    Thursday, June 02, 2011 2:58 AM
  • Hello Again:

    I have tried to follow your instructions - and tried 4 different interpretations of what you laid out and i cannot get it to work properly!

    Perhaps you could help clarify where I am going astray?

    I created a "Click Here" (using the "Insert Hyperlink" feature on the Insert tab of Word 2010).

    For the "Address" in that dialogue box - what should I put in? the first records actual link? or something else? or does it matter?

    Here's whgat i tried. I put the link to the first document:  www.xyz.edu/Sampledoc1.pdf

    When I "hover" over the "ClickHere it shows this.

    I then select the "ClickHere" and using shift F9 I reveal the field code which is:

     { HYPERLINK "HTTP://www.xyz.edu/Sampledoc1.pdf" }

     

    I then replace "everything to the right of HYPERLINK with the mergefield and it looks like this (basically the same as before except the quotes around the name of the first hyperlink are gone)

     

     { HYPERLINK  HTTP://www.xyz.edu/Sampledoc1.pdf }

    If I leave it revealed and select "finish" and merge to a document I get the link above with quotes around it for every record page (I.E. I get record 1's link on every page:

     

     { HYPERLINK "HTTP://www.xyz.edu/Sampledoc1.pdf" }

     

    If I before merging I  shift-F9 back to pretty "ClickHere" mode and merge then I get "ClickHere" looking pretty but the link still is to just that first records pdf - NOT the subsequent records' different links (Sampledoc2.pdf etc etc)

    I'm sure I'm doing something dumb! Please help!
    Thanks,
    VFB
    Thursday, June 02, 2011 1:09 PM
  • Hi ViewsFromAbroad,

    Provided you achieve the 'Click Here' display text, it really doesn't matter what the initial HYPERLINK destination is - that's the part that you'll be replacing.

    Now, suppose you've done the first bit and pressing Shift-F9 reveals { HYPERLINK "HTTP://www.xyz.edu/Sampledoc1.pdf" }. Simply delete everything inside the field after 'HYPERLINK' so you end up with { HYPERLINK }. Now insert your MERGEFIELD, via the Insert|Mergefield dropdown, after 'HYPERLINK' so you end up with { HYPERLINK «Target»}, where 'Target' is you data field's name. With that done, your mailmerge should be ready to go.

     


    Cheers
    Paul Edstein
    [MS MVP - Word]
    Thursday, June 02, 2011 1:28 PM
  • Belated thanks!

    I'm still a little fuzzy on it all but I seem to be able to get it to work!

    Cheers!

    VFB

    Wednesday, June 08, 2011 6:32 PM
  • Hi Paul,

    I think your suggestion is very useful and simple. However, after trying quiet a few times, i face an irritating issue.

    When i create the Hyperlink "Click here" using insert hyperlink and give any dummy link just to create this. Then shift F9, delete all the text after HYPERLINK and finally insert my merge field which is a custom link.

    When i send the mails, the HYPERLINK shows as "Click here" but it links to the original dummy link i used to create it....

    Wednesday, September 11, 2013 7:22 AM
  • This does not work.

    In Office 2010

    Mail Merge to Email with an Excel data source. 

    Various links for each record.

    Insert Hyperlink with a dummy url goes in well.

    Replacing the dummy url in the toggled hyperlink with the merge field looks like it should work but it doesn't.

    When the mail merge to email is completed, all the links that Click Here produces are to the dummy url.

    I wish this worked because it would be nice.

    Kathy

     

    Friday, September 13, 2013 7:23 PM
  • The solution does work for the issue raised in the original post, which is for a mailmerge to a document. Your issue concerns merging to email, which is obviously different. The solution works for the former but not for the latter.

    For an email merge, try using the HTML output format without the hyperlink field. That should output the actual email address rather than the 'click here' link. It may even provide the email address in a hyperlink format (not sure about this as merge to email is not something I use).


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Tuesday, October 01, 2013 2:06 AM