Email report using GroupWise

[pixie]

Hi. I have an Access DB that is for contracts. I have used the code found on Tony's site for emailing a report per person containing only their information using GroupWise. It works great but I have a problem. Right now, the code before the GroupWise code, generates reports based on time criteria. If the query has no records for a person then a report isn't created. Trouble is, the code for GroupWise email sends the auto email message even though there isn't an attachment. How can I tell it to not send the auto email message to a recipient if there isn't a report as an attachment? Thanks in advance. I've been trying to find an answer all morning!

[NeoPa]

To be honest, without having the code to reference it's going to be quite hard to know which bit(s) of it needs to be changed. Most things can be done in various ways in Access (Can be a blessing but can also be a chore) so there's really no way to guess.
Try posting the relevant section of the code (or all of it if it's not too voluminous).

[MMcCarthy]

Hi. I have an Access DB that is for contracts. I have used the code found on Tony's site for emailing a report per person containing only their information using GroupWise. It works great but I have a problem. Right now, the code before the GroupWise code, generates reports based on time criteria. If the query has no records for a person then a report isn't created. Trouble is, the code for GroupWise email sends the auto email message even though there isn't an attachment. How can I tell it to not send the auto email message to a recipient if there isn't a report as an attachment? Thanks in advance. I've been trying to find an answer all morning!

Post the piece of code that runs the report and calls the groupwise function. It's a matter of deciding to ignore the latter line of code if the report has no records. Once we see the code it will give a better idea of how that can be done.

Mary

[pixie]

Here is the code for one person. Before this code runs, the DB creates a report, per person, if there is data. Looking forward to your help! I just can't figure it out!

Sub RunLRiouxEMail()
 
On Error GoTo Err_Handler
Dim strTemp As String
Dim varAttach(2) As Variant
Dim strRecTo(1, 0) As String
Dim strRecCc(1, 0) As String
Dim lngCount As Long
Dim varProxies As Variant
Dim cGW As GW
 
 
varAttach(0) = "C:\Documents and Settings\reo\My Documents\ContractReports\ReportsRioux.rtf"
 
strRecTo(0, 0) = "[e-mail]"
strRecTo(1, 0) = "[e-mail]"
 
strRecCc(0, 0) = "[e-mail]"
 
Set cGW = New GW
With cGW
  .Login
  .BodyText = "Attached please find a word document that lists: " & _
              "contract reports that are over due and contract reports " & _
              "that are due within one to three weeks.  " & vbCrLf & vbCrLf & _
              "Please ensure that the reports are completed and sent " & _
              "to the contract officer on time.  A copy of the dated cover " & _
              "letter transmitting the report and the report itself should " & _
              "be sent to the Accounting Specialist in the Finance Office.  " & _
              "The Accounting Specialist will update the Contracts database " & _
              "with the date the report was submitted and file the report " & _
              "in the contract file for the compliance audits." & vbCrLf & vbCrLf & _
              "If no document is attached to this automated e-mail then " & _
              "you have no past due reports and no reports due within " & _
              "the next three weeks." & vbCrLf & vbCrLf & _
              "For questions please contact the Accounting Specialist " & _
              "at 874-1140 ext 356. Thank you."
  .Subject = "Contract Reports"
  .RecTo = strRecTo
  .RecCc = strRecCc
  .FileAttachments = varAttach
  .FromText = "Finance Department"
  .Priority = "High"
  strTemp = .CreateMessage
  .ResolveRecipients strTemp
  If IsArray(.NonResolved) Then MsgBox "Some unresolved recipients."
  .SendMessage strTemp
  .DeleteMessage strTemp, True
End With
 
 
 
Exit_Here:
  Set cGW = Nothing
  Exit Sub
 
Err_Handler:
  MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
  Resume Exit_Here
 
End Sub

[pixie]

Here is the code that creates a report for one person.

 DoCmd.OpenQuery "qryTimeToMailNoel", acNormal, acEdit
        If DCount("*", "qryTimeToMailNoel") = 0 Then
    DoCmd.Close acQuery, "qryTimeToMailNoel"
        Else
    DoCmd.OpenReport "rptReportsNoel", acPreview, "", ""
    DoCmd.Close acReport, "rptReportsNoel"
    DoCmd.Close acQuery, "qryTimeToMailNoel"
    DoCmd.OutputTo acReport, "rptReportsNoel", _
                   "RichTextFormat(*.rtf)", _                   "g:\accounting\development\ReportsNoel.rtf", _
                   False, ""
    DoCmd.OutputTo acReport, "rptReportsNoel", _                   "RichTextFormat(*.rtf)", _
                   "C:\Documents and Settings\reo\My Documents\ContractReports\ReportsNoel.rtf", _
                   False, ""
        End If

[MMcCarthy]

Here is the code that creates a report for one person.

 DoCmd.OpenQuery "qryTimeToMailNoel", acNormal, acEdit
        If DCount("*", "qryTimeToMailNoel") = 0 Then
    DoCmd.Close acQuery, "qryTimeToMailNoel"
        Else
    DoCmd.OpenReport "rptReportsNoel", acPreview, "", ""
    DoCmd.Close acReport, "rptReportsNoel"
    DoCmd.Close acQuery, "qryTimeToMailNoel"
    DoCmd.OutputTo acReport, "rptReportsNoel", _
                   "RichTextFormat(*.rtf)", _                   "g:\accounting\development\ReportsNoel.rtf", _
                   False, ""
    DoCmd.OutputTo acReport, "rptReportsNoel", _                   "RichTextFormat(*.rtf)", _
                   "C:\Documents and Settings\reo\My Documents\ContractReports\ReportsNoel.rtf", _
                   False, ""
        End If

Where is the line of code that actually calls the mailout function?

[pixie]

The one above is run by a macro which has another RunCode to run the following function

Function ROconnerMail()
Call RunROconnerEMail
End Function

This calls the following:

Sub RunROconnerEMail()
 
On Error GoTo Err_Handler
Dim strTemp As String
Dim varAttach(2) As Variant
Dim strRecTo(1, 0) As String
Dim strRecCc(1, 0) As String
Dim lngCount As Long
Dim varProxies As Variant
Dim cGW As GW
 
 
varAttach(0) = "C:\Documents and Settings\reo\My Documents\" & _
               "ContractReports\ReportsOConnor.rtf"
 
strRecTo(0, 0) = "[e-mail]"
strRecTo(1, 0) = "[e-mail]"
 
strRecCc(0, 0) = "[e-mail]"
 
Set cGW = New GW
With cGW
  .Login
  .BodyText = "Attached please find a word document that lists: " & _
              "contract reports that are over due and contract reports " & _
              "that are due within one to three weeks.  " & vbCrLf & vbCrLf & _
              "Please ensure that the reports are completed and sent " & _
              "to the contract officer on time.  A copy of the dated " & _
              "cover letter transmitting the report and the report " & _
              "itself should be sent to the Accounting Specialist in " & _
              "the Finance Office.  The Accounting Specialist will " & _
              "update the Contracts database with the date the report " & _
              "was submitted and file the report in the contract file " & _
              "for the compliance audits." & vbCrLf & vbCrLf & _
              "If no document is attached to this automated e-mail " & _
              "then you have no past due reports and no reports due " & _
              "within the next three weeks." & vbCrLf & vbCrLf & _
              "For questions please contact the Accounting Specialist " & _
              "at 874-1140 ext 356. Thank you."
  .Subject = "Contract Reports"
  .RecTo = strRecTo
  .RecCc = strRecCc
  .FileAttachments = varAttach
  .FromText = "Finance Department"
  .Priority = "High"
  strTemp = .CreateMessage
  .ResolveRecipients strTemp
  If IsArray(.NonResolved) Then MsgBox "Some unresolved recipients."
  .SendMessage strTemp
  .DeleteMessage strTemp, True
End With
 
 
 
Exit_Here:
  Set cGW = Nothing
Exit Sub

I appreciate the time you are spending as this is getting really frustrating; anything I try doesn't work.

[NeoPa]

Pixie,
If you post code without the tags (as it instructs you to do in the Reply window) then anyone trying to help you will find it difficult to understand what you're doing or what your code is doing. I've fixed up your earlier posts for you but the underlying code is not laid out for reading. If I fix this last one up it's just going to hide most of the info as some of the lines are so long (.BodyText=...) . If you want the help then please at least don't make things harder for those who try to give it. I will go through your last post now trying to make it readable, but please, in future, include the tags and lay the code out in a way that is easily read.

MODERATOR.

[pixie]

I'm sorry. I will review the guidelines and comply in the future.

[NeoPa]

That was a quick response :)
I don't mean to sound heavy, but it can cause a lot of delays sometimes.

I actually enjoyed going through your code though. It's a shame I don't have that level of experience with the objects that drive MS Outlook - but that's another story...