Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Don Soucy Don Soucy is offline
external usenet poster
 
Posts: 1
Default using html links as data in mail merge w/ office 2003

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


  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default using html links as data in mail merge w/ office 2003

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



  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Don Soucy[_2_] Don Soucy[_2_] is offline
external usenet poster
 
Posts: 4
Default using html links as data in mail merge w/ office 2003

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




  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Don Soucy[_2_] Don Soucy[_2_] is offline
external usenet poster
 
Posts: 4
Default using html links as data in mail merge w/ office 2003

Graham

This is the html link as it appears in third column of EXCEL
Download Bio-Energetic Relaxation.mp3

except in EXCEL it is a HYPERLINK (Blue underlined and when moused over
shows
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

Does this make it clearer?

Don

"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




  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor Graham Mayor is offline
external usenet poster
 
Posts: 19,312
Default using html links as data in mail merge w/ office 2003

As you have found, Hyperlinks when merged merely produce the display text
from the data source. I cannot think of any easy way of merging the
hyperlink. You could simply insert the URL without the hyperlink text into
your data source and that should work but see
http://support.microsoft.com/kb/287004 which is a fairly old link but may
point a way forward.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Don Soucy wrote:
Graham

This is the html link as it appears in third column of EXCEL
Download Bio-Energetic Relaxation.mp3

except in EXCEL it is a HYPERLINK (Blue underlined and when moused
over shows
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

Does this make it clearer?

Don

"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





  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default using html links as data in mail merge w/ office 2003

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





  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Don Soucy[_2_] Don Soucy[_2_] is offline
external usenet poster
 
Posts: 4
Default using html links as data in mail merge w/ office 2003

Graham

I tried that

I put "www.upperroomcom.com/cgi-bin/dwn.pl" etc in the Link field and it
still just merges as text. There seems to be no simple way to merge html.

my (very unelegant) solution is paste the HYPERLINK into the main document,
merge the Name & EMAIL, generate the merge mailing, then manually edit the
HYPERLINK code in each letter (ALT F9) before sending them.
Laborious when there are several hundred to do. :-) but what can you do.


Thanks

Don


"Graham Mayor" wrote:

As you have found, Hyperlinks when merged merely produce the display text
from the data source. I cannot think of any easy way of merging the
hyperlink. You could simply insert the URL without the hyperlink text into
your data source and that should work but see
http://support.microsoft.com/kb/287004 which is a fairly old link but may
point a way forward.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Don Soucy wrote:
Graham

This is the html link as it appears in third column of EXCEL
Download Bio-Energetic Relaxation.mp3

except in EXCEL it is a HYPERLINK (Blue underlined and when moused
over shows
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

Does this make it clearer?

Don

"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




  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Don Soucy[_2_] Don Soucy[_2_] is offline
external usenet poster
 
Posts: 4
Default using html links as data in mail merge w/ office 2003

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





  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default using html links as data in mail merge w/ office 2003

If you are still bashing away with the manual solution, have a look at the
post I made in another branch of this conversation - I certainly would not
regard it as either "simple" or "elegant", but it may still save you time if
you are familiar enough with VBA and the VBA editor that you don't have to
struggle too much with VBA stuff.

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

"Don Soucy" wrote in message
...
Graham

I tried that

I put "www.upperroomcom.com/cgi-bin/dwn.pl" etc in the Link field and it
still just merges as text. There seems to be no simple way to merge html.

my (very unelegant) solution is paste the HYPERLINK into the main
document,
merge the Name & EMAIL, generate the merge mailing, then manually edit
the
HYPERLINK code in each letter (ALT F9) before sending them.
Laborious when there are several hundred to do. :-) but what can you do.


Thanks

Don


"Graham Mayor" wrote:

As you have found, Hyperlinks when merged merely produce the display text
from the data source. I cannot think of any easy way of merging the
hyperlink. You could simply insert the URL without the hyperlink text
into
your data source and that should work but see
http://support.microsoft.com/kb/287004 which is a fairly old link but may
point a way forward.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Don Soucy wrote:
Graham

This is the html link as it appears in third column of EXCEL
Download Bio-Energetic Relaxation.mp3

except in EXCEL it is a HYPERLINK (Blue underlined and when moused
over shows
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

Does this make it clearer?

Don

"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





  #10   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default using html links as data in mail merge w/ office 2003

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






Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail merge labels with Word 2003 and Excel 2003 Data lost data CT Mailmerge 2 January 24th 07 03:24 PM
Mail Merge can't find data source in Office XP...but it could in 2000! [email protected] Mailmerge 4 June 7th 06 03:15 PM
Mail merge Office 2003 - how do you print your data source list? Lynda Anne M. Mailmerge 1 May 22nd 06 01:31 PM
Using HTML file as data source for Mail Merge thenin Mailmerge 4 November 21st 05 09:03 AM
Bookmarks in HTML links in Word 2003 AndyBear Page Layout 3 June 3rd 05 01:55 AM


All times are GMT +1. The time now is 11:18 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"