To do this, I think you will need to
a. use VBA and Word Events
b. split your hyperlink column intio two columns, one containing the
hyperlink "link text" (i.e.e the thing you are linking to), and the other
containing the "display text" (i.e. the thing that the recipient sees)
The following seems to work in Office 2003/WinXP with HTML format merges but
I have had difficulty so far in Office 2007:
Part (b) first. You can either split out the link text and display text
manually, or you can do it using Excel functions. Unfortunately I do not
think there is a built-in worksheet function that will do this, but you can
use Excel VBA to define two new ones as follows:
Function GetLinkText(HCell As Range)
GetLinkText = HCell.Hyperlinks(1).Address
End Function
Function GetDisplayText(HCell As Range)
GetDisplayText = HCell.Hyperlinks(1).TextToDisplay
End Function
Let's suppose your Excel column containing the hyperlink is, say, column C
and is called mylink.
Then you could insert column D, call it linktext, insert the following Excel
formula in cell D2, then propagate it down the column in the usual way:
=GetLinkText(C2)
Similarly, you could insert column E, call it displaytext, insert the
following Excel formula in cell E2, then propagate it down the column in the
usual way:
=GetDisplayText(C2)
When you recalculate your sheet, you should see the appropriate link and
display texts. Typically you will need to recalculate manually and save the
sheet before doing your merge (e.g. if you are using the default OLE DB
connection method to get the data, Word does not even open Excel and has no
way to recalculate the sheet)
Now Part (a). The folowing steps demonstrate how you can insert a single
hyperlink in your e-mails.
1. Create a new document, connect it to your data source, and insert one
merge field, and a space character. Select the space, and insert 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. This code assumes that you are
using fields named as in the description above:
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 linktext and displaytext
' NB uncharacterisally for Windows, these feld names must match the
' data source column names exactly - if the column name is LinkText, use
"LinkText"
' not "linktext"
' if some part of your link text or dispaytext is a constant, you
' can just store the variable parts in your data source
' and construct the texts you need here
lt = Doc.MailMerge.DataSource.DataFields("linktext")
' set up the display text that you want. If it should be the same
' as the link text, do that:
'dt = lt
dt = Doc.MailMerge.DataSource.DataFields("displaytext")
' delete any existing text (this is needed for records after record 1)
r.Text = dt
' insert the hyperlink you want
Debug.Print r.Text
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
d. Make sure you correct any broken lines etc.
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.
