Send email to customer from Form, where customer's email data is not in Form data source

[Pawtang]

In this form, you can see the Customer control combobox with the control source of cust_nb. That cust_nb is a foreign key in the form's record source that is related to the Customer table, where cust_nb is the primary key. This table contains the customer's email. I would like for the email button next to the combobox to just launch an Outlook message with the customer's email address populated. I can think of ways to do this that involve an intermediary step - launching a form or query that find the related record and then emailing from there - but I'd like to bypass that and just launch directly into the email step from this form.

I imagine best route forward would be constructing a query in VBA on the button control that finds the related email and then launches the Email process with the email as an argument, but I've never written VBA that contains a SQL section. Could someone point me in the direction of how to run a SQL Select statement inside of a VBA sub to get that information to pass as an argument?

[ranman256]

grab the customers email addr from their table via combo box that IS bound to the client table
cbo fields: CLientID, ClientName, ClientEmail

add an unbound box, txtEmail to the form and fill it when user picks the Client from the combo.

NOTE: in vb, columns begin with zero.

sub cboClient_afterupdate()
txtEmail = cboClient.Column(2)  'col 3
end sub

now you can email using the txtbox.
DoCmd.SendObject acSendQuery, "qsQuery", acFormatXLS, txtEmail, , , "Subject", "message"

[Pawtang]

Public Sub btnEmail_Click()
    Dim email_target As String
    email_target = DLookup("email", "tblCustomers", "customerNumber = '" & Forms!frmRMA!cust_nb) & "'"
    'DoCmd.SendObject acSendNoObject,,,email_target,,,"RMA example",,True)
End Sub

I have begun to try to solve using a common method I've found online, DLookup. However I'm getting a Run-time error '3075': "Syntax error in string in query expression 'customerNumber = 'FULSTEA'

Can't figure out why

ranman256, I may try your method if this ultimately fails, but I'd rather keep data processing functions out of the form design and instead contained in the VBA back-end of it.

[Gicu]

Can you try to enclose the field and table names in square brackets? Also you have the parenthesis in the wrong spot:

DLookup("email", "tblCustomers", "customerNumber = '" & Forms!frmRMA!cust_nb & "'")

[Pawtang]

Can you try to enclose the field and table names in square brackets? Also you have the parenthesis in the wrong spot:

DLookup("email", "tblCustomers", "customerNumber = '" & Forms!frmRMA!cust_nb & "'")

Moving parenthesis did the trick. It's always the dumb little things ya don't see when you're caught up in the weeds!

I now have it displaying the related email in a message box so I can test it out with various customers.

Now to get the DoCmd working!