Send email with report as an attachment

[DJRhino1175]

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
 
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)
 
'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
 
End With
 
Set oMail = Nothing
Set oOutlook = Nothing
 
rstEMail.Close
Set rstEMail = Nothing
 
 
End Sub

This is what I have so far, the ".Attachment.add" is where I'm stuck. I want it to send a report that I have, based of the data in the form where this button is located, by the ID #.

I have tried it with out the .Attachment part and it does everything its supposed to, now I just need to get it to pull the report filtered based off of ID# on the Form.

Does anyone have any Ideas? I have done some searching and everything says to export the report first then attach it, which I prefer not to do.

Thanks for all the help.

[twinnyfo]

DJ - you can only add a file with Attachments.Add.

SO, you must export your Report first (I usually make a PDF), keep track of the file name, and then attach it in your code.

That is the easiest way I know of.

[DJRhino1175]

Darn it. I usually us the Send object, but I cannot add anymore email address to it, so this is my next object. Its so weird you can do one but not the other....So now I'll have to figure out the export part.

Thanks for the help.

[NeoPa]

DJRhino:
Does anyone have any Ideas? I have done some searching and everything says to export the report first then attach it, which I prefer not to do.

Unfortunately that doesn't make too much sense as an attachment, as you probably know, is a file - typically with an extension that indicates the type of file. Trying to use anything that isn't a file, quite apart from there being no interface to allow you to send it, would also be meaningless when it's received. Ask yourself what the recipient would do when they receive it if they receive some data as an attachment that isn't even a file, and doesn't have an extension that allows it to be opened with a specified application.

[twinnyfo]

DJ,

Also, there are some other threads on this forum that discuss filtering reports and saving them.

That could be another threaded question if you have challenges working through that. We'd love to hepp!

[DJRhino1175]

Neopa,

Its because I'm used to sending things with the send-object function and it does everything for you, unfortunately each line in the email address bar only allows 255 characters. This is why i stated "Does anyone have any Ideas? I have done some searching and everything says to export the report first then attach it, which I prefer not to do."

As the send-object filters and attaches the report for you, with out exporting, renaming and all that.

[twinnyfo]

DJRhino1175
Does anyone have any Ideas? I have done some searching and everything says to export the report first then attach it, which I prefer not to do.

We get it! There are some things that Access does and there are some things that Access does not do. This is one of them Access does not do.

The only way around this is to use the method that you prefer not to use. However, I would suggest that using this method will also help you understnad additional aspects of Access that maybe you previously did not understand so well--and thus add those tools to your tool kit!

[NeoPa]

Hi DJ.

I could point to the difference in the parameters expected/allowed for the two separate procedures, but if I'm honest I have to admit that I'm surprised the SendObject() would do that much for you. I expect it must take the internal Access object and first create a temporary file for you in order to add it as an attachment. It's still true that an attachment must first be available as a file within the file system, but if that's created for you by the procedure itself that may be less obvious.

So, your question wasn't as senseless as I'd imagined. Fair play.

[zmbd]

+ How many address are you attempting to use with the DoCmd.SendObject? I've sent stored reports to around 100 to 150 recipients all at once using this simple function.

+ Outlook automation
TwinnyFo is correct about using the Application-Automation approach you're showing in your first post... I usually use something like

'(... THIS IS AIR CODE ...)
'that is to say, I'm pulling this out of my memory without aid of the VBA-Editor so there may be a typo or two :)
DoCmd.OutputTo Objecttype:=acOutputReport, ObjectName:="YourReport", _
  Outputformat:=acFormatPDF, Outputfile:=strToFileLocationWithName
'(...)
With outLookMailItem
  .Subject = Replace(Dir(strOutputPath), ".pdf", "")
  .Attachments.Add strToFileLocationWithName
  .Close (0)
  .Display
End With
'(...)

Use a for-next-loop to cycle the .Attachments.Add to add multiple attachments.

[DJRhino1175]

I think its like 30, but our e-mail format is very long and eats a lot of characters. Its [e-mail], so it doesn't take long to use up 255 characters in each of the lines to:, CC:, BCC:. As a side note I use a macro to do this so this might be what is giving me the limit. I am kind of liking the approach I'm taking. Just need to figure the output to and how to name the file, I'm guessing it will need to be a generic name. I wouldn't mind it if I could pull the ID# as part of the title.

Where in the code that I put up would I put the output at? can it go any where before the display part? A lot of this is new for me so I'm a little nervous with playing around with the code I have as I might not be able to fix it if i screw it up.

[zmbd]

DJRhino1175,

+ You mention Macros here - these are very limited in their application - and yes, indeed, if you are directly entering the emails into the Macro parameters you will hit the 255 limit quite quickly.

+ Try the docmd.sendobject via VBA, I think you'll find that the only limit you will hit is set by your Mail connection. If that doesn't punch the ticket the you'll have to use either the Outlook Automation or (if your admins have left it enabled) the CDO methods.

[DJRhino1175]

I'm going to try both methods tomorrow to see which one I like better. But it is good to know the Docmd.sendobject in VBA is like the one in the macro but way better. Did not even think of that and I knew it was there...Trying to over think it I guess.

[twinnyfo]

DJ,

The big sticking point with .SendObject is that you cannot apply filters directly using that method. You must apply filters either programatically (using the Report's .Filter) or at the Query-level (applying some sort of filter using parameters that hte Query grabs from the Form's Controls.

However, theoritically, if you had a list of Customers with a CustID, your VBA outline would look like this:

Private Sub cmdSendEMail()
    Dim your variables
 
    Get your list of Customer IDs
 
    Loop through your list of IDs
        Create and save your Report based upon the specific ID (name it appropriately)
        Using the file name you just created for your report, attach it to your Outlook Object.
 
    Clean up your objects
 
    Don't forget your Error Handling  ;-)
End Sub

I do this all the time.

We are all glad to hepp you through additional aspects of this process.

[DJRhino1175]

Option Explicit
 
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"
 
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
 
 
End Sub

Here is what I have as of right now. 2 issues...

1) How do I filter the report so it only outputs the current record
2) After the the file is attached, is it possible to delete the file that was created?

Other than this, it is working perfectly, out puts the file, It attaches the file, adds everyone to the e-mail.

[twinnyfo]

Issue 2: Use Kill [FileName]

Issue 1: A little more complex.

There are several ways you could do this.

You could use the value in the Form's Control as one of the parameters of the Report's underlying Query:

"WHERE CustomerID = " & Forms![FormName]![ControlName]

This can be done in the Query Editor's Expression Builder to get the syntax correct. The drawback to this is that this Query is then limited to that Form. Additionally, sometimes these types of Queries can be tricky--especially if they are used as sub-queriees for another Query. There are a few ways around that, but, in general I prefer to avoid that. However, it is the most "direct" way to approach the problem.

Second, you could use a Global filter string which the Report uses at execution. The basics of it go this way:

1. Establish a Global String Vairable or use TempVars().
2. Just prior to executing the report, you set the value of the Filter: gstrFilter = "CustID = " & Me.txtCustID
3. In the Report's OnOpen procedure, you check to see if the Filter is empty (which you can set if you want all records). If not, you set the Record's Filter.
4. Save the Report, using the Customer's Name or CustID--however you want it--in the name of the Report. This item is essentially the same as your current method.

That's really the basics. I have found this second method to be a bit more complicated to design, but I have really liked how it executes. I've had no problems with it whatsoever.

There are probably others ways to skin this cat.

Hope this hepps!

[DJRhino1175]

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

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

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

[twinnyfo]

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:

gstrFilter = "ECN = " & Me.txtECN

(I removed the hashtag)

Now, on the Report's OnOpen Event:

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]

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]

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

SELECT t_testkit.pk_testkit, (...)
FROM t_testYear INNER JOIN  (...)
WHERE (((...)(t_testkit.fk_testYear) 
   Like [TempVars]![ReportYears]))
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)

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.

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

[twinnyfo]

DJ,

The standalone Module would be as simple as this:

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]

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

Private gstrFilter As String

Then on the open event of the report I have:

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:

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:

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

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

[NeoPa]

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

Public gstrFilter As String

[twinnyfo]

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:

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]

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.

Public gstrFilter As String

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

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

[twinnyfo]

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]

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

[twinnyfo]

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]

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

I'm still getting an error on this part:

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

[twinnyfo]

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]

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

[DJRhino1175]

Dang it, Now I'm having issues with my reports. It keeps Telling me to:
Enter Parameter Value ECN#

But I change these in everything. Tables open up fine, queries open up fine, Forms open up fine, but not any of the reports....I can feel my hair turning grayer...

[twinnyfo]

If it is in a control, then you can use it. just use Me.[Name of the Control] in your VBA.

Do you understand that you have to get the value of ECNID into the Filter. Since I don't have your DB in front of me, I am unable to click around and determine your structure and the names of all your controls.

Understanding how to reference objects and controls in your VBA will great dividends for you in the long run.

Please forgive us if we take some of these things for granted.

[twinnyfo]

Somewhere in your report there is a refeerence to the ECN# instead of the ECNID. Is it being sorted by that field, perhaps? this will sometimes cause such an error even after you have changed all your controls.

[DJRhino1175]

I'm sending it to my house laptop so I can see if I can fix it with out any interruptions...

[DJRhino1175]

I think I got it working completely now. Once I went through all my reports and fixed the sorting and grouping, I gave the code a try as it is and it seemed to work. As a matter of fact I tried it multiple times and it worked every time.

Thanks twinnyfo, Its a good thing you are very patient, I can be tough to straighten out...

[twinnyfo]

That's awesome! I'm glad we could get this working for you.

On to the next project, eh?