Hello all, I have a little VB code that sends a generic message after a new record is added:
Private Sub Form_AfterInsert()
DoCmd.SendObject _
, _
, _
, _
"email address", _
, _
, _
"Subject", _
"Text", _
False
End Sub
How would one go about looking up an email address from a contact that is on sub form.
So for example, I give Dave a task to do in a task (entry in form creates a new task in tblTask), I finalise the task (after insert) and Dave is sent an email.
Dave is but one employee amongst several whose email address are located in tblEmployee.
Apologies for being dense, any help is appreciated.
You’ll have to lookup the email address in event code.
Presuming there’s a field in the tasks table that contains the employee name (DAVE), use either a recordset base query or a dlookup function to get the email address from the employee table.
A custom function might be useful
Function GetEmployeeEmail(EmployeeName as string) as string
GetEmployeeEmail=dlookup("[emailaddressfield]","tblEmployees","[employeename]=’" & EmployeeName & "’")
end function