Restricting emails

[corkster]

I am using Access 2003 to send queries via email, by clicking on an
action button within a form with the following code. My problem is
that the queries that I am sending sometimes will not have any results.


How do I code the action button/form to restrict the emails to only
queries that have results?


Dim cc As String
Dim cc1 As String
Dim mb As String
cc = "FA Assigned Open Projects"
cc1 = "FA Assigned Open Projects v1"
mb = "FA Assigned Open Projects v2"
DoCmd.SendObject acSendQuery, cc, acFormatXLS, (e-mail address removed), , ,
"Project Assigned", "There is a new project for you", False
DoCmd.SendObject acSendQuery, cc1, acFormatXLS,
"(e-mail address removed)", , , "Project Assigned", "There is a new project
for you", False
'DoCmd.SendObject acSendQuery, mb, acFormatXLS,
"(e-mail address removed)", , , "Project Assigned", "There is a new project
for you", False

My dilemma is some of the queries have null results. How do I restrict
the emails to only those that have results?

 

[Dirk Goldgar]

I am using Access 2003 to send queries via email, by clicking on an
action button within a form with the following code. My problem is
that the queries that I am sending sometimes will not have any
results.


How do I code the action button/form to restrict the emails to only
queries that have results?


Dim cc As String
Dim cc1 As String
Dim mb As String
cc = "FA Assigned Open Projects"
cc1 = "FA Assigned Open Projects v1"
mb = "FA Assigned Open Projects v2"
DoCmd.SendObject acSendQuery, cc, acFormatXLS, (e-mail address removed), , ,
"Project Assigned", "There is a new project for you", False
DoCmd.SendObject acSendQuery, cc1, acFormatXLS,
"(e-mail address removed)", , , "Project Assigned", "There is a new project
for you", False
'DoCmd.SendObject acSendQuery, mb, acFormatXLS,
"(e-mail address removed)", , , "Project Assigned", "There is a new project
for you", False

My dilemma is some of the queries have null results. How do I
restrict the emails to only those that have results?

Probably your best bet is to use the DCount function first to see if the
query will have any records. For example:

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

DoCmd.SendObject acSendQuery, cc, _
acFormatXLS, _
"(e-mail address removed)", , , _
"Project Assigned", _
"There is a new project for you", _
False

End If

 

[corkster]

That does work for 1 of them, thanks fo that it was very helpful.

Now I assume I need to use do loop to capture all of them. However, I
am not versed on loops. Can you provide me with that or away to
encompass all of them without a loop?

Thanks so much for your help

 

[Dirk Goldgar]

That does work for 1 of them, thanks fo that it was very helpful.

Now I assume I need to use do loop to capture all of them. However,
I am not versed on loops. Can you provide me with that or away to
encompass all of them without a loop?

Thanks so much for your help

I'm not sure what you mean. Could you explain in more detail? Your
example listed three hard-coded sets of query names and e-mail
addresses. If that's all you have to deal with, and each is independent
of the others, just hard-coding to handle one at a time seems simplest
to me. But you could store the query names and e-mail addresses in an
array and use a loop to process them, if you want -- or they could be in
a table and you could loop through a recordset to process them, if
that's more appropriate.

 

[corkster]

Sorry I wasn't clear in my message.

With hardcoding that you refer to how would I do the dcount multiple
times.
Dim cc As String
Dim cc1 As String

IE the following doesn't work. I also tried elseif
If DCount("*", cc1) > 0 Then DoCmd.SendObject acSendQuery, cc1,
acFormatXLS, "(e-mail address removed)", , , "Project Assigned", "There is a
new project for you", False
If DCount("*", cc) > 0 Then DoCmd.SendObject acSendQuery, cc,
acFormatXLS, "(e-mail address removed)", , , "Project Assigned", "There is a
new project for you", False
end if
Could you tell me specifically how to code this?
Sorry, I haven't used access in awhile
thanks for your help.

 

[corkster]

I code the following code and the email sends only to the first docmd,
in this case cc1 email address gets query results. The other emails
don't go to the person mb or cc that would also have query results.
This is why I thought a loop, but I could be way off in my coding. Any
help on this is greatly appreciated.

cc = "FA Assigned Open Projects"
cc1 = "FA Assigned Open Projects v1"
mb = "FA Assigned Open Projects v2"


If DCount("*", cc1) > 0 Then
DoCmd.SendObject acSendQuery, cc1, acFormatXLS,
"(e-mail address removed)", , , "Project Assigned", "There is a new
project for you", False
ElseIf DCount("*", cc) > 0 Then
DoCmd.SendObject acSendQuery, cc, acFormatXLS, "(e-mail address removed)",
, , "Project Assigned", "There is a new project for you", False
ElseIf DCount("*", mb) > 0 Then
DoCmd.SendObject acSendQuery, mb, acFormatXLS, "(e-mail address removed)",
, , "Project Assigned", "There is a new project for you", False


End If

Thanks for your continued help.

 

[Dirk Goldgar]

I code the following code and the email sends only to the first docmd,
in this case cc1 email address gets query results. The other emails
don't go to the person mb or cc that would also have query results.
This is why I thought a loop, but I could be way off in my coding.
Any help on this is greatly appreciated.

cc = "FA Assigned Open Projects"
cc1 = "FA Assigned Open Projects v1"
mb = "FA Assigned Open Projects v2"


If DCount("*", cc1) > 0 Then
DoCmd.SendObject acSendQuery, cc1, acFormatXLS,
"(e-mail address removed)", , , "Project Assigned", "There is a new
project for you", False
ElseIf DCount("*", cc) > 0 Then
DoCmd.SendObject acSendQuery, cc, acFormatXLS, "(e-mail address removed)",
, , "Project Assigned", "There is a new project for you", False
ElseIf DCount("*", mb) > 0 Then
DoCmd.SendObject acSendQuery, mb, acFormatXLS, "(e-mail address removed)",
, , "Project Assigned", "There is a new project for you", False


End If

Your use of ElseIf is ensuring that only one of the queries will ever be
sent. I think you want to test each one separately, like this:

'----- start of code -----
cc = "FA Assigned Open Projects"
cc1 = "FA Assigned Open Projects v1"
mb = "FA Assigned Open Projects v2"


If DCount("*", cc1) > 0 Then
DoCmd.SendObject acSendQuery, _
cc1, _
acFormatXLS,
"(e-mail address removed)", _
, , _
"Project Assigned", _
"There is a new project for you", _
False
End If

If DCount("*", cc) > 0 Then
DoCmd.SendObject acSendQuery, _
cc, _
acFormatXLS, _
"(e-mail address removed)", _
, , _
"Project Assigned", _
"There is a new project for you", _
False
End If

If DCount("*", mb) > 0 Then
DoCmd.SendObject acSendQuery, _
mb, _
acFormatXLS, _
"(e-mail address removed)", _
, , _
"Project Assigned", _
"There is a new project for you", _
False
End If

'----- end of code -----

Thanks for your continued help.

You're welcome.

 

[corkster]

Dirk thanks so much. IT WORKS . This makes more sense now. I
appreciate your efforts.