help exporting an access hyperlink field to a mailmerge word doc

[Wisteria]

I am using an Access query, that contains home email addresses formatted as
"hyperlink", as a data source when creating a mail merge directory in Word.
The result for this field in the Word doc is:
(e-mail address removed)#mailto:[email protected]# instead of: (e-mail address removed)
(underlined and clickable). If I export the query to Excel first, and use
the Excel file as the data source, the directory "home email address" field,
formats as desired. Is it possible to get this accomplished directly from
the Access query, and not performing the Excel step? I am using Office 2007.

 

[Peter Jamieson]

You should be able to create an Access query that strips the necessary
part of the field. e.g. if the hyperlink column is called h, and you
need the first part of the your query SQL could be something like

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


The + '#' stuff is only there in case there is not always a # at the end
of the field when the field only contains the first part of the
hyperlink (I do not know what is possible)

Then use that query as the data source for your merge.

If you are not in a position to create queries in that database, you can
try setting up the query directly using the SQLCommand parameter of Word
VBA's OpenDataSource method.

Peter Jamieson

http://tips.pjmsn.me.uk