0

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
Community
  • 1
  • 1
Faisal
  • 1
  • 1
  • Forms!CusPenInv!InvNo looks like a bad call so try troubleshooting that. Start with some debug.prints and it see if you get null as I expect. See here for how to properly refer to a control on a form: http://access.mvps.org/access/forms/frm0031.htm Be aware it takes me about 5 minutes to get the call right with help from the reference. Also, if CusPenInv is not still open you can't refer to it. There are many ways to skin this cat. First approach is to pass InvNo between forms using OpenArgs during the from open event. I prefer creating Public Get and Set functions in a code module) – mazoula Aug 26 '22 at 04:42

1 Answers1

0

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))
Nouvand
  • 36
  • 3