SendObject error

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

Guest

I am having problems with the following piece of code. It has been written to run a report from details contained on a dialogue form and then issue the report by email as an RTF attachment. This has been working fine for some time, but, when I now run it, it creates an Excel file instead containing nothing but code

Does anyone know how this problem might have been caused and what I can do to correct it

Dim ReportName, WhereCondition As String,
StandardCondition As String, SubSectCondition As String,
DateCondition As String, BodyMessage As String,
CaptionMessage As String, StandardCaption As String,
AreaCaption As String, DateCaption As String,
Dt As New DateInf

Public Sub Form_Load(
ResetFor
End Su

Public Sub ResetForm(
'Normalise form settings and populate with dates of previous wee
Me.TabCtl7.Visible = Fals
Me.InsideHeight = 240
Me.tglAddCriteria.Caption = "Set additional criteria >>>
Me.cbxAllDates = Fals
Me.StartDate = Dt.FirstOfWeek -
Me.EndDate = Dt.LastOfWeek -
Me.StartDate.Visible = Tru
Me.EndDate.Visible = Tru
ReportName = "rptDiscontinuedCatalogue
StandardCondition = "SC<99 AND St='D'
DateCondition = " AND DiscDate Between #"
& Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #"
& Format$(Me.EndDate, "mm/dd/yyyy") & "#
StandardCaption = "Preparing discontinued report for
AreaCaption = "all areas,
DateCaption = "dates as specified
BodyMessage = "Karen," & Chr(10) & Chr(10)
& "Please find attached the latest discontinued product update.
End Su

Private Sub cmdWeeklyReport_Click(
'Mail discontinued report using weekly criteri
Dim Msg, Style, Title, Respons
Msg = "Are you sure you want to mail the weekly report?
Style = vbYesNo + vbExclamation + vbApplicationModa
Title = "Mail Weekly Discontinued Report
Response = MsgBox(Msg, Style, Title
If Response = vbNo The
Exit Su
End I
ResetFor
If Val(Me.cbxAllDates) = -1 The
WhereCondition = "Department<>'94'" &
"AND SC<99 AND St='D'
Els
WhereCondition = "Department<>'94'"
& "AND SC<99 AND St='D' AND DiscDate Between #"
& Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #"
& Format$(Me.EndDate, "mm/dd/yyyy") & "#
End I
Me.Caption = "Preparing report (electrical excluded)...
DoCmd.OpenReport
ReportName:=ReportName,
View:=acViewPreview,
WhereCondition:=WhereConditio
Me.Caption = "Discontinued Report

DoCmd.SendObject acSendReport, , acFormatRTF,
"Karen Roberts",
"Debbie Thompson", , "Discontinued Product Update for Week ",
BodyMessage, Tru
End Sub
 
Hi Basil

Thanks for your response

I was advised that if I wanted to apply criteria to a report (outside of a standard query as in this case) and then mail it on as an attachment, I would first need to specify and run the report with the OpenReport command, and then use the SendObject command whilst omitting reference to the object. Believe it or not, this had actually been working fine for quite some time

Jedste

----- Basil wrote: ----

Hi Jedster

Good to see you've come some way in this..

The first thing I noticed was that there doesn't seem to be any report referenced to be attached. The code I am talking about is

DoCmd.SendObject acSendReport, , acFormatRTF,
"Karen Roberts",
"Debbie Thompson", , "Discontinued Product Update for Week ",
BodyMessage, Tru

Wouldn't you want to include the report name in this? i.e.

DoCmd.SendObject acSendReport, REPORTNAME , acFormatRTF,
"Karen Roberts",
"Debbie Thompson", , "Discontinued Product Update for Week ",
BodyMessage, Tru

I have no idea what was going on with Excel et al. I would have thought that your code would have left an open email (with the relevant recipients and body text) with no attachements

Ba

----- Jedster wrote: ----

I am having problems with the following piece of code. It has been written to run a report from details contained on a dialogue form and then issue the report by email as an RTF attachment. This has been working fine for some time, but, when I now run it, it creates an Excel file instead containing nothing but code

Does anyone know how this problem might have been caused and what I can do to correct it

Dim ReportName, WhereCondition As String,
StandardCondition As String, SubSectCondition As String,
DateCondition As String, BodyMessage As String,
CaptionMessage As String, StandardCaption As String,
AreaCaption As String, DateCaption As String,
Dt As New DateInf

Public Sub Form_Load(
ResetFor
End Su

Public Sub ResetForm(
'Normalise form settings and populate with dates of previous wee
Me.TabCtl7.Visible = Fals
Me.InsideHeight = 240
Me.tglAddCriteria.Caption = "Set additional criteria >>>
Me.cbxAllDates = Fals
Me.StartDate = Dt.FirstOfWeek -
Me.EndDate = Dt.LastOfWeek -
Me.StartDate.Visible = Tru
Me.EndDate.Visible = Tru
ReportName = "rptDiscontinuedCatalogue
StandardCondition = "SC<99 AND St='D'
DateCondition = " AND DiscDate Between #"
& Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #"
& Format$(Me.EndDate, "mm/dd/yyyy") & "#
StandardCaption = "Preparing discontinued report for
AreaCaption = "all areas,
DateCaption = "dates as specified
BodyMessage = "Karen," & Chr(10) & Chr(10)
& "Please find attached the latest discontinued product update.
End Su

Private Sub cmdWeeklyReport_Click(
'Mail discontinued report using weekly criteri
Dim Msg, Style, Title, Respons
Msg = "Are you sure you want to mail the weekly report?
Style = vbYesNo + vbExclamation + vbApplicationModa
Title = "Mail Weekly Discontinued Report
Response = MsgBox(Msg, Style, Title
If Response = vbNo The
Exit Su
End I
ResetFor
If Val(Me.cbxAllDates) = -1 The
WhereCondition = "Department<>'94'" &
"AND SC<99 AND St='D'
Els
WhereCondition = "Department<>'94'"
& "AND SC<99 AND St='D' AND DiscDate Between #" _
& Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" _
& Format$(Me.EndDate, "mm/dd/yyyy") & "#"
End If
Me.Caption = "Preparing report (electrical excluded)..."
DoCmd.OpenReport _
ReportName:=ReportName, _
View:=acViewPreview, _
WhereCondition:=WhereCondition
Me.Caption = "Discontinued Report"

DoCmd.SendObject acSendReport, , acFormatRTF, _
"Karen Roberts", _
"Debbie Thompson", , "Discontinued Product Update for Week ", _
BodyMessage, True
End Sub
 
Basil

Is there any chance that I could see an example of your work so that I might be able to find a way around this problem? I think that might help a lot

Also, would it be the case that you decided to use this method so that you could also tailor the report to the user's needs

Cheers

The Jedste

----- Basil wrote: ----

I'm sorry I couldn't help more... I'll leave your queries to the MVPs

To me it seems quite a complex method - I do something very very similar simply by setting the report's controls, record source - even the layout of the whole report on the load event (some calcultaions and variables are stored on the form's control event). In this way you only need the docmd.sendobject command and the rest takes care of itself. Good luck Jedster

Ba

----- Jedster wrote: ----

Hi Basil

Thanks for your response

I was advised that if I wanted to apply criteria to a report (outside of a standard query as in this case) and then mail it on as an attachment, I would first need to specify and run the report with the OpenReport command, and then use the SendObject command whilst omitting reference to the object. Believe it or not, this had actually been working fine for quite some time

Jedste

----- Basil wrote: ----

Hi Jedster

Good to see you've come some way in this..

The first thing I noticed was that there doesn't seem to be any report referenced to be attached. The code I am talking about is

DoCmd.SendObject acSendReport, , acFormatRTF,
"Karen Roberts",
"Debbie Thompson", , "Discontinued Product Update for Week ",
BodyMessage, Tru

Wouldn't you want to include the report name in this? i.e.

DoCmd.SendObject acSendReport, REPORTNAME , acFormatRTF,
"Karen Roberts",
"Debbie Thompson", , "Discontinued Product Update for Week ",
BodyMessage, Tru

I have no idea what was going on with Excel et al. I would have thought that your code would have left an open email (with the relevant recipients and body text) with no attachements

Ba

----- Jedster wrote: ----

I am having problems with the following piece of code. It has been written to run a report from details contained on a dialogue form and then issue the report by email as an RTF attachment. This has been working fine for some time, but, when I now run it, it creates an Excel file instead containing nothing but code

Does anyone know how this problem might have been caused and what I can do to correct it

Dim ReportName, WhereCondition As String,
StandardCondition As String, SubSectCondition As String,
DateCondition As String, BodyMessage As String,
CaptionMessage As String, StandardCaption As String,
AreaCaption As String, DateCaption As String,
Dt As New DateInf

Public Sub Form_Load(
ResetFor
End Su

Public Sub ResetForm(
'Normalise form settings and populate with dates of previous wee
Me.TabCtl7.Visible = Fals
Me.InsideHeight = 240
Me.tglAddCriteria.Caption = "Set additional criteria >>>
Me.cbxAllDates = Fals
Me.StartDate = Dt.FirstOfWeek -
Me.EndDate = Dt.LastOfWeek - 7
Me.StartDate.Visible = True
Me.EndDate.Visible = True
ReportName = "rptDiscontinuedCatalogue"
StandardCondition = "SC<99 AND St='D'"
DateCondition = " AND DiscDate Between #" _
& Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" _
& Format$(Me.EndDate, "mm/dd/yyyy") & "#"
StandardCaption = "Preparing discontinued report for "
AreaCaption = "all areas, "
DateCaption = "dates as specified"
BodyMessage = "Karen," & Chr(10) & Chr(10) _
& "Please find attached the latest discontinued product update."
End Sub

Private Sub cmdWeeklyReport_Click()
'Mail discontinued report using weekly criteria
Dim Msg, Style, Title, Response
Msg = "Are you sure you want to mail the weekly report?"
Style = vbYesNo + vbExclamation + vbApplicationModal
Title = "Mail Weekly Discontinued Report"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Exit Sub
End If
ResetForm
If Val(Me.cbxAllDates) = -1 Then
WhereCondition = "Department<>'94'" & _
"AND SC<99 AND St='D'"
Else
WhereCondition = "Department<>'94'" _
& "AND SC<99 AND St='D' AND DiscDate Between #" _
& Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" _
& Format$(Me.EndDate, "mm/dd/yyyy") & "#"
End If
Me.Caption = "Preparing report (electrical excluded)..."
DoCmd.OpenReport _
ReportName:=ReportName, _
View:=acViewPreview, _
WhereCondition:=WhereCondition
Me.Caption = "Discontinued Report"

DoCmd.SendObject acSendReport, , acFormatRTF, _
"Karen Roberts", _
"Debbie Thompson", , "Discontinued Product Update for Week ", _
BodyMessage, True
End Sub
 
Back
Top