Send email with report as an attachment

**twinnyfo** · Aug 13 '18, 06:37 PM

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** · Aug 13 '18, 06:44 PM

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** · Aug 13 '18, 06:57 PM

Originally posted by DJRhino

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** · Aug 13 '18, 07:03 PM

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** · Aug 13 '18, 07:13 PM

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** · Aug 13 '18, 07:44 PM

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** · Aug 13 '18, 07:54 PM

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** · Aug 13 '18, 11:21 PM

+ How many address are you attempting to use with the DoCmd.SendObjec t? 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

Code:

'(... 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.Ad d to add multiple attachments.

**DJRhino1175** · Aug 14 '18, 12:12 AM

I think its like 30, but our e-mail format is very long and eats a lot of characters. Its firstname.Lastn ame@cooperstand ard.com, 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** · Aug 14 '18, 12:52 AM

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.sendobjec t 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** · Aug 14 '18, 01:04 AM

I'm going to try both methods tomorrow to see which one I like better. But it is good to know the Docmd.sendobjec t 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** · Aug 14 '18, 10:29 AM

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:

Code:

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** · Aug 14 '18, 11:05 AM

Code:

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** · Aug 14 '18, 11:39 AM

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:

Code:

"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:

Establish a Global String Vairable or use TempVars().
Just prior to executing the report, you set the value of the Filter: gstrFilter = "CustID = " & Me.txtCustID
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.
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!

Send email with report as an attachment

Send email with report as an attachment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment