How do I assign a query result (an email address) to a variable?

Question

I am trying to assign a query result (an email address) to a variable.

I want that variable to be assigned to a button to open the current used mailing system and open a new mail with that email address.

Public valid_mail As Variant
    
Public Sub mail_list()
    valid_mail = "SELECT CustomerFACT.îééì FROM CustomerFACT WHERE (((CustomerFACT.[èìôåï ðééã]) Like '*' & Forms!CustomerLookUpFORM!searchbox & '*')) Or (((CustomerFACT.[ùí àéù ÷ùø]) Like '*' & Forms!CustomerLookUpFORM!searchbox & '*')) Or (((CustomerFACT.[ùí äì÷åç]) Like '*' & Forms!CustomerLookUpFORM!searchbox & '*')) Or (((CustomerFACT.[îñôø òåñ÷]) Like '*' & Forms!CustomerLookUpFORM!searchbox & '*'));"
End Sub
    
Private Sub mail_70_Click()
    DoCmd.SendObject acSendNoObject, , , valid_mail
End Sub

When I am not using the global variable valid_mail, but entering an email address in double quotes, it works.

Answer 1

This is what I tested in my example. You need to substitute the results of the recordset.

Public valid_mail As Variant

Sub test()
    Dim DB As Database
    Dim rs As Recordset
    Dim strSQL As String
    
    Set DB = CurrentDb
    
    strSQL = "select email from [table] "
    
    Set rs = DB.OpenRecordset(strSQL)
    
    valid_mail = rs!email
    
    MsgBox valid_mail
End Sub

Substituting for yours would look like this:

Public valid_mail As Variant
Sub  mail_list()
    Dim DB As Database
    Dim rs As Recordset
    Dim strSQL As String
    
    Set DB = CurrentDb
    
    strSQL = "SELECT CustomerFACT.ieei FROM CustomerFACT WHERE (((CustomerFACT.[eioai ðeea]) Like '*' & Forms!CustomerLookUpFORM!searchbox & '*')) Or (((CustomerFACT.[ui aeu ÷uø]) Like '*' & Forms!CustomerLookUpFORM!searchbox & '*')) Or (((CustomerFACT.[ui ai÷ac]) Like '*' & Forms!CustomerLookUpFORM!searchbox & '*')) Or (((CustomerFACT.[inoø oan÷]) Like '*' & Forms!CustomerLookUpFORM!searchbox & '*'));"
    
    Set rs = DB.OpenRecordset(strSQL)
    
    valid_mail = rs!ieei
    
    ''MsgBox valid_mail
End Sub

Private Sub mail_70_Click()

call mail_list

DoCmd.SendObject acSendNoObject, , , valid_mail
End Sub

and Your sql statement is expressed incorrectly. It should be like below.

strSQL = "SELECT CustomerFACT.ieei FROM CustomerFACT WHERE (((CustomerFACT.[eioai ðeea]) Like '*" & Forms!CustomerLookUpFORM!searchbox & "*')) Or (((CustomerFACT.[ui aeu ÷uø]) Like '*" & Forms!CustomerLookUpFORM!searchbox & "'*)) Or (((CustomerFACT.[ui ai÷ac]) Like '*" & Forms!CustomerLookUpFORM!searchbox & "*')) Or (((CustomerFACT.[inoø oan÷]) Like '*" & Forms!CustomerLookUpFORM!searchbox & "*'));"

my anwer also mistyped.

Like '*" & Forms!CustomerLookUpFORM!searchbox & "'*))

to

Like '*" & Forms!CustomerLookUpFORM!searchbox & "*'))

crrected sql is

strSQL = "SELECT CustomerFACT.ieei FROM CustomerFACT WHERE (((CustomerFACT.[eioai ðeea]) Like '*" & Forms!CustomerLookUpFORM!searchbox & "*')) Or (((CustomerFACT.[ui aeu ÷uø]) Like '*" & Forms!CustomerLookUpFORM!searchbox & "*')) Or (((CustomerFACT.[ui ai÷ac]) Like '*" & Forms!CustomerLookUpFORM!searchbox & "*')) Or (((CustomerFACT.[inoø oan÷]) Like '*" & Forms!CustomerLookUpFORM!searchbox & "*'));"