Email question

[teiben]

I have a form with a field called champion. I need when a user hits a command button, who ever name on the champion textbox to get an email message. I made the champion field a combobox with a rowsource to a lookup table, tblCorpChampions. tblCorpChampions contains the champion names and their email address.
How do I code the button to just email the person selected in the champion cbobox.
I'm guessing I would have the button open a module: I've tried it, below is the code, but I'm doing something wrong.

Private Sub cmdNotifyChampion_Click()
OpenCorpChampion


Option Compare Database
Option Explicit

Sub OpenCorpChampion()
Dim rsEmail As DAO.Recordset
Dim strEmail As String

Set rsEmail = CurrentDb.OpenRecordset("tblCorpChampions") 'table where email is
strEmail = rsEmail.Fields("cemailaddress").Value
rsEmail.MoveNext
Do While Not rsEmail.EOF
strEmail = strEmail & " ; " & rsEmail.Fields("tblCorpChampions").Value
rsEmail.MoveNext
Loop

DoCmd.SendObject , , , strEmail, , , "TS-16949 Non-Conformance", "NC #" & " " & Forms!fEnterNewCorporate.Text19 & " " & "was issued entered into the system ", False, ""
Set rsEmail = Nothing
MsgBox "Email notifications have been sent"
End Sub

 

[bonekrusher]

Try this. Make sure that MS Outlook 11.0 Object Library is selected in your tools.

"Email" is the field whicjh holds the address. "sub" is a test box for they user to type a subject.

DoCmd.SendObject acSendNoObject, "", "", Me.Email, "", "", Me.sub