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...

[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.

[pixie]

I have the first bit of code in a Module called DontMailIfNoAttachment. I added the second code you sent to the Module called basOBowman. (I am testing this out on Bowman not OConnor as Bowman's mail goes to me till she is replaced.) So here is what it looks like but when I go to run, I cant find it to run it.

Option Compare Database
 
Option Explicit
'Line added
Private Const conFile As String = _
                  "G:\Accounting\Development\ReportsBowman.rtf"
 
Function OBowmanMail()
'Line changed
  If Exist(conFile) Then Call RunOBowmanEMail(strFile:=conFile)
End Function
 
Sub RunOBowmanEMail(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 intRes As Integer
Dim strMsg As String
Dim cancel As Boolean
 
Dim cGW As GW
'Line chance
 varAttach(0) = strFile
 
 
strRecTo(0, 0) = "[e-mail]"
strRecTo(1, 0) = "[e-mail]"
 
strRecCc(0, 0) = "[e-mail]"

[NeoPa]

Will have to look at this tomorrow. Sorry - I'm out tonight.
Expect rational response then ;)

[pixie]

Enjoy the night and thanks again for all the help thus far. Looking forward to hearing your response.

[NeoPa]

I've looked at this a little more closely and I think I may have an answer for you.
Only think, because your answer seems to be almost contradictory. You say the code is in two separate modules yet you post them both in the same code window.
If they are simply in three separate modules (as implied) then putting the word Public before each procedure (Sub or Function) definition should make them visible to other modules in the same Project.
Let us know if this fixes your problem. If not, try to explain where your code is located a little more clearly. Hopefully it will be fine with this though. Good luck.

[NeoPa]

Enjoy the night and thanks again for all the help thus far. Looking forward to hearing your response.

BTW I had a fine night thank you.
I was a bouncer for my niece's 18th birthday party. It was quite funny watching the behaviour of all the young things. There was no worry though (18th birthdays are notorious for trouble usually) as two of my nephews also do karate and were there inside in case of any trouble.

[pixie]

Thanks for the reponse. I have to work on three other projects today but will try to find some free time today to get back to you as I so want to have this one wrapped up. Glad you had a nice evening the other night.

[NeoPa]

No hurry.
When you can, let us know if the Public fix worked for you.

[pixie]

Hi, I'm taking a break from my other projects and just entered the Public before all three modules. I can't find the module in my list to run it so I must be doing something dreadfully wrong. Thanks in advance. May I ask if you are in the States?

Here they are:

The one you sent me:

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
 
Public Const ConFile As String = _
    "G:\Accounting\Development\reportsBowman.rtf"

Then this one:

Option Compare Database
Public Const ConFile As String = "G:\Accounting\Development\reportsBowman.rtf"
 
 
Public Function BowmanMail()
If Exist(ConFile) Then Call RunOBowmanEMail(strFile:=ConFile)
End Function

Then the last one:

Public Sub RunOBowmanEMail(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 intRes As Integer
Dim strMsg As String
Dim cancel As Boolean
 
Dim cGW As GW
 
 
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 ECT."
  .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

[NeoPa]

That is the idea with the Public keyword.
Does it fix the problem for you?
In answer to your question, No I live in South London (My profile will give all my publicly accessible details).

[pixie]

I'm not sure if it does solve my problem. When I am in VBA and go to Run on the Menu bar | Run Sub/UserForm, it shows all the modules except those three that I posted here. I must be doing something dreadfully wrong. Oh, and thanks for pointing out your profile.

[NeoPa]

That's not a very good test Pixie. That will only list Public Subroutines (not functions) which have no parameters to pass. None of your procedures matches these criteria.
Try tracing through the code. When it gets to a line which calls one of these procedures, it will either work, or complain that it can't find the procedure referenced.
Profiles are easy to reach. Just click on the name at the top of a post and it will link you through to their profile.

[pixie]

I'm sorry but that is the only test I knew. (Guess you can tell my level of skill, eh? Sorry, self taught. ) I don't know what it means to trace code but would gladly do it if I knew how. I tried stepping threw it but that didn't seem to do what you wanted.

[NeoPa]

'Tracing code' is another way of saying 'Stepping through it'. You don't want to do it in isolation though. By which I mean you don't want to start executing that code out of the normal flow of the code.
What triggers the call to this routine in the first place?
Put your breakpoint on the line :

If Exist(ConFile) Then Call RunOBowmanEMail(strFile:=ConFile)

then see what happens with the code when it tries to call the various procedures.

Also, from post #28, lose the line :

Public Const ConFile As String = _
    "G:\Accounting\Development\reportsBowman.rtf"

...after the Exist() function is defined. This is done again in the module containing the definition of the BowmanMail() function. It should not be defined twice.

[pixie]

The end user gets a message when she opens the database saying reports need to be emailed, if that is true. (If it isn't, no message). She then clicks on the email reports command button which runs a macro that generates, if needed, the reports and then runs the funtions which call the sub to generate the email.

When I did what you told me to do (wow, how neat! thanks for the lesson on how to use that menu option), it takes me here

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

Then back here

Public Function BowmanMail()
If Exist(ConFile) Then Call RunOBowmanEMail(strFile:=ConFile)
End Function

Does that mean it works?

[NeoPa]

I cannot say that it works as I haven't seen the results.
Certainly, if the code progress were a problem, I would expect an error message to come up and the code to show a line highlighted in Yellow.

It certainly doesn't seem to have the problem that it can't see the procedures any more.

After your comments about how to debug, I may see if I can put a short Tutorial together explaining how to use the debugging tools in the VBA editor environment (When I get some time).

[NeoPa]

When you say :

Then back here

Public Function BowmanMail()
If Exist(ConFile) Then Call RunOBowmanEMail(strFile:=ConFile)
End Function

Does that mean it works?

Which bit was yellow after it got back there after the Exist() function?

[pixie]

When I need a break on another project I am working on, I will click the email button and see what happens (but only after I change all the email addy's to mine as I don't want folks getting reports until I get this fixed). I will let you know what happens. Thank you ever so much for all the help to date. If it works, you will be my hero :)

[NeoPa]

That's fine, I'm not short of other work to be getting along with ;)
Whenever you're ready. I'm sure we can get you sorted eventually.

[pixie]

thank you. i hope to be able to get to it either late today or tomorrow. enjoy the day.

[pixie]

Thank you very much. :) I tried it out and it seems to be working just fine. I really really really (did I say really?) appreciate all the help.

[pixie]

Oppps, I spoke too soon. It doesn't send an email if there is no report but if there is a report, it doesn't send it now. Hmmmm. Any ideas there?

[pixie]

Oh my! I think its been too long of a day. It works fine now. Hopefully my brain will get some needed rest tonight. :)

[NeoPa]

I know where you're coming from Pixie :D
Glad you're sorted (You really are aren't you?) J/K.

[pixie]

Good morning. All seems to be working fine. I've moved from a test environment and am now entering the code in "the real thing." Once again, thanks for all your help and your patience. Is there a place for me to rate you? I think you are just the greatest :)

[NeoPa]

Just spam MMcCarthy with PMs singing my praises!
J/K she's an Admin on here and gets hundreds of PMs every week. It's probably true to say she's not looking for more!
You could PM a moderator (Oooh that wouldn't work - that's me). Ah well - I know, and I'm very pleased that you're happy. Come back again any time :)

[MMcCarthy]

Just spam MMcCarthy with PMs singing my praises!
J/K she's an Admin on here and gets hundreds of PMs every week. It's probably true to say she's not looking for more!
You could PM a moderator (Oooh that wouldn't work - that's me). Ah well - I know, and I'm very pleased that you're happy. Come back again any time :)

I'm pleased you're pleased.

Next time I catch you suggesting I get MORE PM's you won't sit down for a week. :D

Glad it's working for you Pixie. We know how good he is but we keep it quiet as we don't want him to get too big-headed.

Mary

[NeoPa]

Glad it's working for you Pixie. We know how good he is but we keep it quiet as we don't want him to get too big-headed.

Mary

Lucky you're there to stop that Mary ;)