Access 2010 - Form A is employee info; Form B is a submit report; Populate ther Email

[Parker]

Hello All,


I have a Table "EmployeeT" and a Form "EmployeeF" that is the input for this table. Each record in the Form is a place for an employee to upload all of their information like "First Name" "Last Name" "Position" "Email address" etc.

I have another Table "NewLabReportT" and a form "NewLabReportF" that is the input for this table. Each record in this form is a place for an employee to upload their most recently completed lab report.

There is an option of Form "NewLabReportF" to select the individual to review this record. The "Reviewer" would be listed in the Table "EmployeeT" and Form "EmployeeF"

If I have not lost you yet...

My goal is when in Form "NewLabReportF" as a user selects their "reviewer" (which the last name is populated from "Employee T") then hits "Submit Record" the email address of the "Reviewer" selected is inserted into my code below:


Private Sub Submit_Record_Click()
DoCmd.SendObject _


, _
, _
, _
"Email @ email. com", _ ''''''''''This is where I want the email address to auto-populate from my Table "EmployeeT"
, _
, _
"***A new Lab Report has been submitted for your review***", _
"Hello," & vbCrLf & vbCrLf & vbCrLf & "Please log into the Lab Report Database and review the latest pending report. If you have any questions please contact the sender." & vbCrLf & vbCrLf & "This is an automated response generated from Microsoft Access." & vbCrLf & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "Lab Report Database", _
False


DoCmd.Close


End Sub

[June7]

Use a multi-column combobox to select the reviewer. One of the columns in combobox can be the email address. Reference that column. Column index begins with 0 so if the email is column 2, the index is 1.

"Email@ email.com", Me.comboboxname.Column(1)

[Parker]

Hello Thank you June7


My problem is what do I write in bewtween the quotation marks in the code where "Email@ email.com" is represented?

[June7]

Ooops, sorry - just replace with the reference to the combobox but don't use quote marks because this is a variable.

[Parker]

could you please type the code I should exactly use? the email address is in column 2 and the combo box "Name" is Combo242

[Parker]

Ooops, sorry - just replace with the reference to the combobox but don't use quote marks because this is a variable.

could you please type the code I should exactly use? the email address is in column 2 and the combo box "Name" is Combo242

[Parker]

could you please type the code I should exactly use? the email address is in column 2 and the combo box "Name" is Combo242

OK So I think I got it to work... but WITH AN ERROR

It auto populates in my Lotus notes but the address in the "TO" line becomes:

Email @ email .com#mailto: Email @ email .com#

AKA it adds on everything I show in red and it sends back a Delivery failure notice: Email @ email .com#mailto: Email @ email .com#

My code is:

, _
Me.Combo242.Column(2), _
, _

[June7]

Column index begins with 0. Second column of the combobox would be 1 in the Column index property.

Me.Combo242.Column(1)

[Parker]

Column 1 is coming up as column 1 in my list

Column 2 which is the email address is truly column 2 in the list as well

I don't think that is where the error lies.

[Parker]

Private Sub Submit_Record_Click()
DoCmd.SendObject _
, _
, _
, _
Me.Combo242.Column(2), _
, _
, _
"***A new Lab Report has been submitted for your review***", _
"Hello," & vbCrLf & vbCrLf & vbCrLf & "Please log into the Lab Report Database and review the latest pending report. If you have any questions please contact the sender." & vbCrLf & vbCrLf & "This is an automated response generated from Microsoft Access." & vbCrLf & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "Lab Report Database", _
False

Again that is the code: and in my Combo242 box Column 1 = Last Name and Column 2 = Email


Even when I enter in Me.Combo242.Column(1) all it adds in the TO line in Lotus Notes is the employees last name. That is what leads me to believe it is (2) not (1). Only problem is I do not know how to eliminate the extra stuff it adds #mailto: Emailaddress#

[June7]

Regardless, index base is 0. If the data is in column 2 of the combobox then the index reference is 1.

Show the combobox RowSource statement.

I use Outlook and this code works for me. I have never used LotusNotes. No idea why it behaves this way.

[Parker]

SELECT [EmployeeT].[Employee_ID], [EmployeeT].[Last_Name], [EmployeeT].[E-mail] FROM EmployeeT ORDER BY [Last_Name];

[June7]

Email is in the 3rd column so index is 2 and your code using 2 is correct. Doesn't matter if column not visible (0 width), it still must be counted.

Still cannot explain the error.

[Parker]

Solved the problem was that in the table the field was set up as a hyperlink!

All i had to do was change it to a "text field" and now it works.