Send email to recipient based on query (1 Viewer)

D0165

New member
Local time
Today, 18:49
Joined
Jun 1, 2022
Messages
9
Hi,

I want to make a button, that starts sending out emails based on a query. I dont want the Query to be in the vba script itself, but I want the button to open a query first, then based on that results I want to start sending mails.

The query uses a popup, when i click the query it opens a popup field and I can type the last name. When i press enter I get all results based on that last name.

I want to make the mail button work the same way. When I click it, i want the query popup to show and when I type the last name and press enter, i want to mail all those based on the query results.
I managed to get the mailscript working, however it always gets all the persons in the table instead of the query results, any idea what I am doing wrong?

Current code that works, but not with a query:
Code:
 Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strEMail As String
    Dim strSubject As String
    Dim strBody As String
    Dim stDocName As String
 
 
 
    Set MyDB = CurrentDb
        
    Set rst = MyDB.OpenRecordset("table1", dbOpenForwardOnly)
 
    With rst
      Do While Not .EOF
        strEMail = ![Emailadres] & ";"
        strSubject = "Subject Title Mail"
        strBody = "Test1 " & ![lastname] & "," & vbCrLf & _
        "" & vbCrLf & _
"Text2" & vbCrLf & _
"" & vbCrLf & _
"Text3" & vbCrLf & _
"" & vbCrLf & _
"Text4," & vbCrLf & _
"" & vbCrLf & _
"Text5"

          DoCmd.SendObject , , , strEMail, , , strSubject, strBody
          .MoveNext
      Loop
    End With
 
    rst.Close
    Set rst = Nothing

Thanks in advance!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:49
Joined
May 7, 2009
Messages
19,169
use your query in creating the recordset:

...
...
Set rst = MyDB.OpenRecordset("yourQueryNameHere", dbOpenForwardOnly)
...
...
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:49
Joined
Sep 21, 2011
Messages
14,045
Why would you base the recordset on anything else, other than the query you want to run?
 

D0165

New member
Local time
Today, 18:49
Joined
Jun 1, 2022
Messages
9
Hi!

Thank you for your quick reply!

So I tried that, the query name is query1234
And changed it to this:
Set rst = MyDB.OpenRecordset("query1234", dbOpenForwardOnly)

But that does not work.
Then I get error 3061 "error 3061 too few parameters expected 1"
 

D0165

New member
Local time
Today, 18:49
Joined
Jun 1, 2022
Messages
9
So it looks like a problem with the Query itself in combination with the vba script.
When I fill in "test" as a criteria, it works with the button.
But I use [test] so that I will get a popup first, and based on what I fill in, the query will run that criteria.

How can I make the vba/button work with the popup of the query?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:49
Joined
May 7, 2009
Messages
19,169
what is "test"? string, date, number.
you change it in the code:
Code:
 Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strEMail As String
    Dim strSubject As String
    Dim strBody As String
    Dim stDocName As String
 
    'arnelgp
    Dim sTest As String
    Dim MyQD As DAO.QueryDef
    'change the prompt here
    sTest = InputBox("Input anything")
 
    Set MyDB = CurrentDb
    Set MyQD = MyDB.QueryDefs("query1234")
    With MyQD
        .Parameters(0) = sTest
        Set rst = .OpenRecordset(dbOpenForwardOnly)
    End With
    Set MyQD = Nothing
    With rst
      Do While Not .EOF
        strEMail = ![Emailadres] & ";"
        strSubject = "Subject Title Mail"
        strBody = "Test1 " & ![LastName] & "," & vbCrLf & _
        "" & vbCrLf & _
"Text2" & vbCrLf & _
"" & vbCrLf & _
"Text3" & vbCrLf & _
"" & vbCrLf & _
"Text4," & vbCrLf & _
"" & vbCrLf & _
"Text5"

          DoCmd.SendObject , , , strEMail, , , strSubject, strBody
          .MoveNext
      Loop
    End With
 
    rst.Close
    Set rst = Nothing
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:49
Joined
Feb 19, 2002
Messages
42,971
The reason your code didn't work initially is because when you run a query that has parameters using DAO or ADO, you must provide the parameter values.

arnelgp changed your code to show you were to populate the parameters. He used an input box(). I would refer to a field on the form. If there are multiple parameters, just use a separate .parameter statement for each. You can refer to the parameters by name or by ordinal position. The parameters are a zero-based array so the first one is (0) and the second is (1), etc.
 

D0165

New member
Local time
Today, 18:49
Joined
Jun 1, 2022
Messages
9
what is "test"? string, date, number.
you change it in the code:
Code:
Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim strEMail As String
    Dim strSubject As String
    Dim strBody As String
    Dim stDocName As String

    'arnelgp
    Dim sTest As String
    Dim MyQD As DAO.QueryDef
    'change the prompt here
    sTest = InputBox("Input anything")

    Set MyDB = CurrentDb
    Set MyQD = MyDB.QueryDefs("query1234")
    With MyQD
        .Parameters(0) = sTest
        Set rst = .OpenRecordset(dbOpenForwardOnly)
    End With
    Set MyQD = Nothing
    With rst
      Do While Not .EOF
        strEMail = ![Emailadres] & ";"
        strSubject = "Subject Title Mail"
        strBody = "Test1 " & ![LastName] & "," & vbCrLf & _
        "" & vbCrLf & _
"Text2" & vbCrLf & _
"" & vbCrLf & _
"Text3" & vbCrLf & _
"" & vbCrLf & _
"Text4," & vbCrLf & _
"" & vbCrLf & _
"Text5"

          DoCmd.SendObject , , , strEMail, , , strSubject, strBody
          .MoveNext
      Loop
    End With

    rst.Close
    Set rst = Nothing
you are a lifesaver! This works!!

Test was a name for example, this is a string.

Would it be possible, to have a second inputbox after the first is filled in, that you can fill in for example; the date 01-06-2022 and that it passes this date in all the emails in the body?
 

D0165

New member
Local time
Today, 18:49
Joined
Jun 1, 2022
Messages
9
The reason your code didn't work initially is because when you run a query that has parameters using DAO or ADO, you must provide the parameter values.

arnelgp changed your code to show you were to populate the parameters. He used an input box(). I would refer to a field on the form. If there are multiple parameters, just use a separate .parameter statement for each. You can refer to the parameters by name or by ordinal position. The parameters are a zero-based array so the first one is (0) and the second is (1), etc.
Hi! What would be the benefit of this? Is referring to a field a better option? Looks like for now the inputbox is working the way I need it to be. Or do you see any downsides from using the inputbox?

Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:49
Joined
Feb 19, 2002
Messages
42,971
The downside of an input box is you don't get to validate the data before using it or even ensure it is present. It is also annoying to the user. And finally, unless it is an actual parameter prompt, you probably already have the field on your form. For example, opening a report for the current record from a form or opening a detail form from a list form. Even if the field is not bound to the current form, you may want to use a combo to let the user select a text value such as person's name or company name rather than have to enter the ID of the record they need to access.
 

D0165

New member
Local time
Today, 18:49
Joined
Jun 1, 2022
Messages
9
Hi, thanks for the quick reply!

I want the user to choose at that time, what the parameter needs to be, so i think inputbox is the best way?
So today user chooses "firstname1", tomorrow it could be "firstname2" or "firstname3"
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:49
Joined
Sep 21, 2011
Messages
14,045
So use your input box to set a tempvar and use that in your query?
 

D0165

New member
Local time
Today, 18:49
Joined
Jun 1, 2022
Messages
9
Yep, just did that indeed! Could have thought of that myself haha... Thank you !!

One last question, Lets say i want to use the inputbox(or multiple inputboxes) so that I can filter in the query.
The query can be: Between 1 and 10 for Example. So I want to choose the between numbers/text.
So the Query is something like "between 1 and 10" so that I get all records between 1 and 10.
How can i make it with two inputboxes, that the first inputbox defines the start of the between query and the second inputbox defines the end of the query?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:49
Joined
Sep 21, 2011
Messages
14,045
Now I would start using a form.
However if you insist on inputbox, just add another in the code.
 

D0165

New member
Local time
Today, 18:49
Joined
Jun 1, 2022
Messages
9
So the only thing I cannot figure out, is how to pass the Between X and Y in the parameter.

I tried this, but does not seem to be working:

Code:
Spopup = "Between " + InputBox("Start Number") + " And " + InputBox("End Number")

    Set MyDB = CurrentDb
    Set MyQD = MyDB.QueryDefs("query1234")
    With MyQD
        .Parameters(0) = Spopup
        Set rst = .OpenRecordset(dbOpenForwardOnly)

Is it possible with what I want?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:49
Joined
Sep 21, 2011
Messages
14,045
I would just get each input and make each a parameter in the query.
We normally use & to concatenate.

Start learning to use Debug.Print to see what you actually have, not what you think you have
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:49
Joined
May 7, 2009
Messages
19,169
how is your SQL look like?
does the Criteria look like this:

..Where [FieldName] Between [Start Number] And [End Number]

if this is the case:
Code:
Dim sStart as String, sEnd As String
sStart = InputBox("Start Number")
sEnd = InputBox("End Number")

    Set MyDB = CurrentDb
    Set MyQD = MyDB.QueryDefs("query1234")
    With MyQD
        .Parameters(0) = sStart
        .Paramters(1) = sEnd
        Set rst = .OpenRecordset(dbOpenForwardOnly)
 

D0165

New member
Local time
Today, 18:49
Joined
Jun 1, 2022
Messages
9
Yes, that is what the SQL looks like.

You are very perfect! It works.

Thank you again!
 

Users who are viewing this thread

Top Bottom