Microsoft Access 2003

[Kevin]

In a form, I have a field for emails. It is hyperlinked. However, I have to
enter the email address then right click and go to the hyperlink editor which
puts a mailto: in front of the email address. How can I set a default in the
form so that when I put in an email address it automatically inserts the
mailto: in front of the email address. Currently it defaults to treat it as a
URL address.

 

[Tom Wickerath]

Hi Kevin,

Working with the hyperlink data type is a PITA in my opinion. Try changing
the underlying field to Text (255). On your form, lets suppose the name of
the text box is txtEmail. You can add a small command button (there are cute
pictures available with little mail icons) to run some code, as follows. This
example uses a command button with the name "cmdSendEmail":

Private Sub cmdSendEmail_Click()
On Error GoTo ProcError

DoCmd.SendObject To:=Nz(Me.txtEmail, ""), Subject:="Whatever", _
MessageText:="Blah blah blah"


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdSendEmail_Click..."
Resume ExitProc
End Sub


You can also reference other textboxes on the form, or fields in a table, to
pick up custom subject and message text, instead of having these values
hard-coded.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Kevin]

Sorry I have not responded sooner, I have been out of the office. Compared to
you I am a lowly novice. Can you be more specific in your instructions? In a
form, the customer information has a text field or the email address. The
text field name is Email Address. I have been able to turn on and off the
hyperlink. When the Hyperlinked is on, access assumes it is a web page unless
I precede it with mailto: I am not familiar with PITA, Text 255, or command
buttons. I began using access with not experience. I purchased several books,
and I learn as I go. Once I've learned what you are trying to show me I will
incorporate it in the future.

 

[Tom Wickerath]

Hi Kevin,

Compared to you I am a lowly novice.

Not really. If you keep working with Access a lot, read questions posted by
others along with the answers received, read books, etc., you will soon be in
a position to answer questions. Actually, attempting to answer questions
posted by others is a great learning technique, because it forces one to
research the answer. Anyways, just keep doing what you are doing. It will
come!

I am not familiar with PITA, ...

Text 255, ....

This refers to the data type in table design view. I was advising to open
the table in design view, and change the hyperlink data type to Text. Set the
Field Size to the maximum, which is 255 characters. That should be long
enough to cover most e-mail addresses.

or command buttons.

A command button is an object on a form that one clicks to cause some
action. For example, open the Northwind sample database that is likely
already installed on your PC (search for Northwind.mdb, or within Access,
click on Help > Sample Databases... > Northwind Sample Database (not
Northwind Access Sample Project). The opening form includes a command button
labeled "OK" (unless you choose "Don't show this screen again". The
switchboard form includes seven command buttons, each of which has a click
event procedure associated with it.

Can you be more specific in your instructions?

I'll try. First, make a copy of your database, and work on the copy. That
way, if anything goes wrong, you can easily restore from a backup copy. Open
the form in question in design view. Click on View > Toolbox to display your
toolbox if it is not already displayed. Select the command button from the
toolbox. Drag a rectangle on your form with your mouse for the approximate
size that you want your new command button. If a wizard pops up, just close
it (this is controlled by another button in the toolbox).

Click on View > Properties to display the properties dialog (if it is not
already displayed). Select your new command button. You should see "Command
Button:" with the default name of the new command button in the title bar of
the properties dialog. Select the Other tab. Name your new command button
"cmdSendEmail" (without the quotes). Select the textbox that is used to
display the e-mail address. You should see Text box in the title bar of the
properties dialog. Change the name of this text box to "txtEmail" (again, no
quotes).

Now select the command button again, and click on the Event tab of the
properties dialog. Click your mouse into the event that reads "On Click". You
should see a dropdown box. Select [Event Procedure] from the list. Click on
the Build button (the small button with the three dots, to the right of the
dropdown). This should cause a module to open up that is associated with this
form. In the module, you should see the following, with your mouse cursor
blinking in-between within your new click event procedure:

Option Compare Database
Option Explicit

Private Sub cmdSendEmail_Click()

End Sub


Special note: If you do not see "Option Explicit" as the second line of
code, then add it manually. Here is a gem tip that discusses these two very
important words, and how you can configure your PC to always include them in
newly created modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Now, go back to the initial reply that I posted. Select everything
in-between the Private Sub cmdSendEmail_Click() and the End Sub, copy it
(Ctrl C) and paste it (Ctrl V), at the location that your cursor was first at
when the module popped open.

Still in the Visual Basic Editor (VBE), click on Debug > Compile
ProjectName, where ProjectName is the name of your VBA project (likely the
same name as your database). If all goes well, your VBA code will compile
without any complaints. If you have any compile errors, you'll need to fix
these before proceeding. Assuming no compile errors, click on the Save
button, and close the Visual Basic Editor.

Open your form in normal mode and try out your new command button.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Kevin]

First, it worked Thanks!

Just one problem and a couple follow-ups.

1) When I push the command prompt to send the email, Outlook comes up.
However, if I change my mind after Outlook comes up and cancel the action, I
get an Error message Error 2501: The SendObject action was canceled.

2) Next, when I push the command prompt to send the email, Outlook comes up
but not in default view. To: , Cc:, and Bc: are in big buttons and no ruler
at the top. Not a problem but strange.

You had What ever in the subject line and Bla bla bla in the body. I figured
out how to make these blanks. How do you make the variable?

Hi Kevin,

Compared to you I am a lowly novice.

Not really. If you keep working with Access a lot, read questions posted by
others along with the answers received, read books, etc., you will soon be in
a position to answer questions. Actually, attempting to answer questions
posted by others is a great learning technique, because it forces one to
research the answer. Anyways, just keep doing what you are doing. It will
come!

I am not familiar with PITA, ...

Text 255, ....

This refers to the data type in table design view. I was advising to open
the table in design view, and change the hyperlink data type to Text. Set the
Field Size to the maximum, which is 255 characters. That should be long
enough to cover most e-mail addresses.

or command buttons.

A command button is an object on a form that one clicks to cause some
action. For example, open the Northwind sample database that is likely
already installed on your PC (search for Northwind.mdb, or within Access,
click on Help > Sample Databases... > Northwind Sample Database (not
Northwind Access Sample Project). The opening form includes a command button
labeled "OK" (unless you choose "Don't show this screen again". The
switchboard form includes seven command buttons, each of which has a click
event procedure associated with it.

Can you be more specific in your instructions?

I'll try. First, make a copy of your database, and work on the copy. That
way, if anything goes wrong, you can easily restore from a backup copy. Open
the form in question in design view. Click on View > Toolbox to display your
toolbox if it is not already displayed. Select the command button from the
toolbox. Drag a rectangle on your form with your mouse for the approximate
size that you want your new command button. If a wizard pops up, just close
it (this is controlled by another button in the toolbox).

Click on View > Properties to display the properties dialog (if it is not
already displayed). Select your new command button. You should see "Command
Button:" with the default name of the new command button in the title bar of
the properties dialog. Select the Other tab. Name your new command button
"cmdSendEmail" (without the quotes). Select the textbox that is used to
display the e-mail address. You should see Text box in the title bar of the
properties dialog. Change the name of this text box to "txtEmail" (again, no
quotes).

Now select the command button again, and click on the Event tab of the
properties dialog. Click your mouse into the event that reads "On Click". You
should see a dropdown box. Select [Event Procedure] from the list. Click on
the Build button (the small button with the three dots, to the right of the
dropdown). This should cause a module to open up that is associated with this
form. In the module, you should see the following, with your mouse cursor
blinking in-between within your new click event procedure:

Option Compare Database
Option Explicit

Private Sub cmdSendEmail_Click()

End Sub


Special note: If you do not see "Option Explicit" as the second line of
code, then add it manually. Here is a gem tip that discusses these two very
important words, and how you can configure your PC to always include them in
newly created modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Now, go back to the initial reply that I posted. Select everything
in-between the Private Sub cmdSendEmail_Click() and the End Sub, copy it
(Ctrl C) and paste it (Ctrl V), at the location that your cursor was first at
when the module popped open.

Still in the Visual Basic Editor (VBE), click on Debug > Compile
ProjectName, where ProjectName is the name of your VBA project (likely the
same name as your database). If all goes well, your VBA code will compile
without any complaints. If you have any compile errors, you'll need to fix
these before proceeding. Assuming no compile errors, click on the Save
button, and close the Visual Basic Editor.

Open your form in normal mode and try out your new command button.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Sorry I have not responded sooner, I have been out of the office. Compared to
you I am a lowly novice. Can you be more specific in your instructions? In a
form, the customer information has a text field or the email address. The
text field name is Email Address. I have been able to turn on and off the
hyperlink. When the Hyperlinked is on, access assumes it is a web page unless
I precede it with mailto: I am not familiar with PITA, Text 255, or command
buttons. I began using access with not experience. I purchased several books,
and I learn as I go. Once I've learned what you are trying to show me I will
incorporate it in the future.

 

[Tom Wickerath]

Hi Kevin,

To prevent Error 2501, add the following code to the error-handling section.
Interestingly, if I recall correctly (?) a 2501 error is generated if one
cancles an e-mail in Outlook, but not if one cancels an e-mail with Outlook
Express. Go figure.

ProcError:
Select Case Err.Number
Case 2501 'Message closed without sending
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure SendMessage..."
End Select
Resume ExitProc
End Sub


Basically, we trap for Error 2501 using a SELECT Case....END SELECT
construct, and simply ignore it.

2) Next, when I push the command prompt to send the email, Outlook comes up
but not in default view. To: , Cc:, and Bc: are in big buttons and no ruler
at the top. Not a problem but strange.

I'm not sure what to offer on this issue. The reference to the ruler makes
it sound like you might be using Microsoft Word as your e-mail editor.

You had What ever in the subject line and Bla bla bla in the body. I figured
out how to make these blanks. How do you make the variable?

See messages # 5 and 6 in this thread:

http://groups.google.com/group/micr..._frm/thread/20a95054dd0cdcf7/f8aca5cb5dd3b164


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________