Hyperlinks, input masks, and sending email in Access 2007 | Member | | Join Date: Sep 2009 Location: Alabama
Posts: 101
| |
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:xxx@domain.com) but sometimes, say for instance if the address was mbo@domain.com, 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! :-)
| |
best answer - posted by ADezii |
The following code will:- Perform minimal validation on the E-Mail Address as entered into the [COMPANY_EMAIL] Field.
- Open a Session of Microsoft Outlook with the chosen E-Mail Address as entered into the [COMPANY_EMAIL] Address Field as the To: segment.
- Leave the Subject and Body empty for the User to fill in.
- Simply fill in the missing info then Send.
- 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
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,765
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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.
| | Member | | Join Date: Sep 2009 Location: Alabama
Posts: 101
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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. :-)
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,226
| | | re: Hyperlinks, input masks, and sending email in Access 2007
Quote:
Originally Posted by 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:xxx@domain.com) but sometimes, say for instance if the address was mbo@domain.com, 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! :-) Danica, what is your E-Mail Client (Outlook, Internet Explorer, etc.)?
| | Member | | Join Date: Sep 2009 Location: Alabama
Posts: 101
| | | re: Hyperlinks, input masks, and sending email in Access 2007
Quote:
Danica, what is your E-Mail Client (Outlook, Internet Explorer, etc.)?
I use MS Outlook. Thanks.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,226
| | | re: Hyperlinks, input masks, and sending email in Access 2007
It should be a relatively simple matter to automate the process of send an E-Mail to a specific Address within Outlook, but first: - 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.)?
- 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...
| | Member | | Join Date: Sep 2009 Location: Alabama
Posts: 101
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,226
| | | re: Hyperlinks, input masks, and sending email in Access 2007
The following code will: - Perform minimal validation on the E-Mail Address as entered into the [COMPANY_EMAIL] Field.
- Open a Session of Microsoft Outlook with the chosen E-Mail Address as entered into the [COMPANY_EMAIL] Address Field as the To: segment.
- Leave the Subject and Body empty for the User to fill in.
- Simply fill in the missing info then Send.
- 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
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,765
| | | re: Hyperlinks, input masks, and sending email in Access 2007
Quote:
Originally Posted by 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. :-) 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).
| | Member | | Join Date: Sep 2009 Location: Alabama
Posts: 101
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,765
| | | re: Hyperlinks, input masks, and sending email in Access 2007
Sounds like fun Danica. Go and enjoy your weekend (and your family). We'll all be here again on Monday I expect ;)
| | Member | | Join Date: Sep 2009 Location: Alabama
Posts: 101
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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!
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,186
| | | re: Hyperlinks, input masks, and sending email in Access 2007
ADezii's code can not produce that message. How much have you changed it?
| | Member | | Join Date: Sep 2009 Location: Alabama
Posts: 101
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,226
| | | re: Hyperlinks, input masks, and sending email in Access 2007
Quote:
Originally Posted by 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! 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
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,186
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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. Quote:
Originally Posted by 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? | | Member | | Join Date: Sep 2009 Location: Alabama
Posts: 101
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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.)
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,765
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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 "@" & ".".
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,226
| | | re: Hyperlinks, input masks, and sending email in Access 2007
Quote:
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.
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,186
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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?
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,765
| | | re: Hyperlinks, input masks, and sending email in Access 2007
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.
|  |
Similar Microsoft Access / VBA bytes | | |  Forums
Visit our community forums for general discussions and latest on Bytes
/bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,567 network members.
|
