strMail = "#MailTo:" & Me.E_mail

[Guest]

I have a jobData table that has fields [Church] (which is stored as its ID),
[Job], [Person]. I also show the Church's email address by via the control
source of [Church] but a row source of vChurchlookup:

SELECT vChurch.ID, vChurch.ServiceName, Parish.Parish, Benefice.Benefice,
Deanery.Deanery, Archdeaconry.Archdeaconry, vChurch.Telephone, vChurch.Email
FROM ((Archdeaconry INNER JOIN Deanery ON Archdeaconry.Archdeaconry =
Deanery.Archdeaconry) INNER JOIN (Benefice INNER JOIN Parish ON
Benefice.Benefice = Parish.Benefice) ON Deanery.Deanery = Benefice.Deanery)
INNER JOIN vChurch ON (Parish.Parish = vChurch.Parish) AND (Parish.Parish =
vChurch.Parish) ORDER BY vChurch.ServiceName;


(which means it shows the email address rather than church ID.

but when I attach the following code to this box:

Dim strMail As String
strMail = "#MailTo:" & Me.E_mail & "#"
Application.FollowHyperlink HyperlinkPart(strMail, acAddress)

it opens a new email with "29" in the to box!

I have tried replacing Me.E_mail with vChurch.Email but it still refuses to
work

any pointers please

Regards

Joe

 

[Tony Vrolyk]

I also show the Church's email address by via the control
source of [Church] but a row source of vChurchlookup:

I am not exactly sure what you mean by this but I take to mean you have a
combo box or list box that has a row source of the SQL statement you gave in
the original post. I think the problem is the bound column on that combo or
list box. Let's say yours is a list box

List boxes can display multiple columns of data but only one of those
columns is the bound column. Meaning: when you reference that list box the
data in the bound column is what you get back.

So according to your example the columns you have are ID, ServiceName,
Parish, Benefice, Deanery, Archdeaconry, Telephone and Email. If the bound
column is column 1 (which is the default) then the data you will get back
when referencing the list box is the ID field. That seems to be the case
since your email To field is being set to a number. (I am assuming that the
IDs are numbers, probably autonumbers).

So you can do one of three things.
1. Move the Email data to the first column
2. Change the bound column to the one where the email is stored (In your
example column 8)
3. Reference the exact column in code when starting your email

To accomplish number 3 you would need to do something like

strMail = "#MailTo:" & Me.E_mail.Column(7) & "#"
Application.FollowHyperlink HyperlinkPart(strMail, acAddress)

PLEASE NOTE that in code the columns start at number 0 instead of 1 so the
eighth column is actually column 7.

I hope that helps
Tony