Compare numbers in an Access query and send an email based on match

htamraz1
htamraz1 used Ask the Experts™
on
I have a Microsoft Access query that pulls client data. It has three columns
- Client
- Sent
- Received

A typical query output may look like this:

Client          Sent          Received
A                 245           245
B                 7651         6874
C                 500           500
D                 144           56        
E                  968           725

I would like to create a report that runs on a timer - every 1 hour and checks for each client in the query if number 'Sent' = number 'Received' (for example, A & C above). If there is a match, send an email to a recepient's email address. When emailing, it would be good if the report could include a copy of the query output as Excel attachment.

Any thoughts on this?

Can you help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer
Commented:
to send the email:

DoCmd.SendObject acSendReport, "rptReportName", acFormatRTF, "NameToReceive@abc.com", "NameToReceiveCC@abc.com", "NameToReceiveBCC@abc.com", "email match list", True

rptReportName is your report name
acFormatRTF is the format of the report
email match list is the subject of the email
True means wait to add some additional notes. If false, it just sends the email without pause.

more later.

Mike

htamraz1Director of Technology

Author

Commented:
Thanks Mike. I am familiar with SendObject. I will interested in your feedback on the query piece.

HT

Commented:
As an Excel attachment??  This is the Access zone.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
The query is easy:

SELECT Client, Sent, Received from myTable WHERE Sent = Received;

use your table name for myTable.  Every client listed has a match.  No clients no Email.
Mike EghtebasDatabase and Application Developer
Commented:
rptReportName is a report based on the query you have above.

Public intTry As Integer
Private Sub From_Open()
   intTry = 0
End If

Private Sub From_Timer()   'interval = 90000

If Time > Me!TimeToEmail and Time <Date("n", 15, Me!TimeToEmail) and intTry < 1 Then
    Call SendEmails
    intTry = 10
End If

intTry = intTry  -1
End Sub

Sub SendEmails()
Dim rs As DOA.RecordSet
Set rs=CurrentDB.OpenRecordset("YourQueryAbove")
if rs.recordcount>0 then
if rs!Sent =rs!Received then
     DoCmd.SendObject acSendReport, "rptReportName",  acFormatRTF, fnClientEmail(rs!Client), , ,"email match list",
end if
end if
rs.close
End If

Write a function to return client email:

Function fnClientEmail(strClient)As Stirng
fnClientEmail=strClient & "Company.com" ' for example
End Function

Me!TimeToEmail is a text box where you enter the time to send email.

Mike
Mike EghtebasDatabase and Application Developer
Commented:
Add a new table (as your form's recordsource) to binde Me!TimeToEmail.

The code fires once every five minutes.
Mike EghtebasDatabase and Application Developer
Commented:
correction...

Sub SendEmails()
Dim rs As DOA.RecordSet
Set rs=CurrentDB.OpenRecordset("YourQueryAbove")
if rs.recordcount>0 then
rs.movefirst
do until rs.eof
   if rs!Sent =rs!Received then
     DoCmd.SendObject acSendReport, "rptReportName",  acFormatRTF, fnClientEmail(rs!Client), , ,"email match list",
   end if
rs.movenext
loop
end if
rs.close
End Sub

sorry.
htamraz1Director of Technology

Author

Commented:
I don't understand how it fires every 5 minutes based on the settings you have. Can you explain please? If you can comment your code, that would be best.

Also, what is your perspective on the query as stated in my question.

Thanks
HT

Commented:
DoCmd.OutputTo acOutputQuery, "NameofQuery", acFormatXLS, "C:\PathToQuery\Filename.xls"

NewMessage.Attachments.Add "C:\PathToQuery\Filename.xls", olByValue
Database and Application Developer
Commented:
re:>how it fires every 5 minutes

'interval = 90000

90000 /1000 =90 seconds

90/60 = 1.5 minutes (sorry it fires every minute and half).
===============
re:> Can you explain please? If you can comment your code, that would be best.

I coppied from my working version I have. I am at work and cannot spend much time on this. But, you can copy and paste and test it (step thru to find its logic).

Mike
htamraz1Director of Technology

Author

Commented:
Thanks. We'll get back to you.
Mike EghtebasDatabase and Application Developer
Commented:
re:> perspective on the query as stated in my question.

if rs!Sent =rs!Received then

compares as it cycle through you records. You need just use:

Set rs=CurrentDB.OpenRecordset("YourQueryAbove")

Mike
Mike EghtebasDatabase and Application Developer

Commented:
Thanks to you mbizup.
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
You're welcome :-)

(and thanks to htamraz1 for following up on this)
Mike EghtebasDatabase and Application Developer

Commented:
htamraz1,

My apology; and thank you for the folloup.  I was thinking you have abbandoned this question.

Once again to mbizup correcting/ helping to see me.

Regards to all,

Mike
Mike EghtebasDatabase and Application Developer

Commented:
correction... now that I have my first cup of coffee.

htamraz1,

My apology; and thank you for the follow up.  I was thinking you have abandoned this question.

Once again thanks to mbizup for correcting/ helping me to see you have awarded the points.

Regards to all,

Mike

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