Trying to send email to a DLookup email address

SteveL13
SteveL13 used Ask the Experts™
on
I have a command button on a form which when clicked is supposed to send an email to a DLookup.  Here is the code:

   With objMail
   'Set body format to HTML
     .BodyFormat = olFormatHTML
     .To = DLookup("[ApproverEmail]", "tblEmployees", "[EmpName] = Form![EmpName]")
     '.Cc = "ccaddress@yourmailaddress.com"
     .Subject = "Time Card Approval Notification For Employee - " & [txtEmployeeName] & " - Dated " & [txtTranxDateHeader]
     '.HTMLBody = "<htmltags>Regular Hours = " & [txtTotRegHrs] & "<br>Overtime Hours = " & [txtTotOThrs] & "<br>Total Hours = " & [txtTotTimeCardHrs] & "<br></htmltags>"
     .HTMLBody = "<htmltags>Regular Hours = " & RegHrs & "<br>Overtime Hours = " & OThrs & "<br>Total Hours = " & totHrs & "<br></htmltags>"
     .send
   End With

The problem appears to be in the line:

 .To = DLookup("[ApproverEmail]", "tblEmployees", "[EmpName] = Form![EmpName]")

becase if I hard code an email address like:

'.To = "name@somecompany.com"

it works fine.

??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
Try this:

.To = DLookup("[ApproverEmail]", "tblEmployees", "[EmpName] = " & chr(34) &  Form![EmpName] & chr(34))

Open in new window


assuming EmpName is TEXT.

In the odd chance that it is numeric:

.To = DLookup("[ApproverEmail]", "tblEmployees", "[EmpName] = " &  Form![EmpName] )

Open in new window

mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also,  depending on where your code is located, you may need to replace this

>>> Form![EmpName]

With something like this:


Forms!YourFormName!EmpName

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial