Simple email from Access 2003 form

[Fred]

I have been searching out VB code to accomplish a simple task and keep
running into snags. I have a form including these fields:

- CourseName
- CourseNumber
- ProjectCoordinator
- email

The "ProjectCoordinator" control on the Courses form is a combo box
drawing from a related table called (T) Names. The "email" control is
automatically populated when the name is chosen.

I want to create a button that, when clicked, creates an email in
Outlook 2003 to the Project Coordinator and tells them that their
learning module has been uploaded and is ready for the staff.

Here is the code I have so far - it is edited from the closest I could
find in another message here.

Private Sub Command231_Click()
Dim strTo As String
Dim strBody As String
Dim strSubject As String

strTo = DLookup(email, "(T) Names", "Name = " & Me.ProjectCoordinator)
strBody = "Your CBL with the title and course number shown above has
been published in the Learning Management System and is available for
staff."
strSubject = "Learing Module information"
DoCmd.SendObject , , acFormatTXT, strTo, , , strSubect, strBody, True

End Sub

I am getting an error in the strTo line = Runtime error 3705. Syntax
error (missing operator) in query expression '(e-mail address removed)'

Is this ensough info to tell where I am messing up?

Thanks,
Fred

 

[Pieter Wijnen]

Quotes, Quotes

try
strTo = DLookup("email", "(T) Names", "Name = '" & Me.ProjectCoordinator &
"'")

Pieter

PS that is a nasty name for a Table/Query
don't you get tired by hitting [] <g>

I have been searching out VB code to accomplish a simple task and keep
running into snags. I have a form including these fields:

- CourseName
- CourseNumber
- ProjectCoordinator
- email

The "ProjectCoordinator" control on the Courses form is a combo box
drawing from a related table called (T) Names. The "email" control is
automatically populated when the name is chosen.

I want to create a button that, when clicked, creates an email in
Outlook 2003 to the Project Coordinator and tells them that their
learning module has been uploaded and is ready for the staff.

Here is the code I have so far - it is edited from the closest I could
find in another message here.

Private Sub Command231_Click()
Dim strTo As String
Dim strBody As String
Dim strSubject As String

strTo = DLookup(email, "(T) Names", "Name = " & Me.ProjectCoordinator)
strBody = "Your CBL with the title and course number shown above has
been published in the Learning Management System and is available for
staff."
strSubject = "Learing Module information"
DoCmd.SendObject , , acFormatTXT, strTo, , , strSubect, strBody, True

End Sub

I am getting an error in the strTo line = Runtime error 3705. Syntax
error (missing operator) in query expression '(e-mail address removed)'

Is this ensough info to tell where I am messing up?

Thanks,
Fred

--

 

[Fred]

Quotes, Quotes

try
strTo = DLookup("email", "(T) Names", "Name = '" & Me.ProjectCoordinator &
"'")

Pieter

PS that is a nasty name for a Table/Query
don't you get tired by hitting [] <g>

Thank you, Pieter - yes, it worked.

I never said I was smart <g> so nasty (T) Table Names, (F) Form Names,
(M) Macro Names, (Q) Queries, etc. have resulted - need to switch to
frmForm, tblTable, rptReport, etc.

 

[Tony Toews]

I never said I was smart <g> so nasty (T) Table Names, (F) Form Names,
(M) Macro Names, (Q) Queries, etc. have resulted - need to switch to
frmForm, tblTable, rptReport, etc.

No. Don't bother with the frm, tbl, rpt, etc. Not only are they
useless they also don't allow you to hit the first letter of the
object name while in the database container window and quickly proceed
down to that set of objects.

And to take things one step further.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Fred]

No. Don't bother with the frm, tbl, rpt, etc. Not only are they
useless they also don't allow you to hit the first letter of the
object name while in the database container window and quickly proceed
down to that set of objects.

And to take things one step further.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Tony,

Thank you! I needed a prod to get more logical and streamlined in my
fledgling DB designs.

Fred