How to create an email from mail merge including a hyperlink, so that the value of a merged field is

[nick.barrar]

Hi,

I'm working with MS Access 2003 and MS Word 2003 and what I need to
know is how to create an email from mail merge including a hyperlink,
so that the value of a merged field is included as part of the
hyperlink URL, not just part of its text.

The context for this is that I'm trying to create a survey to be sent
out via email, the survey is transactional and thus I need to include
a unique number in the hyperlink included in each email to make it
work, I'm doing this by merging the unique number from access into the
URL going in the email.

like this:

http://intranet.company.co.uk/customersurveys/survey_q.asp?ticketid=999999

The "99999" has to be the number from access. I can get it into the
link text - but I can't get it to go in the link URL itself.

If this won't work, is there another way, maybe from the Access end?
Can I generate emails directly from Access?

Any help would be greatly appreciated !

Many Thanks,
Nick

 

[Peter Jamieson]

To specify the display text, things are complicated because there is no
"switch"
in the HYPERLINK field that lets you specify the display text.

You may be able to do the merge using VBA and "Word Events", e.g. as follows
(I haven't tested this recently). Nasty, but even so I suspect this is the
easiest way to do it in Word.

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 mylink for the link
lt = Doc.MailMerge.DataSource.DataFields("mylink")
' set up the display text that you want. If it should be the same as the
link text, do that:
dt = lt

' 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.

Peter Jamieson

 

[nickbarrar]

Peter - I'll try this out and confirm this week, thanks very much for
this, it's greatly appreciated !!!

Nick

 

[nickbarrar]

Peter - I'll try this out and confirm this week, thanks very much for
this, it's greatly appreciated !!!

Nick

Peter thanks - almost worked but we get a syntax error at;

' insert the hyperlink you want
Set h = Doc.Hyperlinks.Add(Anchor:= r, Address = lt, TextToDisplay:=
dt)

any ideas?

Nick

 

[Peter Jamieson]

Yes, there should be a colon after address, i.e.

Set h = Doc.Hyperlinks.Add(Anchor:= r, Address:= lt, TextToDisplay:=dt)