record selection in emails

[philbertson]

below is the code i have to send emails based upon an event.

Dim db As Database

Dim requestqry As Recordset
Dim objoutlook As New Outlook.Application
Dim objmessage As MailItem
Dim strSQL As String
Dim Body As String
Dim email As String


Set db = CurrentDb()


Set requestqry = db.OpenRecordset("finemail")


While Not requestqry.EOF


If Not IsNull(requestqry("fin email")) Then
Set objmessage = objoutlook.CreateItem(olMailItem)
With objmessage
.To = requestqry("fin email")
.Subject = "Employee Request Waiting Approval"
.Body = Body
.Send
End With
End If

requestqry.MoveNext
Wend
requestqry.close
Set requestqry = Nothing
Set objoutlook = Nothing
Set objmessage = Nothing


The body copy of the message is omited for it is of no importance here. Anyway. The code finds the proper email, send the message and all is well right? Wrong.

It gets its data based upon a query. the problem is that it send a email for every record in the query. if i set the query to show only the top record, it sends the top record only.

What i need is to send the record that is currently being viewed/edited on the form where the control button is that triggers this code.

Is this a code problem, a query problem and how do i fix it.

Phil

 

[MarkK]

Refine this line...
Set requestqry = db.OpenRecordset("finemail")
...so it returns only one record, like...
Set rst = db.OpenRecordset("SELECT fld1, fld2, fld3 FROM SomeTable WHERE RecipientID = " & <data from form>)
...and eliminate your while...wend loop.

 

[philbertson]

Tried, now have new problem

i have changed the code as you adivesed and i am now getting an error saying "too few parameters, expected 7"

here is what i did with the code

Set requestqry = db.OpenRecordset("SELECT Request_ID, fin_email, Desired_Position_Title, Department, Dept_Hiring_Contact, Hiring_Manager, departmanger_appr, Depart_man_date, Date1 FROM Finemail WHERE Request_ID = " & Me.Request_ID)

The green text are field names. the red is a quary name.

in the last section where you said to <data from form> do i have it formated correctly and where you said SomeTable can that also be a quary.

Help please!

 

[jgc31]

if request_ID is a string then syntax should be be ....Request_ID=""" & Request_ID & """". If this does not work one approach that can work is to build the query in query builder check it gives the correct result and either copy the SQL or by using a combination of steping through the code in debug mode and then looking at the locals window to check the exact syntax of the requestqry SQL statement.

 

[MarkK]

Yes, if you're having trouble with the query, you can build it in the designer and then copy the text from the SQL view.

 

[philbertson]

figured it out

i finally got it. the code from the qry did it. then after all that trouble it dawned on me. All the data i need is displayed in the form from which the email was being sent so there was a simpler solution. i used me.control to extract the data from the form and place it in the email. After i did that i was able to get rid of almost all the Dim strings and qrys alltogether.

Even though i didn't need the more elaborate version of the code i had been working on, at least i now know how to do it for when i do need to.

Thanks a bunch.

 

[rsmhawaii]

Mind sharing your procedure?

Hi, would you mind sharing the steps you took to copy the form data onto an email? I'm wanting to create a database where our employees can submit their travel requests via the database and then our boss is notified that there is a request awaiting his approval.

Thanks!