Calling Table Field using another Field variable

[darkstar257]

I have a mailing list table set up which displays a list of

ID
First Name
Last Name
Email Address


I also created a form that has a drop down menu where you can select from employees by First Name. This drop down selection is then assigned to the variable ContactName.

However, can I use the email automation from Microsoft "...id=318881" to email to the Address selected based off of the First Name selected from the drop down menu?

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = Forms![Complaint Form]![ContactName]

With objOutlookMsg
' Add the TO: recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

......
...

I can easily do an auto email if the ContactName is directly assigned to be an Email address, but that would cause the Complaint Form's drop down menu to display an email address instead of the person's name..which is not what i want to do.

Can I do something along the lines of:

TheAddress = Forms![Complaint Form]![SELECT EmailAddress WHERE ContactName.First Name = ContactName]

???

 

[Trevor G]

Suggestion is with your combo box add a second column for the email address and set the width to 0cm then with your code you can refer to the hidden column from the combo box then you have your solution.

me.combobox.column(1) would be the hidden column as 0 is the first which would be the FirstName.

Hope that helps.

 

[darkstar257]

Suggestion is with your combo box add a second column for the email address and set the width to 0cm then with your code you can refer to the hidden column from the combo box then you have your solution.

me.combobox.column(1) would be the hidden column as 0 is the first which would be the FirstName.

Hope that helps.

That is awesome! thanks mate