How do I include a personalised hyperlink in a mail-merged e-mail

[Andrew J Cooper]

I'm trying to mail-merge to e-mail in Word 2003. I can get the merge to work
fine and create the messages I want. The one thing I haven't been able to
work out is how to include a link in the merged messages where the URL is
varied by a merge field. Is this possible?

 

[Graham Mayor]

Can you quote an example?
Is the variable message an attachment?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Andrew J Cooper]

I have an Excel Spreadsheet with the merge data. One of the columns contains
a URL which is different for each record. I'm trying to use Word to merge
this information into an e-mail message so that each recipient gets a message
with a link to the URL that is applicable to them.

I can just embed the merge field for the URL, in which case the e-mails
contain the correct URL's, but they're not active links.

I've tried playing with manually creating the Hyperlink field tag in Word
using Ctrl-F9, and have been able to create an active link in the merged
e-mails, but the target URL for the link becomes constant.

 

[Peter Jamieson]

As far as I know, the only way to change the target URL for each message is
to use VBA, and since you're merging to e-mail, the only ways to do that are
a. use VBA and Word Events to insert the hyperlink you need for each
e-mail. This is tricky (worse than "Normal" VBA).
b. (theoretically) merge each record to a new document, then fix the
hyperlink using VBA, then use Doug Robbins' merge to e-mail with attachments
approach, as per

http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.htm

but that is probably even trickier overall.

For option (a), the fllowing approach used to work for ordinary merges but I
haven't tried it recently.

1. Create a new document, connect it to your data source, and insert one
merge field and a bookmark named "mybm"

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)
Dim dt as String
Dim lt as String
Dim h as Hyperlink
Dim r as Range


' set the range variable to our placeholder bookmark
Set r = Doc.Bookmarks("mybm").Range

' delete any existing text (this is needed for records after record 1)
r.Text = ""


' construct the link text that you want. I'm assuming your data source has
fields called idfield and namefield.
lt = http://www.testsite.com?id= & _
Doc.MailMerge.DataSource.DataFields("idfield") & _
"&name=" & _
Doc.MailMerge.DataSource.DataFields("namefield")
' set up the display text that you want. If it should be the same as the
link text, do that:
dt = lt


' insert the hyperlink you want
Set h = Doc.Hyperlinks.Add(Anchor:=r, Address=lt, TextToDisplay:=dt)


' Set mybm to "cover" the inserted link so it is easy to delete the old
hyperlink


Doc.Bookmarks.Add Name:="mybm", Range:=h.Range


Set r = Nothing
Set h = Nothing


End Sub


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.


Peter Jamieson

 

[Adri Rietjens]

Ever used Alt+F9 when constructing the mailmerge document?
When using this combination you toggle between Fieldresult and Fieldcode.
Insert a hyperlink to a file on your computer. It does not matter which
file. Next toggle Alt+F9. Now you see the Fieldcode used for hyperlinks.
Insert the Mergefield (also a fieldcode) containing a reference to the unique
URL in the fieldcode.

That's all...Now you can merge a unique url for every recepient.

 

[Peter Jamieson]

There are a number of spearate issues here, and in this case I may have
answered the wrong one.

First of all, if you merge to e-mail sending a hyperlink of any kind, the
recipient does nto necessarily see a clickable hyperlink. It depends on the
mail format that you specify (HTML, plain text, attachment) and the
capabilities and options in the recipient's e-mail client. For example, if
you merge to plain text via Word 2003/OL2003, as far as I can see if you
also use OL2003 to receive/view the e-mail, it does not display a clickable
link no matter what you do in Word, despite the fact that in this scenario,
OL2003 thinks it has received an HTML format e-mail (and as far as I can
tell, actually sends an HTML format e-mail unless you happen to open the
plain text e-mail in the out basket before it is sent).

That may be enough to "solve" the original question.

However, there is another problem if you want to show different display text
for the link for each e-mail: Once you have inserted a HYPERLINK field, Word
never changes the display text, and there is no switch that allows you to
modify the display text afterwards. As far as I know, if you want to change
the display text, you have to re-insert the HYPERLINK each time and the only
way to do that when you merge to e-mail is either to merge first, then
replace the display texts, then send e-mails (complicated) or use Word
events during the merge.

Peter Jamieson