Converted Macro Help

[joshandsony]

Below is a piece of my macro that I converted to VB. Can someone tell me what I can add to this information to make the macro "skip" the email if the query that I am trying to send is blank?

'------------------------------------------------------------
' CONFIRMED_EMAIL_MACRO
'
'------------------------------------------------------------
Function CONFIRMED_EMAIL_MACRO()
On Error GoTo CONFIRMED_EMAIL_MACRO_Err
DoCmd.OpenQuery "Confirmed Email Query", acViewNormal, acEdit
DoCmd.ApplyFilter "", "[Confirmed Email Query]![Vendor Name]=""A B Chance CO"""
' AB CHANCE
DoCmd.SendObject acQuery, "Confirmed Email Query", "Excel97-Excel2003Workbook(*.xls)", "Email", "", "", "A B Chance PO Information Required", "", True, ""
DoCmd.ShowAllRecords
DoCmd.ApplyFilter "", "[Confirmed Email Query]![Vendor Name]=""ACI COMMUNICATIONS INC"""
' ACI COMMUNICATIONS
DoCmd.SendObject acQuery, "Confirmed Email Query", "Excel97-Excel2003Workbook(*.xls)", "Email", "", "", "ACI PO Information Required", "", True, ""
DoCmd.ShowAllRecords
DoCmd.ApplyFilter "", "[Confirmed Email Query]![Vendor Name]=""A C P INTERNATIONAL"""
' ACP International
DoCmd.SendObject acQuery, "Confirmed Email Query", "Excel97-Excel2003Workbook(*.xls)", "Email", "", "", "ACP International PO Information Required", "", True, ""
DoCmd.ShowAllRecords

Thank you.

 

[pbaldy]

Try

If DCount("*", "QueryName") > 0 Then

 

[joshandsony]

I tried the string above, but it is not skipping the email function of the command. I need to skip those if there are no records within the query.

 

[pbaldy]

What exactly did you try? That would certainly work, used correctly.

 

[joshandsony]

This is how I wrote it and it brings up the second vendor with a blank query and still goes to the email function.

DoCmd.ApplyFilter "", "[Confirmed Email Query]![Vendor Name]=""A B Chance CO"""
' AB CHANCE
If DCount("*", "[Confirmed Email Query]") > 0 Then
DoCmd.SendObject acQuery, "Confirmed Email Query", "Excel97-Excel2003Workbook(*.xls)", "smyund@hps.hubbell.com", "", "", "A B Chance PO Information Required", "", True, ""
End If

DoCmd.ShowAllRecords
DoCmd.ApplyFilter "", "[Confirmed Email Query]![Vendor Name]=""ACI COMMUNICATIONS INC"""
' ACI COMMUNICATIONS
If DCount("*", "[Confirmed Email Query]") > 0 Then
DoCmd.SendObject acQuery, "Confirmed Email Query", "Excel97-Excel2003Workbook(*.xls)", "ethach@acicomms.com", "", "", "ACI PO Information Required", "", True, ""
End If

 

[pbaldy]

I can't see how that would send the email if the query returned no records. I suspect that the filter is not doing what you think it is. Can you post a sample db?

 

[joshandsony]

Good Morning-

Here is a quick and dirty of what I am trying to do. I need the email macro to skip over part # C because there are no records to send.

 

[pbaldy]

That doesn't have the code in it, just the original macro. As I said, the filter you are applying has no effect on the DCount test or the SendObject. The query itself still returns all records. This is along the lines of what you're trying to do:

http://granite.ab.ca/access/email/reporttomultiplerecipients.htm

To do what you're doing with the query, you could add a criteria to it that points to a form control. Populate that form control with the appropriate vendor, then your test with the DCount would work properly as would the SendObject.