Email Mail Merge with Hyperlink as MrgField

[Finz2drght]

I created a document (Word 2002) to be used as an email to a group of people.
Each person should receive a different hyperlink.

I tried creating a new document and using the instructions found under MVP:
Ctrl F9, type Hyperlink and space then enter the mergefield, etc. The merge
keeps the same hyperlink for all people instead of using the individual links
associated with the record in my Excel document.

I can send the email as text and the link works, but I lose formatting. Is
there a way to send as HTML, retaining my formatting and allow each
individual to receive their associated hyperlink?

Will this feature improve with the 2007 version? Thanks in advance for your
assistance!

 

[Peter Jamieson]

To specify the display text, things are complicated because there is no
"switch"
in the HYPERLINK field that lets you specify the display text.

However, it may be worth checking to see whether the link text (target URL)
is actually different for each recipient, even though the hyperlink looks
the same.

Otherwise...

You may be able to do the merge using VBA and "Word Events", e.g. as follows
(I haven't tested this recently). Nasty, but even so I suspect this is the
easiest way to do it in Word.


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 a field called mylink for the link
lt = Doc.MailMerge.DataSource.DataFields("mylink")
' 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.