Emailing report 1

[RCorrigan]

I have a report that I can preview and print

Private Sub cmdPreview_Click()
On Error GoTo Err_cmdPreview_Click

    Dim strDocName As String
    Dim strWhere As String
    
    strDocName = "Report - RPC v1"
    strWhere = "[Requested Date] = #" & Format(Me.cboStartDate.Value, "yyyy-mm-dd") & "#"
    DoCmd.OpenReport strDocName, acPreview, , strWhere
        
    
Exit_cmdPreview_Click:
    Exit Sub

Err_cmdPreview_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreview_Click
    
End Sub

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

    Dim strDocName As String
    Dim strWhere As String
    
    strDocName = "Report - RPC v1"
    strWhere = "[Requested Date] >= #" & Format(Me.cboStartDate.Value, "yyyy-mm-dd") & "# "
    DoCmd.OpenReport strDocName, acNormal, , strWhere
    
Exit_cmdPrint_Click:
    Exit Sub

Err_cmdPrint_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint_Click
    
End Sub

And (in another form) send an email

Private Sub cmdEmail_Click()

    Dim Email As String
    Dim ref As String
    Dim header As String
    Dim body As String
         
    Dim objOutlook As Outlook.Application
    Dim objEMail As Outlook.MailItem
            
    Email = Me!cboEmailTo
    ref = Me!cboStartDate
    header = "FWL - User Access Request Report"
    body = Me!txtComments
        
    Set objOutlook = CreateObject("outlook.application")
    Set objEMail = objOutlook.CreateItem(olMailItem)
           
    
    With objEMail
        .To = Email
        .Subject = header & " " & ref
        .body = body
        .Save
        .Send
        
    End With
    
    
    
End Sub

What I would like to do is attach the report to an email.

Do I have to use docmd.OutputTo and then attach it ? And if so how ?? or can I attach it some other way?

MTIA


<Do I need A Signature or will an X do?>

 

[Remou]

Yes, you can save your report to disk in a suitable format and then use:

[tt] With objEMail
.To = Email
.Subject = header & " " & ref
.Attachments.Add "c:\a.txt"
.Attachments.Add "c:\abc.txt" 'and so on
.body = body
.Save
.Send

End With[/tt]

You can even include the report in the body of the email, with a little code.

 

[webturner]

Use DoCmd.SendObject

SendObject ObjectType , ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile

 

[RCorrigan]

Am confused about the docmd.OutputTo ...... am trying to get the report as detailed in the first bit of code to email from the same form as I can preview / print it ...

Also what's the difference (pros/Cons) of docmd.SendObject over with ObjEMail ??

MTIA

<Do I need A Signature or will an X do?>

 

[Remou]

One of the advantages of SendObject is that it will use whatever email program is available. One disadvantage is you cannot have a pretty email. One of the advantages of using Outlook is that you can have a pretty email, another is that you can have several attachments. A disadvantage is that the user must have Outlook installed.

It is likely that you will need to get rid of the Where statement in the code ([tt]strWhere = "[Requested Date] = #" & Format(Me.cboStartDate.Value, "yyyy-mm-dd") & "#"[/tt]) and put in the query that the report is based on, instead.

 

[RCorrigan]

Remou Not sure I understand ..... the code at the top for the cmd_click() with the where statement is the "query" that I use !!!!!

<Do I need A Signature or will an X do?>

 

[Remou]

You need OutputTo to send the report as an attachment with Outlook, as per your post, or SendObject, as per webturner. With both SendObject and OutputTo you cannot have a Where statement, so you will need to alter the report so that it uses a query as its record source. If you do this, you can reference the form on the criteria line of the query and both OutputTo and SendObject will work.

 

[RCorrigan]

[whoosing sound as that goes straight over my head]

How's that work then?

[Wanders off to look up query, access & Paracetamol on Google]

<Do I need A Signature or will an X do?>

 

[Remou]

Click on the name of the table you want to base this report on; choose Insert->Query from the menu; choose Simple Query Wizard from the pop-up box, press Ok. On the next screen, choose the fields that you want, be sure to include Requested Date; press Next. This is the last screen, choose 'Modify the query's design' option and press Finish. This will open the Query Design window and, if you intend to use Access, your life will revolve around this window. Locate the column headed Requested Date and paste:
[tt]Format(Forms![Name of Form].cboStartDate, "yyyy-mm-dd")[/tt]
On the row marked Criteria. Choose View->Datasheet View, and if all is well, save the query. You can now base your report on this query, or use the SQL in an existing report.

 

[RCorrigan]

Remou

Thanks for that .... have got so far and when I goto Datasheet view I get an error telling me that the expression is typed incorrectly or is too complex to evaluate ...... what I have got in my criteria box is

 Format([forms]![FWL - Access Report by Date - RPC v1].[cbostartdate],"yyyy-mm-dd")

Is the form name an issue ???

<Do I need A Signature or will an X do?>

 

[RCorrigan]

BTW, if I get that criteria working, how would you apply it to an existing report ?

<Do I need A Signature or will an X do?>

 

[Remou]

I don't think so. Was the form open when you tried?

 

[Remou]

If this query is set up with the proper fields needed for the report, you can simply cut and paste the SQL (SQL view from the View menu) into the Record Source of the report, backing it up first, of course. Then again, you can see how the whole thing works and from that decide you would prefer to modify the Record Source, using the three little dots (...) to the right of Record Source on the property sheet for the report. Finally, you may decide that the report is simple enough to use the Report Wizard to build a new report based on this query. The decision is yours.