E-Mail Access Report With Multiple Outlook Options

[jkpats12]

Hello, I have some code put together with help through this site which allows me to send e-mail's out as high importance, voting options, flags..etc.

My issue is I would like to send a report that is built in the current database instead of attaching a report from a network share ?

The process currently loops through 100's of e-mail addresses & e-mail's out the info, but I would like to attach the Access report into this e-mail without having to save it to a network share over a 100 times (this report is built with a dynamic query that will update the info on the report based on the loop)

Any thoughts would be appreciated.

Thank you

 

[rolaaus]

If you post the code you found (with any modifications you may have made) I'm sure we can come up with something - it may be as simple as saving this report out to the Windows temp directory - which I have code I am currently using, that will do this. But I won't know if this helps unless I see what you're using.

 

[jkpats12]

Below is the code I'm currently using, everything is working fine; however the .Attachments.Add code is where I would like to just place a report created within the Access db rather then sending it out to a location to pick it up from.

Any help would be appreciated.

Thanks


Option Compare Database

Function EMail()

Dim olApp As Outlook.Application
Dim olnamespace As Outlook.NameSpace
Dim olMail As Outlook.MailItem
Dim strSQL As String
Dim strSender As String
Dim strRecipient As String
Dim strEmail As String
Dim strDeliveryDate As Date
Dim strSubject As String
Dim strDbaseName As String

On Error Resume Next

DoCmd.Hourglass True

'StrRecipient = Recipient name here
'strEmail = E-Mail address here
'strDbaseName = DBase Name Here
'strSubject = "Subject Here"
'strSender = fOSUserName()

Set olApp = New Outlook.Application
Set olnamespace = olApp.GetNamespace("MAPI")
Set olMail = olApp.CreateItem(olMailItem)

With olMail

.To = strEmail
.VotingOptions = "Approve;Reject"
.Importance = olImportanceHigh
.Subject = strSubject
.SentOnBehalfOfName = strSender
.Body = "Body here"
'.Attachments.Add "Looking to add Access Report here ?"
.Links
.FlagDueBy = #mm/dd/yyyy#
.FlagStatus = olFlagMarked
.Display

End With

On Error Resume Next
DoCmd.Hourglass False
Set olApp = Nothing
Set olnamespace = Nothing
Set olMail = Nothing

End Function

 

[rolaaus]

I am pretty sure (99%) that you need to do an Export of your report (most likely you would want a "snapshot" file. Then you can attach that snapshot file as your email attachment.

I'm not sure what version of Access you are running, or what capabilities may have changed, but it has been a standard for quite some time that in order to do any sort of "transferring" of objects (forms, reports, etc.) that you can only "send" them to other Access database files. If you are wanting to view these items (specifically reports) you would have to do an export into some other format (like XLS, or Word or Snapshot).

In case you're not familiar, a snapshot is similar in nature to Microsoft Excel's "viewer" program, where you can send a spreadsheet file to someone that doesn't have Excel, and they are able to open up the file and print it out, but they can't do any editing. The great thing about snapshot reports is that it retains any special formatting you might have - like, my earliest Access database had a report that would shade an entire record in the report if that record was behind schedule, or it would turn the border of a specific field on if say, that record was designated as a "prototype" (it was for a manufacturing company).

One thing you may need to also ensure is that when you run this "send email" procedure, and if you are using the snapshot type report, you will have to save the report to disk, then pick up the directory you used and filename and use that in your attachment.add line of code.

Let me know if you need specific help and I'll see what I can do.

 

[jkpats12]

Thank you rolaaus........I'm familiar with what you reference.

I was just hoping to send the report that is contained within Access due to roughly 100 e-mail's that will be sent & the report is dynamic to each e-mail since it is based off of a dynamic query.

Thanks again for the help.