Retrieve field data from other table

Question

I have table#1 which contains customers pending invoices, and table#2 which contains customer master data such as their email addresses.

I'm trying to create a button in a form based on table#1 to send the pending invoice number by email. I couldn't retrieve the customers email addresses from table# 2 by DLookup.

Dim Msg As String
Dim EmailRecTo As String
EmailRecTo = DLookup("CustomerEmails", "CustomersMasterData", "CustomerName=" & Forms!CusPenInv!InvNo)
Dim O As Outlook.Application
Dim M As Outlook.MailItem

Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)
Msg = "Dear Customer," & "<P>", & "You have pending invoice number " & InvNo
    
With M
    .BodyFormat = olFormatHTML
    .HTMLBody = Msg
    .To = EmailRecTo
    .Subject = "Pending Invoices"
    .Display
End With
    
Set M = Nothing
Set O = Nothing

Answer 1

What kind of datatype is [InvNo] field? If it was Text/String datatype then you need to convert it into string. Try this:

EmailRecTo = DLookup("CustomerEmails", "CustomersMasterData", "CustomerName='" & Forms!CusPenInv!InvNo & "'")

or

EmailRecTo = DLookup("CustomerEmails", "CustomersMasterData", "CustomerName=" & CStr(Forms!CusPenInv!InvNo))