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!

[pixie]

Thanks. I hope you or someone will be able to help me out. I tried doing

If varAttach(0).Count = O Then vbCancel Send End if

byt that didn't work. I am not sure what left to try. Looking forward to a response.

[NeoPa]

The first thing you need to do is to determine what recognisable situation occurs when there are no items in your report. Does it fail to produce a file or does it produce a very small one. When you know that, we can look at ways of determining whether or not that state can be recognised by your code.

[pixie]

If there is no data, a report is not created and thus not saved in the folder. The folder gets cleaned out after each email run.

[NeoPa]

That, we can work with.
It involves using Dir() but I'll have to look into it a bit before answering properly.

[NeoPa]

Our first step is to put the following code in a general Module in your database.

Option Compare Database
Option Explicit
 
'Exist returns true if strFile exists.
'22/05/2003 Rewritten with better code.
'20/05/2005 Added finding of R/O, System & Hidden files
Public Function Exist(strFile As String, _
                      Optional intAttrib As Integer = &H7) As Boolean
    Exist = (Dir(PathName:=strFile, Attributes:=intAttrib) <> "")
End Function

[NeoPa]

Working from the code you posted in post #7...

'Line added
Private Const conFile As String = _
                  "C:\Documents and Settings\reo\My Documents\" & _
                  "ContractReports\ReportsOConnor.rtf"
 
Function ROconnerMail()
'Line changed
  If Exist(conFile) Then Call RunROconnerEMail(strFile:=conFile)
End Function
 
'Line changed
Sub RunROconnerEMail(ByRef strFile As String)
 
  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
 
'Line changed
  varAttach(0) = strFile
 
  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

[pixie]

OK. I have it in the General Module as its own module. What next? Boy, do I really appreciate all your attention and help.

[NeoPa]

I hope you refreshed after posting and saw the other posts ;)

[pixie]

Good morning. I was in a meeting yesterday afternoon (a rare thing for me) so didn't see the rest of your reply till late. I just added it but for some reason I can't run it. Should the first two line changes go in a new module?

[NeoPa]

I'm a bit confused by the last bit (your question).
Where do you have the code currently (Module names and Types pls)? What is the name of the form you're working in?
There is some flexibility here as to where things can go, so if I know what you have where, I can better explain where it would be easiest for you to put the changes.