Sending e-mail from Access

[Trish T]

After searching all morning and finding a lot of messages about codes
(SendObject) and macros (which I know nothing about), I finally found Wayne's
answer (below) on how to send e-mails to individual contacts in our Contact
Management database (which I created based on a sample CM database I
downloaded from Microsoft.) It works great!! However, I already have over
900 email addresses in my EmailAddress field (we have over 1600 records, but
many are missing this data). Is there an easy way to add "MailTo:" in front
of the ones already in the database?

Also, the Contacts form allows users to add a new contact. How can I have
the words "MailTo:" in the box on the form where they enter the client's
email address (and possibly permanently there, so they can't delete it but
have to type in the e-mail address after it)?

Thanks for your help,
Trish

 

[Wayne-I-M]

Hi Trish

You can use an append qerry to add the mailto: before the e mail address you
have stored in your table.

Note I would create a new field in the table call it NewEMailAddress and
append into that rather than simply changing all you e mail addresses (just
in case in goes wrong)

Don't forget to format NewEMailAddress as a hyerlink if you want to use the
simple OnClick = send mail option

You can use AfterUpdate event on your form control so that something like
If [control] does not start MailTo: then Add MailTo

If you need any help reply with a samle of your e mail address (not real
ones - just an example of the type - this forum may be looked at spamers so
don't a real example)

 

[Wayne-I-M]

Just had a thought - don't forget to append "#" before the Mailto in the append

like this

UPDATE TableName SET TableName.NewEmailAddress = "#" & "MailTo:" &
[TableName]![OldEmailAddress];


Or if your in design view in the update query
Drag the "New EMail address" into the Field Row
Add this to the Update To row

"#" & "MailTo:" & [TableName]![OldEmailAddress];


If you don't add the # it will not work correctly as (I think) you want it to

If you using the field in a form your may have to check the IsHyperlink in
the properties box

 

[Trish T]

Sorry, but I need more help than that. Like instructions on how to add
"MailTo" by using an append query. I tried following the instructions in
Access Help for an append query, but it just adds 1620 new records to my
table. This whole thing may be a little beyond me.
Thanks,
Trish

Hi Trish

You can use an append qerry to add the mailto: before the e mail address you
have stored in your table.

Note I would create a new field in the table call it NewEMailAddress and
append into that rather than simply changing all you e mail addresses (just
in case in goes wrong)

Don't forget to format NewEMailAddress as a hyerlink if you want to use the
simple OnClick = send mail option

You can use AfterUpdate event on your form control so that something like
If [control] does not start MailTo: then Add MailTo

If you need any help reply with a samle of your e mail address (not real
ones - just an example of the type - this forum may be looked at spamers so
don't a real example)


--
Wayne
Manchester, England.

After searching all morning and finding a lot of messages about codes
(SendObject) and macros (which I know nothing about), I finally found Wayne's
answer (below) on how to send e-mails to individual contacts in our Contact
Management database (which I created based on a sample CM database I
downloaded from Microsoft.) It works great!! However, I already have over
900 email addresses in my EmailAddress field (we have over 1600 records, but
many are missing this data). Is there an easy way to add "MailTo:" in front
of the ones already in the database?

Also, the Contacts form allows users to add a new contact. How can I have
the words "MailTo:" in the box on the form where they enter the client's
email address (and possibly permanently there, so they can't delete it but
have to type in the e-mail address after it)?

Thanks for your help,
Trish

 

[Trish T]

Wayne, I so appreciate your help and I'm getting there! I used an update
query and it added MailTo in front of the existing email addresses in my
table and main query. And those existing e-mails now work fine in the form:
when you click on the email address, it opens Outlook with the e-mail address
inserted. Perfect! The problem is adding new email addresses, either into
existing records or when entering a new contact. I tried adding If [control]
does not start MailTo: then Add Mail To to the After Update property (as you
suggested), but I get an error that the macro doesn't exist. Any other ideas?
Thanks!
Trish

Just had a thought - don't forget to append "#" before the Mailto in the append

like this

UPDATE TableName SET TableName.NewEmailAddress = "#" & "MailTo:" &
[TableName]![OldEmailAddress];


Or if your in design view in the update query
Drag the "New EMail address" into the Field Row
Add this to the Update To row

"#" & "MailTo:" & [TableName]![OldEmailAddress];


If you don't add the # it will not work correctly as (I think) you want it to

If you using the field in a form your may have to check the IsHyperlink in
the properties box




--
Wayne
Manchester, England.

After searching all morning and finding a lot of messages about codes
(SendObject) and macros (which I know nothing about), I finally found Wayne's
answer (below) on how to send e-mails to individual contacts in our Contact
Management database (which I created based on a sample CM database I
downloaded from Microsoft.) It works great!! However, I already have over
900 email addresses in my EmailAddress field (we have over 1600 records, but
many are missing this data). Is there an easy way to add "MailTo:" in front
of the ones already in the database?

Also, the Contacts form allows users to add a new contact. How can I have
the words "MailTo:" in the box on the form where they enter the client's
email address (and possibly permanently there, so they can't delete it but
have to type in the e-mail address after it)?

Thanks for your help,
Trish

 

[Wayne-I-M]

Open your form in design view
Right click the NewEmailAddress control
Open the properties box
In the event column selec AfterUpdate
Click the build option (...) You will see this


Private Sub NewEmailAddress_AfterUpdate()

End Sub


You should add this one line of code
Me.NewEmailAddress = "#" & "MailTo:" & Me.NewEmailAddress.Text



so that it looks like this


Private Sub NewEmailAddress_AfterUpdate()
Me.NewEmailAddress = "#" & "MailTo:" & Me.NewEmailAddress.Text
End Sub


Of course you can easily improve this be using the <>"Mailto*" to check if
the user has not already added the Mailto secion of the address. Repost if
you need this option.


--
Wayne
Manchester, England.

Wayne, I so appreciate your help and I'm getting there! I used an update
query and it added MailTo in front of the existing email addresses in my
table and main query. And those existing e-mails now work fine in the form:
when you click on the email address, it opens Outlook with the e-mail address
inserted. Perfect! The problem is adding new email addresses, either into
existing records or when entering a new contact. I tried adding If [control]
does not start MailTo: then Add Mail To to the After Update property (as you
suggested), but I get an error that the macro doesn't exist. Any other ideas?
Thanks!
Trish

Just had a thought - don't forget to append "#" before the Mailto in the append

like this

UPDATE TableName SET TableName.NewEmailAddress = "#" & "MailTo:" &
[TableName]![OldEmailAddress];


Or if your in design view in the update query
Drag the "New EMail address" into the Field Row
Add this to the Update To row

"#" & "MailTo:" & [TableName]![OldEmailAddress];


If you don't add the # it will not work correctly as (I think) you want it to

If you using the field in a form your may have to check the IsHyperlink in
the properties box




--
Wayne
Manchester, England.

After searching all morning and finding a lot of messages about codes
(SendObject) and macros (which I know nothing about), I finally found Wayne's
answer (below) on how to send e-mails to individual contacts in our Contact
Management database (which I created based on a sample CM database I
downloaded from Microsoft.) It works great!! However, I already have over
900 email addresses in my EmailAddress field (we have over 1600 records, but
many are missing this data). Is there an easy way to add "MailTo:" in front
of the ones already in the database?

Also, the Contacts form allows users to add a new contact. How can I have
the words "MailTo:" in the box on the form where they enter the client's
email address (and possibly permanently there, so they can't delete it but
have to type in the e-mail address after it)?

Thanks for your help,
Trish

:

Hi Tim

There is a very simple method that you may want to try.
All you need to do it to store the hyperlink in a field and then (on your
form) click it and it will open the outlook message to that person.
OK it's not "quite" as simple as that - but almost. You need to store the
link in the right way for it to all work smoothly.

* Open you "table" that holds the data the form is based on (not the query
- but the table itself).
* In design view add a new field (I say add a new field as if you simply
change an old one you may lose all the data in the field - so use a new one).
* Format it from the dropdown list as a “hyperlinkâ€
* Save the table
* Open it in table view
* Now this is where you need to get the link right

If you simply put something like
(e-mail address removed) this is what you will see

BUT access actually “stores†(even if you can’t see it)
[email protected]#http://[email protected]#

To get round this you need to type this
MailTo: (e-mail address removed)

Next close the table and bring the new field into the query the form is
based on

If you are not basing your form on a query but directly from the table go
and sit in a small dark room and have a dam good talk with yourself and
convince yourself that you should not base a form on a table – but rather you
should use a query.

Next bring the new field into your form. Use view - available fields.

When you click it will open outlook with the address in the right place.

Top Tip – simple cheat – create a small rectangle (or small square) on your
form. Color it green (or whatever). Select you e mail address field and
color this green also. Put the new box over the e mail address field and
send to the back. Now when you click the nice looking box this will act as
the button for the hyperlink.

Have fun

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.

 

[Trish T]

I inserted the event procedure as you said, but when I try to enter an
e-mail address into the form I get an error, "Compile error: Method or data
member not found" and the Visual Basic window opens and the second instance
of .NewEmailAddress is highlighted in gray and the first line -- Private Sub
EmailAddress_AfterUpdate() -- is highlighted in yellow.

Open your form in design view
Right click the NewEmailAddress control
Open the properties box
In the event column selec AfterUpdate
Click the build option (...) You will see this


Private Sub NewEmailAddress_AfterUpdate()

End Sub


You should add this one line of code
Me.NewEmailAddress = "#" & "MailTo:" & Me.NewEmailAddress.Text



so that it looks like this


Private Sub NewEmailAddress_AfterUpdate()
Me.NewEmailAddress = "#" & "MailTo:" & Me.NewEmailAddress.Text
End Sub


Of course you can easily improve this be using the <>"Mailto*" to check if
the user has not already added the Mailto secion of the address. Repost if
you need this option.


--
Wayne
Manchester, England.

Wayne, I so appreciate your help and I'm getting there! I used an update
query and it added MailTo in front of the existing email addresses in my
table and main query. And those existing e-mails now work fine in the form:
when you click on the email address, it opens Outlook with the e-mail address
inserted. Perfect! The problem is adding new email addresses, either into
existing records or when entering a new contact. I tried adding If [control]
does not start MailTo: then Add Mail To to the After Update property (as you
suggested), but I get an error that the macro doesn't exist. Any other ideas?
Thanks!
Trish

Just had a thought - don't forget to append "#" before the Mailto in the append

like this

UPDATE TableName SET TableName.NewEmailAddress = "#" & "MailTo:" &
[TableName]![OldEmailAddress];


Or if your in design view in the update query
Drag the "New EMail address" into the Field Row
Add this to the Update To row

"#" & "MailTo:" & [TableName]![OldEmailAddress];


If you don't add the # it will not work correctly as (I think) you want it to

If you using the field in a form your may have to check the IsHyperlink in
the properties box




--
Wayne
Manchester, England.



:

After searching all morning and finding a lot of messages about codes
(SendObject) and macros (which I know nothing about), I finally found Wayne's
answer (below) on how to send e-mails to individual contacts in our Contact
Management database (which I created based on a sample CM database I
downloaded from Microsoft.) It works great!! However, I already have over
900 email addresses in my EmailAddress field (we have over 1600 records, but
many are missing this data). Is there an easy way to add "MailTo:" in front
of the ones already in the database?

Also, the Contacts form allows users to add a new contact. How can I have
the words "MailTo:" in the box on the form where they enter the client's
email address (and possibly permanently there, so they can't delete it but
have to type in the e-mail address after it)?

Thanks for your help,
Trish

:

Hi Tim

There is a very simple method that you may want to try.
All you need to do it to store the hyperlink in a field and then (on your
form) click it and it will open the outlook message to that person.
OK it's not "quite" as simple as that - but almost. You need to store the
link in the right way for it to all work smoothly.

* Open you "table" that holds the data the form is based on (not the query
- but the table itself).
* In design view add a new field (I say add a new field as if you simply
change an old one you may lose all the data in the field - so use a new one).
* Format it from the dropdown list as a “hyperlinkâ€
* Save the table
* Open it in table view
* Now this is where you need to get the link right

If you simply put something like
(e-mail address removed) this is what you will see

BUT access actually “stores†(even if you can’t see it)
[email protected]#http://[email protected]#

To get round this you need to type this
MailTo: (e-mail address removed)

Next close the table and bring the new field into the query the form is
based on

If you are not basing your form on a query but directly from the table go
and sit in a small dark room and have a dam good talk with yourself and
convince yourself that you should not base a form on a table – but rather you
should use a query.

Next bring the new field into your form. Use view - available fields.

When you click it will open outlook with the address in the right place.

Top Tip – simple cheat – create a small rectangle (or small square) on your
form. Color it green (or whatever). Select you e mail address field and
color this green also. Put the new box over the e mail address field and
send to the back. Now when you click the nice looking box this will act as
the button for the hyperlink.

Have fun

--
Wayne
Manchester, England.
Not an expert.
Enjoy whatever it is you do.