Selecting email from a table (1 Viewer)

murray83

Games Collector
Local time
Today, 11:03
Joined
Mar 31, 2017
Messages
728
im sure this has been asked before, i have done a search here but cant find anything like it

just need a pointer in what im doing wrong, as using teh following code

Code:
'email script
Public Sub CreateEmailWithOutlook()

'bits for the attachment
Dim myPath As String
Dim strReportName As String
todayDate = Format(Date, "DDMMYY")

    myPath = "G:\GENERAL\MTM\km\database workings\PCC Compliance DB\FailureReport\"
    strReportName = "Failure Report.pdf"
    

' Create a new email object
    Set olApp = CreateObject("Outlook.Application")
    Set olMailItem = olApp.createitem(olMailItem)

    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = DLookup("[email]", "[tblSupplierDetails]", "SupplierNumber = Reports![Qry_FailureReport]![SupplierCode]")

im having issues with the .To section

it keeps telling me, as you can see in the attached that the report name 'Qry_FailureReport' is wrong but its not
 

Attachments

  • 1.jpg
    1.jpg
    95.3 KB · Views: 58
  • 2.jpg
    2.jpg
    63.1 KB · Views: 48

isladogs

MVP / VIP
Local time
Today, 11:03
Joined
Jan 14, 2017
Messages
18,186
Always test issues like this with a Debug line
You need to add delimiters e.g. for text field

Code:
DLookup("[email]", "[tblSupplierDetails]", "SupplierNumber = ' " & Reports![Qry_FailureReport]![SupplierCode] & " ' ")

I've added spaces between the quotes to make it easier to read
It might be better to use a variable for the filter criteria instead
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,354
Use a string variable to set this up before getting into the Outlook object. As Colin stated it will help with debugging as well.

For some reason once you are "inside" the olMailObject it seems to not like references to Access objects.

Code:
Dim sEmailTo as String

sEmailTo = DLookup("[email]", "[tblSupplierDetails]", "SupplierNumber = Reports![Qry_FailureReport]![SupplierCode]")
Debug.print sEmailTo
...
With olMailItem
     .To = sEmailTo

I'm also not entirely sure you can refer to report object in that way?
 

murray83

Games Collector
Local time
Today, 11:03
Joined
Mar 31, 2017
Messages
728
so if i was to move the generation of the string before i got to the inside of the email it may work

as i put on the button you press to start it which asks for supplier code and the day for the report so it pulls correct info
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,354
so if i was to move the generation of the string before i got to the inside of the email it may work

as i put on the button you press to start it which asks for supplier code and the day for the report so it pulls correct info

Why not have the supplier code in a drop down on the form where the button is and a date picker for the report date, then simply use those form values in both you report and your email routine ?

Less chance of someone putting in the wrong supplier code and a duff date? And more appealing to the end user than a parameter prompt box.
 

murray83

Games Collector
Local time
Today, 11:03
Joined
Mar 31, 2017
Messages
728
Why not have the supplier code in a drop down on the form where the button is and a date picker for the report date, then simply use those form values in both you report and your email routine ?

Less chance of someone putting in the wrong supplier code and a duff date? And more appealing to the end user than a parameter prompt box.

that is an idea, but then i would have to put every supplier into the table, which wouldn't be hard but not every supplier may have an issue and also would need to update the table in time for new suppliers etc

where as i think the option of being asked for which supplier is easier as that has less to maintain no need to update for new suppliers
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,354
? Shirley your suppliers are already in a table ?

I'll assume they are, and that if there is a report to generate, then you could build the combo to only show those suppliers that meet the criteria for that report. It's like a self referencing restriction on what you make available for display.
 

murray83

Games Collector
Local time
Today, 11:03
Joined
Mar 31, 2017
Messages
728
? Shirley your suppliers are already in a table ?

I'll assume they are, and that if there is a report to generate, then you could build the combo to only show those suppliers that meet the criteria for that report. It's like a self referencing restriction on what you make available for display.

no not yet its a new stream for the company
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,354
Okay. But presumably all the suppliers that could be in the reports can be queried?
That gives you your list.
 

murray83

Games Collector
Local time
Today, 11:03
Joined
Mar 31, 2017
Messages
728
how about if on the button press i ran another query which looked up the address and then the email used that table/query for the address
 

Minty

AWF VIP
Local time
Today, 11:03
Joined
Jul 26, 2013
Messages
10,354
Actually just pull the email address into the combo I suggested as a hidden column and pull the value directly from that. No need to mess with another query.

Make the combo do the hard work.
 

Users who are viewing this thread

Top Bottom