Send email with report as an attachment

**DJRhino1175** · Aug 14 '18, 12:17 PM

Code:

Dim aFile As String
aFile = "N:\Unsecure Share\New ECN System\New ECN Report.pdf"
If Len(Dir$(aFile)) > 0 Then
     Kill aFile

What I used for the Kill, seems to work ok.

The solution to Issue 1 is a little over my head at the moment.

The Form this cmdButton is on will already be filter to the record, so is this the code I would use just before the Docmd.outputTo

Code:

gstrFilter = "ECN# = " & Me.txtECN#

The report doesn't truely execute in the code anywhere.

**twinnyfo** · Aug 14 '18, 12:27 PM

First, some sage advice, learned the hard way: When you name a field or control, refrain from using special characters like "#". This can cause problems as you start to use these field names throughout your VBA. Field name of ECN, control named txtECN should be sufficient for any purposes you have.

Remember to declar this Global String in a standalone VBA Module--otherwise you won't be able to use it beyond the confines of the Form.

Then, when you are on your Form, this is where you set the Filter, as you have described:

Code:

gstrFilter = "ECN = " & Me.txtECN

(I removed the hashtag)

Now, on the Report's OnOpen Event:

Code:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo EH

    If Len(gstrFilter) <> 0 Then
        With Me
            .Filter = gstrFilter
            .FilterOn = True
        End With
    End If

    Exit Sub
EH:
    MsgBox "There was an error initializing the Report!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

Do you see what we are doing? We check to see if the filter is empty first. If not, we apply the filter to the Report. This will show only the data for that particular ECN.

Make sense?

This has saved me many haedachees and recoding of queries, and I personally think it's pretty easy to do, once you understand the basic principles behind it.

**DJRhino1175** · Aug 14 '18, 12:45 PM

Ok I got the report part in place and ' blocked out from running.

I don't completely understand the "Remember to declare this Global String in a standalone VBA Module" part.

Do I need to create a new module and put this as the code in the module?

**zmbd** · Aug 14 '18, 01:42 PM

Alternative to the global variable in the report is to use the tempvars collection and pass the value there...
Then query, report, macro, and vba can all access the value.

Really depends on how the report is structured. IMHO: The query should be such that it pulls the WHERE clause to restrict the records fed to the report instead of filtering the report along the lines of:

(I've pulled a lot out of this SQL indicated by (...))

Code:

SELECT t_testkit.pk_testkit, (...)
FROM t_testYear INNER JOIN  (...)
WHERE (((...)(t_testkit.fk_testYear) 
   Like [iCODE][TempVars]![ReportYears][/iCODE]))
ORDER BY t_testinglaboratory.testinglaboratory_name, t_testkit.fk_testYear, t_testkit.fk_testmonth, t_samplingpoint.samplingpoint_name;

My code sets several [tempvars] that the query runs against which then feeds the report.

I've also done this directly as a report filter:
(simple example)
[IMGnoThumb]https://bytes.com/attachment.php? attachmentid=96 54[/IMGnoThumb]

Simple code:
This is in my cancel button, it resets a couple of the tempvars so that if the report is open directly it will default to showing everything in query.
You'd use the same concepts here to set the member of the tempvars collection with the form's value.

Code:

Private Sub zctrl_btn_Cancel_Click()
'Temp Code
  If [TempVars]![reportyears] & "" = "" Then
    [TempVars].Add "ReportYears", "*"
  Else
    [TempVars]![reportyears] = "*"
  End If
  If [TempVars]![openreportfor] & "" = "" Then
    [TempVars].Add "OpenReportFor", "3"
  Else
      [TempVars]![openreportfor] = 3
  End If
  DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Attached Files

971227.JPG (65.0 KB, 3852 views)

**twinnyfo** · Aug 14 '18, 01:59 PM

DJ,

The standalone Module would be as simple as this:

Code:

Option Compare Database
Option Explicit

Private gstrFiler As String

In general, I agree with Z's statement of restricting the records sent to the Report first, rather than making the Report filter.

There are ways of using a Global string instead of TempVars(). Either method works. You just have to remember to treat TempVars as an object and not a variable.

It comes down to how comfortable you are with each method (or personal preference).

**DJRhino1175** · Aug 14 '18, 03:02 PM

I created a module and Named it ReportFilter using this code:

Code:

Private gstrFilter As String

Then on the open event of the report I have:

Code:

Private Sub Report_Open(Cancel As Integer)

    Dim gstrFilter As String
    
    On Error GoTo EH

    If Len(gstrFilter) <> 0 Then
        With Me
            .Filter = gstrFilter
            .FilterOn = True
        End With
    End If

    Exit Sub
EH:
    MsgBox "There was an error initializing the Report!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub

End Sub

And this is the Code for e-mailing:

Code:

Private Sub btnMail1_Click()

Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
Dim strReportName As String
Dim gstrFilter As String

Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)

strReportName = "New ECN Report"

DoCmd.OutputTo acOutputReport, "rptECN", acFormatPDF, CurrentProject.Path & _
               "\" & strReportName & ".pdf", False, , , acExportQualityPrint
 
'Retrieve all E-Mail Addressess in tblEMailAddress
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", dbOpenSnapshot, dbOpenForwardOnly)
 
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![EmailAddress] & ";"
      .MoveNext
  Loop
End With
'--------------------------------------------------
 
With oMail
  .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = "Please review the attached ECN and complete any and all tasks that pertain to you."
  .Subject = Replace(Replace("ECN# |1: |2", "|1", Nz([ECN#], "")), "|2", Nz([NewPN], ""))
    .Display
  .Attachments.Add CurrentProject.Path & "\" & strReportName & ".pdf"
  
End With
 
Set oMail = Nothing
Set oOutlook = Nothing
 
rstEMail.Close
Set rstEMail = Nothing

Dim aFile As String
aFile = "N:\Unsecure Share\New ECN System\New ECN Report.pdf"
If Len(Dir$(aFile)) > 0 Then
     Kill aFile
End If

End Sub

I got something wrong as it is trying to output all records of the report and not the one that's filtered on the Form. I have never done a global string or Tempvars so I don't really know how to do them correctly.

I use a Embedded Macro with a where condition of:

Code:

="[ECN#]=" & Nz([ECN#],0)

This opens the form filtered to the ECN# I clicked on to view.

**NeoPa** · Aug 14 '18, 03:05 PM

That should say (In Twinny's post.) :

Code:

[U][B]Public[/B][/U] gstrFilter As String

**twinnyfo** · Aug 14 '18, 03:23 PM

Thanks Neo!

Yes - Public

Line 3 of your second block of code and line 10 of your third block of code should be deleted. Since you have declared it publicly, re-declaring it makes it local and it would only apply to the Report. It probably threw an error, because I had fat-fingered my previous instructions as "Private".

Also in your third block of code, you must set the Filter's value:

Code:

gstrFilter = "[ECN#]=" & Me.txtECN#

You could make this line 16.

Delete the embedded macro you described in your fourth block of code.

Unless I've missed something obvious, this should do it.

Believe it or not, your description of what is happening is exactly what we told it to do. But, this should fix it....

**DJRhino1175** · Aug 14 '18, 03:58 PM

The embedded macro is there for a reason,It opens the form to the specific ECN# that needs to be reviewed for editing.

I made the changes you suggested but it wont compile...

Error says Method or data member not found. This is on the second block of code.

Code:

Public gstrFilter As String

Code:

Private Sub btnMail1_Click()

Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
Dim strReportName As String

Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)

strReportName = "New ECN Report"

gstrFilter = "[ECN#]=" & Me.txtECN#

DoCmd.OutputTo acOutputReport, "rptECN", acFormatPDF, CurrentProject.Path & _
               "\" & strReportName & ".pdf", False, , , acExportQualityPrint
 
'Retrieve all E-Mail Addressess in tblEMailAddress
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", dbOpenSnapshot, dbOpenForwardOnly)
 
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![EmailAddress] & ";"
      .MoveNext
  Loop
End With
'--------------------------------------------------
 
With oMail
  .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = "Please review the attached ECN and complete any and all tasks that pertain to you."
  .Subject = Replace(Replace("ECN# |1: |2", "|1", Nz([ECN#], "")), "|2", Nz([NewPN], ""))
    .Display
  .Attachments.Add CurrentProject.Path & "\" & strReportName & ".pdf"
  
End With
 
Set oMail = Nothing
Set oOutlook = Nothing
 
rstEMail.Close
Set rstEMail = Nothing

Dim aFile As String
aFile = "N:\Unsecure Share\New ECN System\New ECN Report.pdf"
If Len(Dir$(aFile)) > 0 Then
     Kill aFile
End If

End Sub

Code:

Private Sub Report_Open(Cancel As Integer)

    On Error GoTo EH

    If Len(gstrFilter) <> 0 Then
        With Me
            .Filter = gstrFilter
            .FilterOn = True
        End With
    End If

    Exit Sub
EH:
    MsgBox "There was an error initializing the Report!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub

End Sub

[imgnothumb]https://bytes.com/attachments/attachment/9655d1534261749/enbedded-macro.jpg[/imgnothumb]

Attached Files

Enbedded macro.jpg (193.1 KB, 3739 views)

**twinnyfo** · Aug 14 '18, 04:30 PM

Error says Method or data member not found. This is on the second block of code.

Which specific line is highlighted with this error?

**DJRhino1175** · Aug 14 '18, 05:31 PM

gstrFilter = "[ECN#]=" & Me.txtECN#

**twinnyfo** · Aug 14 '18, 05:50 PM

This is on your Form? You have a control named "txtECN#" (see your Post #16)?

txtECN# should be the name of the Control that has the ECN# in it. You could also use Me.ECN#.

Please keep in mind that every time I write that control/field name a shiver runs up my spine.

That pound sign ("#") could be causing some problems--I think someone might have mentioned that a while back....

#itsapoundsign

:-D

**DJRhino1175** · Aug 14 '18, 06:19 PM

Ok, I went in and changed ECN# to ECNID...

I'm still getting an error on this part:

Code:

gstrFilter = "[ECNID]=" & Me.ECNID

**twinnyfo** · Aug 14 '18, 06:35 PM

DJ,

Do you have a split form with the datasheet on the bottom or do you have a form with subform in datasheet view on the bottom. This is probably where it is being hung up.

Wherever you are getting your ECNID must be where you get the same ECNID for your Filter.

Me.ECNID and Me.txtECNID always assume a form which moves through records and the current record holding the value you can use. Apparently you are not doing it that way and the main from is not bound to that same set of data.

I hope I am making sense. But you need to assign the ECNID to the Filter--but I don't know where you are getting the ECNID from.

**DJRhino1175** · Aug 14 '18, 06:41 PM

ECNID is on my form, but it is located in the Header. Could this be the issue?

Send email with report as an attachment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment