MailMerge hyperlinks!?

[Ian McKeag]

I am mail merging in word, URL's from a hyperlink field in an Access
database. However when they are merged, it is duplicated and includes hash’s
##

e.g. www.url.com#www.url.com# or (e-mail address removed)#[email protected]#

From what I understand, the hyperlink field in access is made up of 'text to
display#URL#subaddress'. So..

Is there any way to only show the 'text to display' or 'URL' part of the
hyperlink, without the #'s and without repeating?

Any help would be great!
Many thanks,
Ian McKeag

 

[Peter Jamieson]

From a couple of older posts of mine...

-----------------------------------

There are at least two different problems here:
a. Word doesn't recognise the datatype "Hyperlink" (i.e. does not
treat the
contents as a hyperlink). It just treats the content of the field as text.
b. if you are trying to use a Hyperlink field to generate a proper
hyperlink in Word, the usual problem is that when you merge, the /display
text/ of the field remains the same even when the data is different in every
record. This is simply because there is no way to specify the display text
in the field code - you can only do it programmatically, e.g. via VBA.

As far as extracting the hyperlink from Access is concerned, you can do it
in an Access query and use that query as the data source for your merge. As
far as I can tell, hyperlinks are basically stored as

displaytext#linktext

e.g. if the hyperlink column is called h, your query SQL could be something
like

SELECT Mid(h, instr(1, h, '#') + 1, Len(h) - instr(1,h,'#') - 1) as
hyperurl, * FROM mytable

then you can insert the link using a nested field, e.g.

{ HYPERLINK "{ MERGEFIELD hyperurl }" }

or it may be better to insert the linktext directly using

{ MERGEFIELD hyperurl }

and find some way to get Word to convert the text to proper hyperlinks
(probably by mucking around with the Autoformat options).

-----------------------------------

-----------------------------------

I think you have to use Word Mail Merge events for this. I have not tested
this recently, but for example:

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 WEBADDRESS for the link. NB, these field names
' are case-sensitive.
lt = Doc.MailMerge.DataSource.DataFields("WEBADDRESS")
' set up the display text that you want. I assuming you have a field
' called WEBTEXT
dt = Doc.MailMerge.DataSource.DataFields("WEBTEXT")

' 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. You should realise
that
once you have enabled the events, they apply to any document until either
you or
Word has disabled them again. This is why I do not particularly like using
Events, but even so,
I suspect that this is the easiest way to perform this particular task in
Word.

--------------------------

Once you are committed to using VBA to process mailmerge events, you may
find it is easier to split the Access hyperlink in the event handling
VBA, rather than issuing SQL stuff to do it.


Sorry it's a bit scrappy...

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Ian McKeag]

I am using the {HYPERLINK {MERGEFIELD url}} which is nearly working, I am so
close but not really sure if it can be done...!?

I am using the following VB code to update the URLS for each document-
---------------------------------------------------------
Dim Hyperlink

ActiveDocument.Fields.Update

For Each Hyperlink In ActiveDocument.Hyperlinks
Hyperlink.TextToDisplay = Hyperlink.Address

Next Hyperlink
----------------------------------------------------------

which sometimes works but can cause these problems when executed;

* the address is followed by a '#' creating - www.url.com#
* the document UNC is placed in front of the URL creating -
C:\Docs\File.docwww.url.com#
* the mergefield is alted to create {HYPERLINK "www.url.com#"}
* the mergefield is deleted to create {HYPERLINK }

This is very frustrating being so close but so far!

Is it possible to use this method to mail merge URL's into documents?

And if so..
any ideas on how to ensure there are no # charachters or maybe use some code
to delete any # in a URL for the hyperlink fields?

Many thanks!
Ian McKeag

 

[Peter Jamieson]

You should be able to remove a trailing # using either

For Each h In ActiveDocument.Hyperlinks
h.TextToDisplay = split(h,"#")(0)
Next

or

For Each h In ActiveDocument.Hyperlinks
h.TextToDisplay = left(h.Address & "#",instr(1,h.Address & "#","#")-1
Next

However, if you are running this code post-merge, e.g. on an output
document, as I think you probably are, then
a. Word will probably already have mangled the intended address (e.g.
added http://, which you might or might not want, and as you say it's
adding the document URL in some case, and I'm not sure why - perhaps
it's because it does not recognise the URL as a valid HHTP URL and
decides that it must be a FILE URL, perhaps because of the trailing #
b. if you are able to strip the trailing # before it is used in the
HYPERLINK field (e.g. by using SQL to strip it out, roughly as
suggested), then perhaps some of these problems will go away.


Otherwise, there are a couple of possible ways around this, e.g.
a. stick to a post-merge VBA solution but merge the hyperlink text
immediately after each HYPERLINK field, so that your VBA can pick up
that text and use it to re-insert the hyperlink.
b. switch to using merge events, but change the technique I suggested
there as it would of course be simpler to iterate through the hyperlinks
as you have rather than mess around with bookmarks as my code does. But
even there, it would be simpler to indicate the source of each hyperlink
in a different way


e.g. for (a) you could try something like

For each hyperlink that needs to be filled in from your data source, in
the mailmerge main document insert (say)

{ HYPERLINK "http://replaceme/" }{ MERGEFIELD myhlinkfieldname }§

Then do something like the following - sorry, I only have time to
suggest what the code should do right now...

Dim h As Hyperlink
Dim r As Range
For Each h In ActiveDocument.Hyperlinks
If h.Address = "http://replaceme/" Then
' 1. set a range starting at the character
' immediately following the HYPERLINK and
' ending with an unambiguous "terminator'
' (I have suggested § for now)
' 2. get the displaytext and the addresstext from that range
' 3. replace the existing hyperlink using e.g.
h.Range.Hyperlinks.Add h.Range, addresstext,,,displaytext
End if
Next


For (b) you might do something similar, but let's leave that for now...


http://tips.pjmsn.me.uk