Hyperlinks, input masks, and sending email in Access 2007

[DanicaDear]

I've been trying to figure this out for three weeks. I've been sleeping on it. Not getting answers, just losing sleep. ;-)

In a table, I have a field COMPANY_EMAIL. In this field I want to allow up to 8 characters, use the same domain always, and in a form be able to click and open an email to this customer. I have tried this in numerous ways, and I think it's occuring to me finally I can't do everything in one place. I think I might need a button.

I'm using <aaaaaaaa!"@domain"."com" in my input mask. This is pretty good, except if the address is only 4 letters, it leaves 4 spaces on the left side. I don't like that. Ideas?

I have put a button next to the email field in the form (as opposed to trying to use a hyperlink and an input mask...that got me nowhere fast). Right now the button is just sitting there. I know I'll need some code probably behind "on click" but I don't know what to put there to open an email to the address specified. Ideas??

I'm aware of the mailto: part...I tried to use one field as a hyperlink with the mailto: and domain.com all in the input mask (to end up with mailto:[e-mail]) but sometimes, say for instance if the address was [e-mail], the "m" would replace the "m" in mailto: and the "bo" would go in as the email address. When I chose an address that would go in correctly and clicked it in the form, it would never open an email anyway...so I'm trying to come up with a new plan...like the button plan above.

I need your guidance/ideas, so throw some at me!!!
Thank you so much for your time, patience, and brains! :-)

[NeoPa]

I'm not too hot on mailing from an application. Whenever I tried it the side issues put me off. There are a whole bunch of threads (some in which I'm involved) which deal with security problems etc etc.

Having said that, I can certainly help with the interface and how to make up, and even store, the email addresses.

Let's start with a question. Do you really want the "@blah blah.com" bit to be entered by the operator and/or saved? I would consider including that as static on the form in a Label control and just dealing with the name part in the interface.

[DanicaDear]

I could definetely be convinced to go one way or the other. You see, I am just doing what I can comprehend on Access...I'm not yet familiar with all the other, better ways things can be done. I'm learning A LOT here on BYTES. In fact, BYTES has probably made this project a success instead of a failure for me.
So, it seems you encourage me to just deal with the first part of the email address. I can go that route, I'll just need some guidance on how to get the @domain.com connected. The people that will be using the DB are far less computer savvy than I am, so I wanted to make it easy for them to open an email to the customer. Persuade me in a direction...your advice so far has been phenominal. :-)

[ADezii]

@DanicaDear
Danica, what is your E-Mail Client (Outlook, Internet Explorer, etc.)?

[DanicaDear]

Danica, what is your E-Mail Client (Outlook, Internet Explorer, etc.)?

I use MS Outlook. Thanks.

[ADezii]

It should be a relatively simple matter to automate the process of send an E-Mail to a specific Address within Outlook, but first:

1. Where are you obtaining the E-Mail Address from? If it is a Field on a Form, then what is the Field Name (txtEMailAddress, txtCompany, etc.)?
2. Do you want the E-Mail automatically sent with Default Body Text and Subject, or do you simply want the Outlook Window open with the To: and Subject: automatically filled in, and leave the Body: to you?, etc...

[DanicaDear]

1. Field COMPANY_EMAIL in table HOTSTICK_CUSTOMERS and form HOTSTICK_CUSTOMERS. (I was educated on better naming after I named everything...will do better in the future.) Also note, I changed the name to appear as "Email" to the user. But I think the program writer is mostly concerned with the name that shows in design view...which is COMPANY_EMAIL.
2. I want to open an email and leave both the subject and the body as the user's responsibility. I can see where I might eventually in the future want to automate things much more but for now I'm just a beginner and I could spend a year writing this DB if I don't watch it. ;-) (Why am I suddenly laughing out loud?)

The lab freqeuntly sends out saved emails where sometimes it would likely be easier to copy and paste the email address from Access to Outlook. On other occasions, they might want to send a unique email and that is where clicking the email address in the Access program would be more helpful. That's why I initially tried to create one text box with the domain attached and make it a hyperlink...so I could have flexibility to copy or click. I'm still open and conviceable. You people know lots more than me. I tend to take good advice. Thanks again, Danica

[ADezii]

The following code will:

1. Perform minimal validation on the E-Mail Address as entered into the [COMPANY_EMAIL] Field.
2. Open a Session of Microsoft Outlook with the chosen E-Mail Address as entered into the [COMPANY_EMAIL] Address Field as the To: segment.
3. Leave the Subject and Body empty for the User to fill in.
4. Simply fill in the missing info then Send.
5. Be sure to set a Reference to the Microsoft Outlook XX.X Object Library before you attempt to execute this code.

'YOU MUST FIRST SET A REFERENCE TO THE OUTLOOK XX.X OBJECT LIBRARY
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.Namespace
Dim strEMailAddress As String
Dim txtEMail As TextBox
 
Set txtEMail = Me![COMPANY_EMAIL]
 
'Let's at least perform Minimal Validation
If IsNull(txtEMail) Then
  MsgBox "No E-Mail Address listed", vbCritical, "Missing E-Mail Address"
    txtEMail.SetFocus
      Exit Sub
ElseIf InStr(txtEMail, "@") = 0 Or InStr(txtEMail, ".") = 0 Then
  MsgBox "Invalid E-Mail Address", vbCritical, "Invalid E-Mail Address"
    txtEMail.SetFocus
      Exit Sub
End If
 
strEMailAddress = txtEMail
 
DoCmd.Hourglass True
 
Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)
 
With oMail
  .To = strEMailAddress
  .Body = ""
  .Subject = ""
    .Display
End With
 
Set oMail = Nothing
Set oLook = Nothing
 
DoCmd.Hourglass False

[NeoPa]

@DanicaDear
OK. For the sake of this we'll assume that the domain part of the email address is to be @Domain.com. We can also work on an eight character name part if you like, but I expect you can handle that in the design of your form (and control). You would need to change what type of data is expected in [COMPANY_EMAIL], as well as add a Label control after it which displayed the domain name. The Label control (Let's call it lblDomain) would display @Domain.com. This is easily changed in Design View for a different domain of course.

What I'm suggesting here is easily incorporated into ADezii's code. It would simply replace line #8.

Set txtEMail = Me![COMPANY_EMAIL]

All we do really is add the domain address (conveniently found in lblDomain) to the end of [COMPANY_EMAIL] to produce the full address :

Set txtEMail = Me.[COMPANY_EMAIL] & Me.lblDomain.Caption

Otherwise, I'm sure ADezii's code will work fine for you (not that it wouldn't without this suggestion you understand).

[DanicaDear]

Thanks everyone. I'm off til Monday. Will get restarted then. I'm soooo tempted to try this right away (because I will admit this project is kind of exciting). However, 40 hours of Access is enough in a week so I definitely need to take my breaks so I don't get burnt out. (Plus I have the cutest little one year old!) :-)
Y'all have a nice weekend.

[NeoPa]

Sounds like fun Danica. Go and enjoy your weekend (and your family). We'll all be here again on Monday I expect ;)

[DanicaDear]

Hey GUYS! LOL.
I'm trying to get the email thing to work. When I tried ADezii's code with my email address I got a warning box that said "Not a valid email address." Then I tried NeoPa's suggestion and I get the debugger which takes me to NeoPa's line #8. I think I did all the suggestions you made NeoPa...but something isn't right. (Set the reference to Outlook, properly named the form, etc etc). Can you help? Thanks!

[ChipR]

ADezii's code can not produce that message. How much have you changed it?

[DanicaDear]

On the HOTSTICK_CUSTOMERS form, all I did was add the label "@southernco.com", change the name and picture on the button, attach ADezii's code at the OnClick event, and change the line number 8 from

Set txtEMail = Me![COMPANY_EMAIL] 

to

Set txtEMail = Me.[COMPANY_EMAIL] & Me.lblDomain.Caption

If you find worse than that...I did it on accident. :-)

The form only lists 13 customers and a few of those *do* have bad email addresses...but you will be able to tell what's correct just by looking. Those that are screwed up are due to me playing with hyperlinks previously. I have only tried to use the button on the addresses that are valid.

[ADezii]

@DanicaDear
Completely replace your code with the following code (Copy and Paste), and all should be well in your world.

'YOU MUST FIRST SET A REFERENCE TO THE OUTLOOK XX.X OBJECT LIBRARY
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.NameSpace
Dim strEMailAddress As String
Dim strEMail As String
 
'Set txtEMail = Me.[COMPANY_EMAIL]
strEMail = Me.[COMPANY_EMAIL] & Me.lblDomain.Caption
 
'Let's at least perform Minimal Validation
If IsNull(Me![COMPANY_EMAIL]) Then
  MsgBox "No E-Mail Address listed", vbCritical, "Missing E-Mail Address"
    Me![COMPANY_EMAIL].SetFocus
      Exit Sub
ElseIf InStr(strEMail, "@") = 0 Or InStr(strEMail, ".") = 0 Then
  MsgBox "Invalid E-Mail Address", vbCritical, "Invalid E-Mail Address"
    txtEMail.SetFocus
      Exit Sub
End If
 
DoCmd.Hourglass True
 
Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)
 
With oMail
  .To = strEMail
  .Body = ""
  .Subject = ""
    .Display
End With
 
Set oMail = Nothing
Set oLook = Nothing
 
DoCmd.Hourglass False

[ChipR]

Ok, let's look at what the code actually does, rather than just copy and paste. The line number 8 that you changed appends the "@southernco.com" onto the [COMPANY_EMAIL], so I will assume the [COMPANY_EMAIL] you are testing with is just something like a name.
Look at lines 10 through 19. You are checking to verify that [COMPANY_EMAIL] contains both a "@" and "." symbol, and it clearly doesn't since you are going to paste them on the end later. Perhaps instead, you should verify that it doesn't have those characters.
@ChipR

[DanicaDear]

Oh my goodness...you people ROCK!!!!!!!!!!!!
ChipR...thank you for your hints. They are helping.
ADezii, thanks for the code!
NeoPa, thanks for the good explanations.
I'm out of questions for now but I'm sure I'll be back with a new string very soon! LOL. (Some of you may not want to open a new can of worms. hahaha.)

[NeoPa]

You do keep us somewhat busy Danica, but I admit to feeling pretty confident when seeing a question from you that you'll do all you can to make the question make sense and work with us when we post. That's way better than average so I'm happy to look forward to your next question.

PS. @Chip. Your points are well considered, but I think you may have overlooked something in the detail. ADezii's code checks the control contains a value, then goes on to check that the resultant variable strEMail contains both the characters "@" & ".".

[ADezii]

You do keep us somewhat busy Danica, but I admit to feeling pretty confident when seeing a question from you that you'll do all you can to make the question make sense and work with us when we post. That's way better than average so I'm happy to look forward to your next question.

I'm with you 100% on this one, NeoPa.

[ChipR]

I wrote that before ADezzi posted the new code, but finished after it, so it doesn't apply to the post right above it. Perhaps you could move/remove it?

[NeoPa]

I cannot sensibly remove it Chip (sorry) after various references to it, but I have added a quote to your last post into it to make it clear for anyone who may come across it.

[NeoPa]

A new thread was added here as a hijack. If anyone's interested in finding it, it can be found at eMail Problem in Access 2007.