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.
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Don Soucy" wrote in message
...
Graham
It contains an elaborate string with a simple tag:
"Download Bio-Energetic Relaxation.mp3"
is the tag visible to the reader, while :
a
href="http://www.upperroomcomm.com/cgi-bin/dwn.pl?ID=MP3-relax64k.mp3&TXN=COMPLIMENTARY2009-0001"
title="http://www.upperroomcomm.com/cgi-bin/dwn.pl?ID=MP3-relax64k.mp3&TXN=COMPLIMENTARY2009-0001"Download
Bio-Energetic Relaxation.mp3/a
is the code behind the tag.
I want to be able to send links to each member with a different TXN ID ie
COMPLIMENTARY2009-0001, 0002, 0003 etc, etc.
I have the source data in an EXCEL spreadsheet with a column containing
the
unique html data but it won't merge into the main document.
It either shows only the tag, or shows the code as text rather than a
hyperlink consisting of tag with code visible with mouseover and active on
click.
The data in EXCEL appears as just described, but after merge it does not.
Don Soucy
"Graham Mayor" wrote:
What *exactly* does the third column contain.
--
Graham Mayor - Word MVP
My web site www.gmayor.com
Word MVP web site http://word.mvps.org
Don Soucy wrote:
I want to use a data file consisting of 3 columns, a name, an e-mail
address and a unique html link and merge it into an e-mail message.
When I try, the html link loses the underlying format and appears as
plain text.
I've tried saving the data file from Excel in a variety of formats
with no success.
I've treid formatting the {{LINK}} placeholder as HTML and still no
luck
Any suggestions?
Thanks