Pulling email address from drop down

[headtrip]

Hello everybody! I'm new to Access and new to these forums, so be gentle. I've searched the forums the best I can and can't seem to find a resolution to my problem... or at least what I'm doing wrong. Please help me learn Access. Ok, so my question. I am trying to pull an email address from my table called "Employees". The table itself has the columns "EmployeeID" (an autonumber for each employee in the db) "LastName" "FirstName" "Title" and "Email" inside of it. In my form "DraftRequestForm" I have a drop down box (named "ProductSpecialist") which pulls all Employees with the Title of "Product Specialist" and places it in the drop down box, combining their "FirstName" field and "LastName" field into one name using a Row Source value of:

SELECT [FirstName] & " " & [LastName] AS Name FROM Employees WHERE (((Employees.Title)="Product Specialist")) ORDER BY Employees.LastName, Employees.FirstName;

Now, my problem is that once the person fills out the form, they need the form emailed to whatever employee is selected in the "ProductSpecialist" drop down box. I'm using the following code below for when the button is clicked:

Private Sub EmailButton_Click()   

Dim FieldValue As Variant
Dim psEmailTo As String

FieldValue = Forms!DraftRequestForm!ProductSpecialist
 
psEmailTo = DLookup("[Email]", "Employees", "[EmployeeID]=" & FieldValue)


DoCmd.SendObject , , , psEmailTo, , , "test subject", "test email body", True

End Sub

The email window pops up just fine in Outlook, but no matter how I format the DLookup, I can not get it to pull the Email address and place it in the To: field. What am I missing here? Remember, I'm brand new to Access so it could be something really simple. Any help would be greatly appreciated. Thanks!

 

[georgedwilkinson]

Maybe your Dlookup isn't getting what you think it is. Try putting a debug statement after the DLookup. Something like this:

Debug.Print "Email is " & psEmailTo

or this:

MsgBox ("Email is " & psEmailTo)

 

[headtrip]

Looks like it's pulling nothing :/ Thanks for the quick response!

 

[pbaldy]

Your DLookup is looking for EmployeeID, but that isn't a field in the combo's row source. It would make more sense to include the email address in the combo row source (it can be hidden) and get it like:

psEmailTo = Forms!DraftRequestForm!ProductSpecialist.Column(x)

where x is the column containing the address (the column property is zero based).

 

[headtrip]

Thank you pbaldy! This solved my problem, did not know you could do that. Thanks again!

 

[pbaldy]

No problemo, and welcome to the site!