[Peter Jamieson]

If you want, despam my e-mail (remove KillMAPS from
) and email me, as it's easier to send sample
files with macros etc. so you can see what's going on. I'm in the U.K.
timezone (i.e. it's late right now) but will probably be around for another
hour or so.

--
Peter Jamieson
http://tips.pjmsn.me.uk

"Don Soucy" wrote in message
...
Peter

I've treid to follow your directions but you are light years beyond me.

In splitting the Link, does the /a belong with Linktext or Displaytext?

I have never 'defined' function in Excel VBA.
nor even worked with them beyond the stock ones (sum etc).
not sure how to proceed here.

Your directions in Word VBA truly leave me in the dust.

I would attempt this route if I had a slim hope of it working.

Thanks

Don

"Peter Jamieson" wrote:

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