Code not working, any suggestions?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following throws a report into the body of an OUTLOOK 2003 email and it
worked the other day, however though data exists for some the code stops with
a debug error on the OutputTo line.

When I remove the "OnNoData" Cancel=True event on the report the code runs
generating the email.

The code stalls on the first director (who happens to not have any data for
the specified day), when the "OnNoData" Event has Cancel=True.

I suspect I am missing an error handler somewhere or something to that
effect but have no clue at this point.

Private Sub Command25_Click()

Dim wdApp As Word.Application
Dim doc As Word.Document
Dim Report As MailItem
Dim Directors As ADODB.Recordset


Set Directors = New ADODB.Recordset

Directors.Open "tblDirectorsList", CurrentProject.Connection, adOpenStatic

Do Until Directors.EOF

[Forms]![frm_PIReporting].[txtDirector].Value = Directors![Name]
[Forms]![frm_PIReporting].[txtEmail].Value = Directors![E_Mail]
DoCmd.OutputTo acOutputReport, "rpt_FalloutByDate", acFormatRTF,
"S:\EmailReports\Fallouts.rtf"

Set wdApp = New Word.Application
Set doc = wdApp.Documents.Open("S:\EmailReports\Fallouts.rtf")
Set Report = doc.MailEnvelope.Item

Report.To = [Forms]![frm_PIReporting].[txtEmail]
Report.Subject = "Audit Failures"

Report.Save
strID = Report.EntryID
Set itm = Nothing
Set OL = CreateObject("Outlook.Application")
Set ns = OL.GetNamespace("MAPI")
Set theitem = ns.GetItemFromID(strID)
If Not theitem Is Nothing Then
theitem.Forward.Display
theitem.Delete
End If

wdApp.Quit False
Set doc = Nothing
Set wdApp = Nothing


On Error Resume Next
Directors.MoveNext
Loop

End Sub

Could some offer a solution please?

Thanks
 
I would strongly recommend that you put error handling into the routine so
that you can see whether an error's being raised.

You might also consider using DCount to determine whether there is any data
for the director before you call the report. In fact, you could join
tblDirectorsList to the data, so that your Directors recordset only returns
those directors for whom there is data.

Without knowing more about the tables involved, I can't offer you specific
advice on how to create the recordset, but assuming you've got a Fallouts
table with the director's name on it, it would be something like:

SELECT DISTINCT Director.Name, Director.E_Mail
FROM tblDirectorsList INNER JOIN Fallouts
ON Fallouts.Name = tblDirectorsList.Name
 
Thanks Doug, I added an

"On Error Resume Next" line just before the OutputTo statement and the
routine ran without a problem.

I must have deleted it by mistake while I was getting the routine put
together.

Thanks
--
Jeff C
Live Well .. Be Happy In All You Do


Douglas J. Steele said:
I would strongly recommend that you put error handling into the routine so
that you can see whether an error's being raised.

You might also consider using DCount to determine whether there is any data
for the director before you call the report. In fact, you could join
tblDirectorsList to the data, so that your Directors recordset only returns
those directors for whom there is data.

Without knowing more about the tables involved, I can't offer you specific
advice on how to create the recordset, but assuming you've got a Fallouts
table with the director's name on it, it would be something like:

SELECT DISTINCT Director.Name, Director.E_Mail
FROM tblDirectorsList INNER JOIN Fallouts
ON Fallouts.Name = tblDirectorsList.Name

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff C said:
The following throws a report into the body of an OUTLOOK 2003 email and
it
worked the other day, however though data exists for some the code stops
with
a debug error on the OutputTo line.

When I remove the "OnNoData" Cancel=True event on the report the code runs
generating the email.

The code stalls on the first director (who happens to not have any data
for
the specified day), when the "OnNoData" Event has Cancel=True.

I suspect I am missing an error handler somewhere or something to that
effect but have no clue at this point.

Private Sub Command25_Click()

Dim wdApp As Word.Application
Dim doc As Word.Document
Dim Report As MailItem
Dim Directors As ADODB.Recordset


Set Directors = New ADODB.Recordset

Directors.Open "tblDirectorsList", CurrentProject.Connection, adOpenStatic

Do Until Directors.EOF

[Forms]![frm_PIReporting].[txtDirector].Value = Directors![Name]
[Forms]![frm_PIReporting].[txtEmail].Value = Directors![E_Mail]
DoCmd.OutputTo acOutputReport, "rpt_FalloutByDate", acFormatRTF,
"S:\EmailReports\Fallouts.rtf"

Set wdApp = New Word.Application
Set doc = wdApp.Documents.Open("S:\EmailReports\Fallouts.rtf")
Set Report = doc.MailEnvelope.Item

Report.To = [Forms]![frm_PIReporting].[txtEmail]
Report.Subject = "Audit Failures"

Report.Save
strID = Report.EntryID
Set itm = Nothing
Set OL = CreateObject("Outlook.Application")
Set ns = OL.GetNamespace("MAPI")
Set theitem = ns.GetItemFromID(strID)
If Not theitem Is Nothing Then
theitem.Forward.Display
theitem.Delete
End If

wdApp.Quit False
Set doc = Nothing
Set wdApp = Nothing


On Error Resume Next
Directors.MoveNext
Loop

End Sub

Could some offer a solution please?

Thanks
Click to expand...
Click to expand...
 
"On Error Resume Next" is NOT error handling: it's error avoidance!

Resume Next means that you just ignore the errors, and try to carry on as
though nothing's wrong. Sometimes that's okay, but other times, when things
need to work and don't, you not only won't know what the error was, but you
may not even know that an error occurred!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff C said:
Thanks Doug, I added an

"On Error Resume Next" line just before the OutputTo statement and the
routine ran without a problem.

I must have deleted it by mistake while I was getting the routine put
together.

Thanks
--
Jeff C
Live Well .. Be Happy In All You Do


Douglas J. Steele said:
I would strongly recommend that you put error handling into the routine
so
that you can see whether an error's being raised.

You might also consider using DCount to determine whether there is any
data
for the director before you call the report. In fact, you could join
tblDirectorsList to the data, so that your Directors recordset only
returns
those directors for whom there is data.

Without knowing more about the tables involved, I can't offer you
specific
advice on how to create the recordset, but assuming you've got a Fallouts
table with the director's name on it, it would be something like:

SELECT DISTINCT Director.Name, Director.E_Mail
FROM tblDirectorsList INNER JOIN Fallouts
ON Fallouts.Name = tblDirectorsList.Name

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff C said:
The following throws a report into the body of an OUTLOOK 2003 email
and
it
worked the other day, however though data exists for some the code
stops
with
a debug error on the OutputTo line.

When I remove the "OnNoData" Cancel=True event on the report the code
runs
generating the email.

The code stalls on the first director (who happens to not have any data
for
the specified day), when the "OnNoData" Event has Cancel=True.

I suspect I am missing an error handler somewhere or something to that
effect but have no clue at this point.

Private Sub Command25_Click()

Dim wdApp As Word.Application
Dim doc As Word.Document
Dim Report As MailItem
Dim Directors As ADODB.Recordset


Set Directors = New ADODB.Recordset

Directors.Open "tblDirectorsList", CurrentProject.Connection,
adOpenStatic

Do Until Directors.EOF

[Forms]![frm_PIReporting].[txtDirector].Value = Directors![Name]
[Forms]![frm_PIReporting].[txtEmail].Value = Directors![E_Mail]
DoCmd.OutputTo acOutputReport, "rpt_FalloutByDate", acFormatRTF,
"S:\EmailReports\Fallouts.rtf"

Set wdApp = New Word.Application
Set doc = wdApp.Documents.Open("S:\EmailReports\Fallouts.rtf")
Set Report = doc.MailEnvelope.Item

Report.To = [Forms]![frm_PIReporting].[txtEmail]
Report.Subject = "Audit Failures"

Report.Save
strID = Report.EntryID
Set itm = Nothing
Set OL = CreateObject("Outlook.Application")
Set ns = OL.GetNamespace("MAPI")
Set theitem = ns.GetItemFromID(strID)
If Not theitem Is Nothing Then
theitem.Forward.Display
theitem.Delete
End If

wdApp.Quit False
Set doc = Nothing
Set wdApp = Nothing


On Error Resume Next
Directors.MoveNext
Loop

End Sub

Could some offer a solution please?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Then I am somewhat back to where I started, the reports generated are correct
and the error is occuring with the OnNoData set to cancel=True and I am
clueless about error handling
 
Back
Top