Send Automated Email by Clicking on a Button?

[Guest]

Hello:

I checked my Access book and some of the previous threads but I couldn't
find an answer to my questions. I am in the process of building a little Many
to Many application to track student registration for our training classes.
One of the fields that will be captured is of course the student's email
address.

I need the ability to click on a button and automatically send an email to
the student confirming receipt of their registration.

1. Can this be done in Access 2003?
2. If so, how do I design the script?

Thanks,
RT

 

[Guest]

Hello:

I think should provide more specific details and ask if we can do this in
Access 2003?

We're going to enter the student's class registration info into a child
table [tblLink] that includes the Date, Time, and Location of the class. We
then want to look up the student's email address from tblStudents, and send a
confirmation email message, via Outlook 2003, by clicking on a button. That
email confirmation should include the following information.

The Student's First& Lastname,plus the student's StudentID number from
"tblStudents".

The CourseID and CourseName from "tblCourses" and the

ClassDate, ClassTime, and Location from "tblLink".

Thanks,
RT

 

[Arvin Meyer [MVP]]

Have a look at this code and see if it will do what you need:

http://www.datastrat.com/Code/OutlookEmail.txt

It should be easy enough to add a few lines to fill in the body of the
message.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thanks so much Arvin for pointing me in the right direction, but it’s still
unclear to me how to capture the info and then click on a button that
automatically sends an Outlook 2003 email with the information we need in the
message. I’m assuming we can capture all of the values we need from the
current record and place them into variables such as vSTudentID and
vStudentLastName, etc.

But then how do we get those variables into the email and how do we address
it to the student’s email address? And I'm confused about the attachment, we
don't need nor want any attachments.

The information we need to capture and put into the email is as follows:

Addressed to: StudentEmail

StudentID, StudentLastName, StudentFirstName,

CourseID and CourseName

ClassDate, ClassTime, and ClassLocation

Then we would of course have a standard message such as, “You are now
registered to attend the course shown above.

Thank you,
Training Staff

There are 3 tables in the Many to Many design, they are:

tblCourses, tblStudents, and tblLink

tblLink has the following fields

CourseID
StudentID
Class_Date
Class_Time
Class_Location
Confirmed
Attended

 

[Arvin Meyer [MVP]]

OK, here's the relevant code, assuming that the control names of your fields
is the same as below:

Private Sub Command0_Click()
'Arvin Meyer 03/12/1999
'Updated 7/21/2001
On Error GoTo Error_Handler

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strBody As String

strBody = Me.StudentID & ", " & _
Me.StudentLastName & ", " & _
Me.StudentFirstName & vbCrLf & vbCrLf & _
Me.CourseID & " and " & _
Me.CourseName & vbCrLf & vbCrLf & _
Me.ClassDate & ", " & _
Me.ClassTime & ", and " & _
Me.ClassLocation & vbCrLf & vbCrLf & _
"You are now registered to attend the course shown above."

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = Me.StudentEmail
.Subject = "Registration Information"
.body = strBody
.Send
'.ReadReceiptRequested
End With

Exit_Here:
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Hi Arvin:

Is it me or do others also have a difficult time accessing this Newsgroup?
It seems as if the Internet Gods have to be aligned just right to enter the
forum.

I can't begin to tell you how much I appreciate your help. I'm having some
problems with this Many to Many design, so I'm editing the table structure
and moving some of the fields. However, the names of the fields are the same
so that shouldn't be a problem. At least I'm assuming it doesn't matter when
using ME.??? if the field is in a parent or child table, is that correct?

Nonetheless, this looks like it's exactly what I need to get me going. If I
have any specfic questions and if the Internet Gods are aligned correctly,
I'll ask them [if you don't mind].

Thanks again and have a great day,
Robert

 

[Arvin Meyer [MVP]]

This morning I put a simple many-to-many example on the Access MVP website.
You may want to take a look at it, if you're still having trouble:

http://www.accessmvp.com/Arvin/ManyToMany.zip
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi Arvin:

Is it me or do others also have a difficult time accessing this Newsgroup?
It seems as if the Internet Gods have to be aligned just right to enter
the
forum.

I can't begin to tell you how much I appreciate your help. I'm having some
problems with this Many to Many design, so I'm editing the table structure
and moving some of the fields. However, the names of the fields are the
same
so that shouldn't be a problem. At least I'm assuming it doesn't matter
when
using ME.??? if the field is in a parent or child table, is that correct?

Nonetheless, this looks like it's exactly what I need to get me going. If
I
have any specfic questions and if the Internet Gods are aligned correctly,
I'll ask them [if you don't mind].

Thanks again and have a great day,
Robert

OK, here's the relevant code, assuming that the control names of your
fields
is the same as below:

Private Sub Command0_Click()
'Arvin Meyer 03/12/1999
'Updated 7/21/2001
On Error GoTo Error_Handler

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strBody As String

strBody = Me.StudentID & ", " & _
Me.StudentLastName & ", " & _
Me.StudentFirstName & vbCrLf & vbCrLf & _
Me.CourseID & " and " & _
Me.CourseName & vbCrLf & vbCrLf & _
Me.ClassDate & ", " & _
Me.ClassTime & ", and " & _
Me.ClassLocation & vbCrLf & vbCrLf & _
"You are now registered to attend the course shown above."

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = Me.StudentEmail
.Subject = "Registration Information"
.body = strBody
.Send
'.ReadReceiptRequested
End With

Exit_Here:
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Hi Arvin:

I'm definitely going to check out your example. I think I've got most of the
design down, however, I'm having a problem figuring out where to put the
CourseNo field to track which courses the students are taking. Right now I
can tell which students are registered in each class and which classes each
student took.

I posted the details in the "Database Design" section. I think the title is:
"Need Help Tweaking a Many to Many Design"

Thanks,
Robert