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!"@dom ain"."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:xxx@doma in.com) 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...you r 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 (txtEMailAddres s, 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_CUSTOM ERS and form HOTSTICK_CUSTOM ERS. (I was educated on better naming after I named everything...wi ll 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.