Email Individual Reports

[vnwallace]

Hi,

I am working on an Access DB and I need to email individual reports to email addresses linked to the corresponding email. I would like these email to be sent though outlook and the attachment to be a PDF. Does anyone know how to help with this?

Thank you!

Valerie Wallace

 

[pbaldy]

This should get you started:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[vnwallace]

Thank you! Before I get started, do I create this using a Standard Module or a Class Module? I'm guessing class but I just wanted to verify before I start this.

 

[vnwallace]

Also, when looking at this code, I see something that might need to differ from mine. I need an email with the recipient/subject/body to pop up but not to send (we will manually push the send button).. Any ideas?

 

[cricketbird]

Here is the code I use for a button on a form that will print a report corresponding to the current record and open up an email with the recipient blank. You can hard-code any variable (to, subject, body) by modifying this code slightly. It doesn't send the email - just opens it up so you can edit the body with your own relevant message.

Private Sub EmailKeeperCardBtn_Click()

On Error GoTo MyErrorHandler
Me.Refresh

Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim stEmailMessage As String
Dim stCaption As String
Dim myPath As String

'This sets the strings for each part of the email
stEmailMessage = "Please see the attached updates for item#" & Me.ITEMID
stSubject = "Updates to Item#" & Me.ITEMID'
stReport = "RptITEM"
'This will change the caption of the report (which is what the filename is saved as) to something meaningful - in this case the numbers and date of thereport.  If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

stCaption = Me.ITEMID & " " & Me.NOTEID & " #" & " " & Format(Now(), "yyyy-mm-dd") & " " & Format(Now(), "hh-nn")

myPath = "C:\MyPath\My Folder\"

stWhere = "ITEMID = " & Me.ITEMID

DoCmd.OpenReport stReport, acViewPreview, , stWhere, acWindowNormal, ""
Reports![RptITEM].Caption = stCaption  'Renames The report and adds ID field

'In my environment, some users have Office 2010, and other still have 2007 and can't handle PDFs.  This code checks which version and sends an XPS file for 2007 users instead (we, for whatever reason, use XPS here still a bit).

If Access.Version > 13 Then

DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint

Else

DoCmd.SendObject acSendReport, stReport, acFormatXPS, , , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, , acFormatXPS, myPath & ".xps", False
End If


Exit Sub

MyErrorHandler:
'Ignoring this error, mostly.  You may not want to do this,

If Err.Number = 2501 Then
Beep
Beep


Exit Sub
Else
MsgBox Err.Number & ":" & Err.Description, vbOKOnly
End If

End Sub

 

[pbaldy]

Either in a standard module or behind a form, depending on your specifics. Whether the email is sent or stays open for the user is optional, depending on method.

 

[vnwallace]

Thank you! This is an amazing start! Below is my code.. I have a few issues..

-I need it to split out the reports and email to a corresponding address (stored in a table that the report gathers it's data from..the table is "Counterparty Data")
-In my code I couldn't get my reports to save to the directory specified.. it's not important to me that they save individually

Thank you so much for all of your help, it is GREATLY appreciated!!!

Private Sub EmailReport_Click()

Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim stEmailMessage As String
Dim stCaption As String
Dim myPath As String

'This sets the strings for each part of the email

stEmailMessage = "Please see the attached collateral call for today."
stSubject = "Collateral Demand"
stReport = "DECO Import File"

'This will change the caption of the report (which is what the filename is saved as"
to something meaningful - in this case the numbers and date of thereport. If you have multiple people making these reports at once, and would have multiple ones with the same date/time stamp, you should modify this a little to prevent overwriting files.

stCaption = "Collateral Demand" & Format(Now(), "mm-dd-yyyy")

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database"

DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint

Exit Sub

 

[vnwallace]

I actually got it to save where I want it to. Now I just need help splitting out the reports to indivuals and email to their corresponding email address. Below is my updated code:

Private Sub EmailReport_Click()

Dim stReport As String
Dim stWhere As String
Dim stSubject As String
Dim stEmailMessage As String
Dim stCaption As String
Dim myPath As String


stEmailMessage = "Please see the attached collateral call for today."
stSubject = "Collateral Demand"
stReport = "DECO Import File"

stCaption = "Collateral Demand" & Format(Now(), "mm-dd-yyyy")

myPath = "J:\Investments\Trade Operations\Derivative Transactions\Collateral\Access Database\"

DoCmd.SendObject acSendReport, stReport, acFormatPDF, , , , stSubject, stEmailMessage, True, ""

DoCmd.OutputTo acOutputReport, stReport, acFormatPDF, myPath & stCaption & ".pdf", False, , , acExportQualityPrint

Exit Sub

 

[pbaldy]

I'm back to the loop and filtering method linked to earlier.

 

[vnwallace]

Is there a way to build that in to my code already or do I need to write it completely different? When looking through this code I am thinking that I will need to write it completely different than what I have so far.. hopefully not though!

Thanks so much!

 

[pbaldy]

The guts of what you have would go inside the loop. You'd also have to add something (customer ID, etc) so that each PDF created had a different name, and you'd need to filter the report either with the method noted or another.