how can i activate email field in Access

---

Request for Question Clarification by aceresearcher-ga on 23 Oct 2002 10:22 PDT

kkeegler,

What aspect of e-mail are you trying to activate?

- Send a database object's output as an attachment to an e-mail
message
- EmailSubject Property
- Send a datasheet, form, report, module, or data access page in
e-mail by using the SendObject action
- Create a command button that creates an e-mail message when clicked
- Create a hyperlink that creates an e-mail message when clicked
- Import or link data from Microsoft Outlook or Microsoft Exchange
- Send a data access page as the body of an e-mail message
- Set options for sending an e-mail message

Or something else?

Help for any of the above actions (plus several others is available by
pulling down the "Help" menu in Access, choosing "Microsoft Access
Help", clicking on the "Index" tab, then typing "e-mail" in the
"1.Type keywords" box. Double-click on the word "e-mail" in the "2.Or
choose keywords" box. This will bring up a list of topics in the
"3.Choose A Topic" box. Double-click on the one that you want to see.

If this does not provide the information you need, please post a
Request for Clarification here describing in more detail what aspect
of e-mail you wish to activate, so that Researchers can assist you
further.

I hope you find this information helpful!

aceresearcher

---

Clarification of Question by kkeegler-ga on 23 Oct 2002 11:15 PDT

Sorry, I should have been more specific.

I have a field where I track my customer email address and would like
to be able to simply click the email address in the Form to send them
a note rather than do the "copy - paste" to Outlook. I use Office 2K.

I tried "Help", you know how that goes.

   Ken

---

Request for Question Clarification by aceresearcher-ga on 23 Oct 2002 13:17 PDT

Okay Ken, then the help you want is probably:
"Create a hyperlink that creates an e-mail message when clicked".

This is Access' help info for that process (with a few clarifications
added by me):

If you want to insert the e-mail in a form, report, or data access
page, open it in "Design" view.

On the "Insert" menu, click "Hyperlink" (not "Hyperlink column").

In the "Insert Hyperlink" dialog box, click "E-mail address" under
"Link to".

In the "Text to display" box, type the text that you want displayed in
the field or text box (such as "Sandy Smith"). If you leave the box
blank, then Microsoft Access will use the e-mail address as the
display text.

Click ScreenTip, and type the text that you want to appear when the
mouse pointer hovers over the hyperlink (such as "Send e-mail to
Sandy"). If you leave the ScreenTip text box blank, then Microsoft
Access will display the e-mail address as the ScreenTip.

In the E-mail address box, type an e-mail address (such as
"sandyjsmith@hotmail.com". Or select an address from the Recently used
e-mail addresses list.

In the Subject box, type the information that you want to appear in
the Subject field of the e-mail message.
Note: Some Web browsers and e-mail programs might not recognize the
subject line. If you don't want to specify a subject, just leave it
blank.

Click "OK". 

On a data access page, Microsoft Access adds the hyperlink in a
hyperlink control. In a form or report, it adds the hyperlink in a
label. To test the link on a page, switch to Page view and click the
hyperlink. To test the link in a form or report, right-click the
label, point to Hyperlink on the shortcut menu, and then click Open.
Microsoft Access opens your mail program and creates a new message,
with the address and subject filled in.

Ken, if this doesn't work, or you have questions about how you do part
of it, please post a Request for Clarification. Give as much
information about what happened at what step, or what part you don't
understand or know how to do, as you can, and we will go from there.

---

Clarification of Question by kkeegler-ga on 23 Oct 2002 17:42 PDT

Thanks for your patience!!

I did as you suggested and I understand what we did.

However, I guess I am missing something in my clarification.
I already have 150 or so email addresses in a field called
"emailaddress" and those are the addresses that I want to "click" and
send an email. Right now they are simply text.

In your example which was quite easy to follow I typed in my hotmail
address and a typical subject and that would send all emails to me.
The part that may be confusing me is the "Recently used e-mail list"

When I set up a new customer email or modify an existing customer I
would like that field to be "click and send".

I hope this helps.

   Ken

---

Request for Question Clarification by aceresearcher-ga on 27 Oct 2002 04:12 PST

kkeegler,

If I understand you correctly, what you are asking is how to
automatically change your already-entered text strings of e-mail
addresses into e-mail hyperlinks. Is this the case?

aceresearcher

---

Clarification of Question by kkeegler-ga on 27 Oct 2002 08:47 PST

That is correct, as well as new email addresses I will add in the future.

   Ken

---

Request for Question Clarification by aceresearcher-ga on 28 Oct 2002 04:00 PST

ken,

Okay, I tried this and it worked for me, but it's pretty convoluted,
and for that I apologize.

First make sure that you have your Access data file saved under a
couple of other different names, just in case, so you can at least be
no worse off than you are right now if I totally make you screw things
up.

If you have Microsoft Word, open that up into a new, empty document.

Also open up Notepad into a new, empty document (usually under Start
--> Programs --> Accessories).

Now open up your Access table with the column of non-hyperlinked
e-mail addresses. Put your curson on that column. Then pull down
"Insert" and select "Hyperlink Column". This will insert a new column
in front of your e-mail address column.

Now drag your mouse down the column of 150 e-mail addresses to
highlight them in one big chunk. Then Right-click, select "Copy".

Switch over to Notepad, Right-click, and select "Paste". This should
create a list of all your e-mail addresses without any special
formatting in it. Then Right-click and select "Select All". Then
Right-click again and select "Copy".

Now switch over to Microsoft Word. Right-click and select "Paste".
This should copy in your list of plain-text, non-linked e-mail
addresses. Pull down "Edit" and select "Select All". Then pull down
"Format", select "Autoformat" and click "OK" to "Autoformat Now". This
should turn your list of e-mail addresses into a list of hyperlinks.
Then pull down "Edit" again, "Select All", Right-click, and "Copy".

Go back over to Access. Put your cursor in the first cell in the new,
blank Hyperlink Column you just created earlier. Right-click and
select "Paste".

Now you should have a column of all your hyperlinked e-mail addresses
next to the old non-linked column.

If this works, then you will need to change any existing report
formats to show the new hyperlinked e-mail field instead of the old
non-linked field. Once you have changed this everywhere, you can
probably delete the old non-linked column if you wish.

Just a disclaimer, ken:
Everything I know about PCs and PC software, has been completely
self-taught over the last 15 years. I am by no means an expert. There
is probably an easier way to do this, and anyone who knows what it is,
is welcome to chime in here. In addition, I am unsure whether this
procedure will throw some of the functionality you have set up in your
Access file out of whack (hopefully not!).

Please let me know if this works for you, or if you have additional
questions or problems.

Regards,

aceresearcher

---

Request for Question Clarification by aceresearcher-ga on 31 Oct 2002 06:22 PST

Ken,

I just wanted to check and see if you had been successful at getting
your column of e-mail addresses formatted as hyperlinks.

Just let me know -- if need be, we can work on this some more!

aceresearcher

---

Clarification of Question by kkeegler-ga on 31 Oct 2002 10:38 PST

I actually was working on a solution that ended up to be fairly
"slick".
Slick in the sense it resolves 2 issues at the same time, 1. making
"HOT" the address and 2. converting the hot field back to "Text" for
editing. Pretty cool!!
I also included URL's in the code.
I would be happy to send you the example in a sample DB so you can add
it to your files and / or use it as appropriate.
While I didn't accept your last proposed solution as THE answer I was
looking for, out of fairness to you for your time I will as it did
sound like a workable solution.

If you would like the sample file send me an email to
kkeegler@hotmail.com.

   Thanks
      Ken

---

Clarification of Question by kkeegler-ga on 31 Oct 2002 10:41 PST

By-the -way.

How do I accept your answer to close the question?

       Ken

Ken, 
 
Thank you for accepting my response as an Answer. I would be delighted
to know the solution you devised; however, the Researchers' Terms of
Service do not permit us to have personal contact (such as by phone or
e-mail) with customers.

If you have the time and the inclination to post your solution here as
a "Clarify Question" or "Post Comment", I am sure I will not be the
only one to find it interesting and educational. (Note that if you
post your solution as an "Answer Clarification Request", the system
will require me to respond.)

Once I have posted this Answer, to close out this question, you just
need to provide a Rating for the quality of service which you feel
that I provided to you.

I am so glad you have been able to resolve your issue! I hope that you
feel that my input assisted you to do so.

aceresearcher

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

Okay, I tried this and it worked for me, but it's pretty convoluted,
and for that I apologize.
 
First make sure that you have your Access data file saved under a
couple of other different names, just in case, so you can at least be
no worse off than you are right now if I totally make you screw things
up.
 
If you have Microsoft Word, open that up into a new, empty document. 
 
Also open up Notepad into a new, empty document (usually under Start
--> Programs --> Accessories).
 
Now open up your Access table with the column of non-hyperlinked
e-mail addresses. Put your curson on that column. Then pull down
"Insert" and select "Hyperlink Column". This will insert a new column
in front of your e-mail address column.
 
Now drag your mouse down the column of 150 e-mail addresses to
highlight them in one big chunk. Then Right-click, select "Copy".
 
Switch over to Notepad, Right-click, and select "Paste". This should
create a list of all your e-mail addresses without any special
formatting in it. Then Right-click and select "Select All". Then
Right-click again and select "Copy".
 
Now switch over to Microsoft Word. Right-click and select "Paste".
This should copy in your list of plain-text, non-linked e-mail
addresses. Pull down "Edit" and select "Select All". Then pull down
"Format", select "Autoformat" and click "OK" to "Autoformat Now". This
should turn your list of e-mail addresses into a list of hyperlinks.
Then pull down "Edit" again, "Select All", Right-click, and "Copy".
 
Go back over to Access. Put your cursor in the first cell in the new,
blank Hyperlink Column you just created earlier. Right-click and
select "Paste".
 
Now you should have a column of all your hyperlinked e-mail addresses
next to the old non-linked column.
 
If this works, then you will need to change any existing report
formats to show the new hyperlinked e-mail field instead of the old
non-linked field. Once you have changed this everywhere, you can
probably delete the old non-linked column if you wish.
 
Just a disclaimer, Ken: 
Everything I know about PCs and PC software, has been completely
self-taught over the last 15 years. I am by no means an expert. There
is probably an easier way to do this, and anyone who knows what it is,
is welcome to chime in here. In addition, I am unsure whether this
procedure will throw some of the functionality you have set up in your
Access file out of whack (hopefully not!).
 
Please let me know if this works for you, or if you have additional
questions or problems.
 
Regards, 
 
aceresearcher

Answer was a work-around solution.
The solution settled on requires a look at the code and is a little
long but the basic code follows. It also involves setting up 4 new
fields in the table and a Command Button on the form.

Good Luck and Thanks again.

        Private Sub cmdMakeHot_Click()
'this will make the ControlSource of the Email & Web Address textboxes
for "fldEmailText"
'and "fldWebText" change to "fldMailTo" and "fldURL"
Dim db As Database                           '
Dim rsCustomers As Recordset
Dim strSQL As String
Dim strConstruct As String



      'Make the textboxes HYPERLINK
If Me!cmdMakeHot.Caption = "Make  Text" Then        ' Does the button
say "Make Text"?
   
   Me!cmdMakeHot.Caption = "Make  Hot"            ' make the caption
say "Make Hot"
   Me!cmdMakeHot.ForeColor = 16744448             ' make the text blue
   Me!txtEmailAddress.ForeColor = 0             ' make the text black
   Me!txtWebAddress.ForeColor = 0             ' make the text black
   Me!txtEmailAddress.ControlSource = "fldEmailAddress"  ' Yes, the
make the control source the Text field
   Me!txtWebAddress.ControlSource = "fldWebAddress"       ' make the
control source the Text field
   Me!cmdMakeHot.ControlTipText = "Change Text field to Hyperlink to
initiate Email or Web Browser" ' Change the Tool Tip
Else  'Make the textboxes HOT                    ' No, the button says
"Make Hot", so

   Me!cmdMakeHot.Caption = "Make  Text"             ' make the caption
say "Make Text"
   Me!cmdMakeHot.ForeColor = 0                    ' make the text
black
   Me!txtEmailAddress.ForeColor = 16744448             ' make the text
blue
   Me!txtWebAddress.ForeColor = 16744448             ' make the text
blue
   Me!cmdMakeHot.ControlTipText = "Change Hyperlink field to Text for
editing" ' Change the Tool Tip
      'now put the addresses into the HOT fields
      Set db = CurrentDb                           ' set the database
object
      strSQL = "SELECT * FROM tblClientInformation WHERE
[tblClientInformation]![ClientCompanyName] = '" & Me!ClientCompanyName
& "';"
      Set rsCustomers = db.OpenRecordset(strSQL)   ' set the recordset
object
      
      'Do the Email first
      
      ' if the string begins with "#MailTo:" Then
      If Left(Me!txtEmailAddress, 8) = "#MailTo:" Then
         If Right(Me!txtEmailAddress, 1) = "#" Then
            strConstruct = Me!txtEmailAddress
         Else
            strConstruct = Me!txtEmailAddress & "#"
         End If
      
      ' if the string begins with "MailTo:" Then
      ElseIf Left(Me!txtEmailAddress, 7) = "MailTo:" Then ' if the
string begins with "MailTo:" Then
         If Right(Me!txtEmailAddress, 1) = "#" Then
            strConstruct = "#" & Me!txtEmailAddress
         Else
            strConstruct = "#" & Me!txtEmailAddress & "#"
         End If
      ' if the string does not begin with "#MailTo:" or "MailTo:" Then
      Else
         If Right(Me!txtEmailAddress, 1) = "#" Then
            strConstruct = "#MailTo:" & Me!txtEmailAddress
         Else
            strConstruct = "#MailTo:" & Me!txtEmailAddress & "#"
         End If
      End If
      
      rsCustomers.Edit
      rsCustomers!fldMailTo = Nz(strConstruct, "")
      rsCustomers.Update
      'CurrentDb.Execute ("UPDATE [Customer Locations] SET [Customer
Locations].[fldEmailAddress] = '" & Nz(Me!txtEmailAddress, "") & "'
WHERE [Customer Locations].[CustKey] = '" & ClientCompanyName & "';")
      'CurrentDb.Execute ("UPDATE [Customer Locations] SET [Customer
Locations].[fldMailTo] = '" & Nz(strConstruct, "") & "' WHERE
[Customer Locations].[CustKey] = '" & ClientCompanyName & "';")
      'Do the Web second      'rsCustomers!fldWebAddress
Me!txtWebAddress

      ' if the string begins with "#Http://" Then
      If Left(Me!txtWebAddress, 8) = "#Http://" Then
         If Right(Me!txtWebAddress, 1) = "#" Then
            strConstruct = Me!txtWebAddress
         Else
            strConstruct = Me!txtWebAddress & "#"
         End If
      
      ' if the string begins with "Http://" Then
      ElseIf Left(Me!txtWebAddress, 7) = "Http://" Then ' if the
string begins with "MailTo:" Then
         If Right(Me!txtWebAddress, 1) = "#" Then
            strConstruct = "#" & Me!txtWebAddress
         Else
            strConstruct = "#" & Me!txtWebAddress & "#"
         End If
      ' if the string does not begin with "#Http://" or "Http://" Then
      Else
         If Right(Me!txtWebAddress, 1) = "#" Then
            strConstruct = "#Http://" & Me!txtWebAddress
         Else
            strConstruct = "#Http://" & Me!txtWebAddress & "#"
         End If
      End If
      rsCustomers.Edit
      rsCustomers!fldURL = Nz(strConstruct, "")
      rsCustomers.Update
      
      rsCustomers.Close
      Set rsCustomers = Nothing
      Set db = Nothing
      'change the controlsource to look at the TEXT fields
      Stop
      Me!txtEmailAddress.ControlSource = "fldMailTo" ' make the
control source HOT
      Me!txtWebAddress.ControlSource = "fldURL" ' make the control
source HOT
End If
'Me.Refresh


End Sub

Private Sub txtEmailAddress_Exit(Cancel As Integer)
    If Nz(Me!txtEmailAddress, "") <> "" Then Me!cmdMakeHot.Enabled =
True
    Me.Refresh
End Sub

Private Sub txtWebAddress_Exit(Cancel As Integer)
    If Nz(Me!txtWebAddress, "") <> "" Then Me!cmdMakeHot.Enabled =
True
    Me.Refresh
End Sub