How can I click on email address and make it open mail program? 1

[Guest_imported]

I thought it would be a neat little feature if you could click on an email address (or a command button) that has been entered and make your favorite mail program open up with the address in the address line.

The only way I can think to do this is to put a command button next to the email field that starts a macro that opens up the mail program. How will I get the address into the address line?

Any ideas?

 

[TheInsider]

Use a hyperlink type field, when you enter the email address, prefix it with "mailto:" when you click the e-mail address it will open your default mail program.

Rob Marriott
rob@career-connections.net

 

[organizer]

I have a question on this method.
Since I also use the data in my E-Mail field to send bulk mailings to members, as well as when I print our directory, adding "mailto:" at the beginning of the addresses creates both technical and aesthetic problems.

I thought I would solve this problem by leaving the E-Mail field as text, and creating a new field, which I called "MailTo" and formatted as a hyperlink. Then - so as not to have to update two fields everytime a member changed their e-mail address, I coded an event for the AfterUpdate of the EMail field on my form to autmatically update the "MailTo" field:

Private Sub HomeE_mail_AfterUpdate()

    Me!MailTo = Trim("mailto:" & [HomeE-Mail])
    
End Sub

The problem is that if I TYPE "mailto:" and the address into the hyperlink field, it works just fine. But it does NOT work when the program generates it this way. The link in the field looks exactly the same either way, but nothing happens when I click the program-generated version - even if I close down the program and reopen it (in case there was something that needed to be reset that I was unaware of) How can I get around this?

I do have successful code for launching Outlook with a double-click on the test Email field in a different database, but this database needs to launch Outlook Express and so far I can't find any way to do that other than the hyperlink formatting to launch the default e-mail.

Thanks,
-cynthia

 

[TheInsider]

You could simply use a hyperlink field and do away with the redundant text field. When you need to mass mail you could simply use the following in your query:

Mid$([email],9,Len([email])-9)

to remove the "mailto:" and "#" that surround the e-mail addresses.

 

[organizer]

Thank you. I finally had time to try it today. It worked just fine.
The person who uses this database is going to love this feature.
-cynthia

 

[organizer]

Out of curiousity (and because it it awkward to edit the contents of a hyperlink field):
Does anyone know why the hyperlink generated by my code would not work?

(And in case Microsoft reads this, sure would be nice if the hyperlink field in Access could just recognize the difference between"

http://me.isp.com"

and "me@isp.com", like every other application including MSWord, Outlook, and Outlook Express can.)

 

[huggybear]

I have used the following code to send an email from Access. It gets called from a command button click.

DoCmd.SendObject , , , Me!

Where Email is a field in the form's record source. I used a text field to hold the email address. I've never used a hyperlink field so I don't think that is even necessary. Check the help for SendObject to see the other options you have with it.

The caveat is that it only seems to work with Outlook. Other than that it works like a charm. There's been tons of questions here about emailing through Lotus and I've yet to see a clear explanation of how to do that from within Access. Can anyone offer some help for that?

Bear

 

[organizer]

Apologies about my post just previous to huggybear. I'm accostomed to a different board format and in retrospect I realize in this format the reference to my code is not so good without including the code again in the new message.

Similar to huggybear, my issue is that the database users do not use Outlook - mine use Outlook Express. And sometimes we mail bulk to members, rather than individual notes. Here was my attempt to use the hyperlink field and still be able to bulk mail. (I've now solved the problem using the solution CCTC1 posted above - but I'm still curious if anyone can tell me why my solution below did not work). Here's my attempt:

I thought I would solve this problem by leaving the E-Mail field as text, and creating a new field, which I called "MailTo" and formatted as a hyperlink. Then - so as not to have to update two fields everytime amember changed their e-mail address, I coded an event for the AfterUpdate of the EMail field on my form to autmatically update the "MailTo" field:

Private Sub HomeE_mail_AfterUpdate()
     Me!MailTo = Trim("mailto:" & [HomeE-Mail])
End Sub

The problem is that if I TYPE "mailto:" and the address into the hyperlink field, it works just fine. But it does NOT work when the program generates it this way. The link in the field looks exactly the same either way, but nothing happens when I click the program-generated version - even if I close down the program and reopen it (in case there was something that needed to be reset that I was unaware of). Any clues why this does not work?

-cynthia

 

[huggybear]

Organizer,

Sorry to have retrod the ground you already covered!

I have been trying to get this email thing working for a while and what you are doing is very interesting to me. I hadn't used hyperlinks before, but it looks as though that may be the way to go.

Would a mailto:whatever hyperlink launch any default email application, even Lotus Notes' ccMail?

Regarding your question about programmatically setting the hyperlink, might you need to be referring to the Hyperlink property of your control, i.e. Me!MailTo.Hyperlink = Trim("mailto:" & [HomeE-Mail])? Just a thought I had.

Bear

 

[organizer]

Hullo,
No problem re-covering the ground! Others have done it for me in the past.

I don't have tons of experience with the hyperlink format in this program myself, but my understanding is that it launches your default e-mail program, so I would guess it might work for Lotus. You can easily try it by just making a new blank database, create one table, make ONE field "Email", make one entry, and click on it to see what it launches - just remember to put "mailto:" in front of the e-mail address or it will try to find a website instead of launching your e-mail program. When you're done you just delete your trial database. This is one of my methods for experimenting without creating a trail of detritus in my actual database!

I was reading another thread on this topic earlier this week in a different forum, and one person said that it DID work to go in and use "copy and paste" to put the "mailto:" in front of addresses that already existed in his database - it wasn't necessary to actually type each letter. Haven't tried that myself but will be trying it this week as I make this change.

However, if you plan use the data in your email field for anything else (i.e. bulk mailing or a directory), be sure to reread the other posts on this thread so you set the rest of it up right.

I tried your suggestion. The code I tried was

Private Sub HomeE_mail_AfterUpdate()
    Me!MailTo.Hyperlink = Trim("mailto:" & [HomeE-mail])    
End Sub

When I tried to update the data in a HomeE-mail field I got an error message
"Runtime error #438: Object doesn't support this property or method". The HomeE-mail field updated, but the MailTo field still just said "#Name?" like they do when they don't know what to do.

If I could get code to do that for me, it would be great, because the text field is a lot easier to edit and our members seem to change their e-mail addresses all the time! In the meantime, I'm going with the hyperlink formatting for my HomeE-mail field and the suggestion from CCTC1 (above) in the queries I use for the directories and bulk mail.

(by the way - if you don't already know better, don't put a hyphen in your field name like I did. It creates nuisance later, but I did it before I knew better and now it is scattered througout my database so I'm living with it)

Good luck. Let me know if I can be of more help.
-cynthia

 

[organizer]

I've tried a few more things toward the idea of getting the hyperlink field to be updated using code (and still work). Maybe this will click an idea for someone else here:

I tried setting the "is hyperlink" property of the [MailTo] field to "yes" (that is the field that is already formatted as a hyperlink in the table - the property was nevertheless set as "no" in the property box on the form even when the hyperlink was working). That change didn't make any difference.

However, I then noticed that once the code has generated the update from the [HomeE-mail] field to the [MailTo] field, making ANY change manually to the data in the [MailTo] field causes the hyperlink to work.

For example, if I update [HomeE-mail] to read new@isp.com (it will be text in that field), the code generates mailto:new@isp.com in the [MailTo] field. If I then delete and replace the "m" at the beginning of that field, the hyperlink will now work.

So, I'm wondering if I could somehow add the keystrokes to the code and would that do it. For example. The code in the [HomeE-mail] After_Update would update [MailTo] and set focus to [MailTo] and then (here's the part I don't know how to do) would execute the keystrokes
Delete (to delete the entry)
Cntrl+z (for undo)
Perhaps this would set the hyperlink to work.
It does it if I do it manually.

I know that SOMEWHERE in the vast Access help files there is a list of ASCII codes for the keystrokes, but I have neglected to note its location any time I have stumbled across it and have never had luck finding it when I've tried deliberately. And I think that in the one Access programming class I took (two years ago!) we learned how to program Access to execute keystrokes.

That's my latest idea. Anyone know how to do it? (or have another idea)?
-cynthia

 

[huggybear]

I don't know about your latest posting, I just now saw it and haven't had time to scope it out. But, I did find out that Hyperlink has its own properties and one of them is Address (check out the Object Browser).

I made a little test database as you suggested and played around with it a bit. In the one field table I had an email field with a hyperlink type into which I inserted an email address blahblah@yadayada.com. On a form, I bound a textbox to that field, put a blank label under it and then a command button.

The command button's click event has this code:

lblTest.Hyperlink = "mailto:" & email
lblTest.Caption = lblTest.Hyperlink.Address

Voila, the label now holds a link to my email.

Now, a question. To do a bulk email, do you loop through a recordset making a bunch of email addresses for the To: line of the message? Or what?

Bear

 

[organizer]

I wish my method were so elegant as to loop the addresses! I am only minimally literate with code so we do our bulk mailing with a query. All the code I've found works only for Outlook, and I get an error message when I try to activate the Outlook Express libraries in the references.

Here's what we use. It's not too intuitive if you've got multiple users for your database, but since I can actually talk to all mine it works just fine.

I do my bulk mailing with a query. We have a button to launch the query. the query selects all the members for whom [HomeE-mail] is not null (or any subset of that that the user wants). Make sure any criteria columns you have in your query have the "show" property unchecked.

There is only one column on the query which will show when the query runs, and it is called:

Export: [FirstName] & &quot; &quot; & [LastName] & &quot; <&quot; & [HomeEMail] & &quot;>; &quot;

"Export" could be any word you wanted to put there - it will be your column header when you run the query.
The only part of this you really need is

Export: [HomeE-mail] & "; "

where ";" is any delimiter accepted by your email program to separate addresses.

Here's how it works
1) Run the query
2) Select all
3) Copy
4) Open a blank message in your e-mail program
5) Paste (for bulk mail I always use the "blind copy" field rather than the "to&quot
6) Scroll to the top of the field you just pasted into and delete the word "Export" from the beginning of your list
7) Write (or paste) your message
8) Send (some isp's require someone on the "to" line - I use myself)

I have a little button the users can use if they need to remember the steps (I've written them in slightly more detail there, since copying from one program and pasting into another seems to confuse a lot of people).

Using the more complicated version of the query above, both Outlook and Outlook Express will recognize that the e-mail address (between the &quot;< >&quot goes with the concatenated name and will just show you a list of names to work with instead of all those addresses. It's a nice feature if I'm working with a list that I DO want to show on the &quot;to&quot; line and people want to know who else got the message. I don't know what Lotus would do with that code. It would not work with AOL, but the simpler version would.

This method will NOT work with a hyperlink formatted field, because when you paste it, it adds ## around the address and it keeps the mailto: as part of the address (which the e-mail program doesn't know what to do with). BUT - I am solving that problem using the line of code provided by CCTC1 in an earlier posting on this thread, which strips all the extra stuff off and leaves just the address. AND continuing to pursue the ideas for the duplicate field.

It's a little cludgey, but it gets the job done!
-cynthia

 

[huggybear]

Oh, ok, I see what you're doing now. That's why you need to use a hyperlink data type?

I came up with this code to loop through a recordset and generate a semi-colon delimited string of email addresses that go into the To: box. By the way, all these email addresses are stored as text in the table so there's no First and Last name shown. I think that it might be possible to do that with one of the Hyperlink properties but I wouldn't swear to it.

This is in a command button click event:

Dim db As Database
Dim rs As Recordset
Dim strAddress As String
Dim strComma As String
Set db = CurrentDb
Set rs = Me.RecordsetClone
strAddress = &quot;mailto:&quot;
strDelimiter = &quot;;&quot;

If rs.RecordCount <> 0 Then
rs.MoveFirst
strAddress = strAddress & rs!Email
cmdSendEmail.Hyperlink.Address = strAddress
rs.MoveNext

While Not rs.EOF
strAddress = strAddress & strDelimiter & rs!Email
cmdSendEmail.Hyperlink.Address = strAddress
rs.MoveNext
Wend

Else
MsgBox &quot;No Email address information available.&quot;
End If

I just did this and now my email isn't working on this computer so I can't really test it. Sorry about that! I am thinking though that the mailto: isn't necessary because all you really need in the To: line is an email address or two or whatever.

Anyway, I think I am zeroing in on the solution.

Thanks for your help.

Bear

 

[organizer]

I'm off this project for a few days - I'll try out your code next week Thanks!

The procedure I described in the post immediately above works with a text field (actually, three text fields if you want to include the names). The reason I got intrigued with this hyperlink field was so we could launch an e-mail to a SINGLE member right from the database, but without compromising our bulk mailing ability. That way - since the database is networked - we don't all have to update our email program address books every time a member changes their email address (which they seem to do with some frequency)!

Good luck getting it to run with Lotus. I hope one of these things works.
This is fun - kind of like doing puzzles.
-cynthia

 

[huggybear]

I agree about the fun part, I enjoy puzzles.

Have a good break.

Bear