email reports from access through outlook

I need to send a filtered report to a set of employees with their individual inventories. The report I need to run is "copy of trader holdings," with the table with the list of traders named "books," I also have a table with each traders email address named "email." Finally I have a form named "select trader" that I use to filter out each trader manually, though I don't know if that is needed to do this automatically. Each "trader" that I wish to create a report for has a unique number in the books table, that field is named "trader." The email fields are named "email" and email2," as a number of the book reports need to be sent to multiple email addresses. In a perfect world I would add a button to my "select trader" form and the rest would flow automatically through outlook on my computer.

Finally I should mention that my programming level is just above "ham biscuit."
Who is Participating?
Helen FeddemaConnect With a Mentor Commented:
Supposing you have a form that displays data for a trader on each record, you could use something like this (adding in the stuff from the other procedure that creates the report, saves it as a PDF and attaches it to the email):

Private Sub cmdSendEMail_Click()
'Created by Helen Feddema 5-19-2000
'Last modified 11-15-2001

On Error Resume Next

   DoCmd.RunCommand acCmdSaveRecord

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim itm As Outlook.MailItem
   If Nz(Me![EmailName]) = "" Then
      GoTo ErrorHandlerExit
   End If

   'Create new mail message to current contact
   Set itm = appOutlook.CreateItem(olMailItem)
   With itm
      .To = Me![EmailName]
      .Subject = Nz(Me![Subject])
      .Body = Nz(Me![Body]))
      'To display email for manual editing
      'To send automatically
   End With
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Helen FeddemaCommented:
You should be able to modify this code for your use (you will need to make a query or recordset with one record per employee):

Public Sub SendPDFEmails()
'Created by Helen Feddema 24-Jan-2010
'Last modified by Helen Feddema 24-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim dbs As DAO.Database
   Dim lngCount As Long
   Dim lngEmployeeCount As Long
   Dim lngID As Long
   Dim msg As Outlook.MailItem
   Dim rpt As Access.Report
   Dim rstEmployees As DAO.Recordset
   Dim strAttachmentsPath As String
   Dim strBody As String
   Dim strEmployeeName As String
   Dim strEMailAddress As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReportFile As String
   Dim strReportName As String
   Dim strSQL As String
   Dim strSubject As String
   Dim strTitle As String
   strAttachmentsPath = GetProperty("AttachmentsPath", "") & "\"
   strSubject = GetProperty("MessageSubject", "Your custom report")
   strBody = GetProperty("MessageBody", "Your current report is attached as a PDF")
   strReportName = "rptEmployeeInvoices"
   Set dbs = CurrentDb
   Set rstEmployees = dbs.OpenRecordset("qryEMailEmployees")
   lngEmployeeCount = rstEmployees.RecordCount
   Debug.Print lngEmployeeCount & " employees need reports"

   If lngEmployeeCount = 0 Then
      strTitle = "No reports to send"
      strPrompt = "No employees need reports; canceling"
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
      GoTo ErrorHandlerExit
   End If
   Do While Not rstEmployees.EOF
      lngID = rstEmployees![EmployeeID]
      strEmployeeName = rstEmployees![Salesperson]
      strEMailAddress = rstEmployees![Email]
      strReportFile = strAttachmentsPath & "Employee Invoices" _
         & " for " & strEmployeeName & ".pdf"
      Debug.Print "PDF save name and path: " & strReportFile
      'Create filtered query as report record source
      strRecordSource = "qryInvoices"
      strQuery = "qryInvoicesPerEmployee"
      If lngID <> 0 Then
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[EmployeeID] = " & lngID & ";"
      End If
      Debug.Print "SQL for " & strQuery & ": " & strSQL
      lngCount = CreateAndTestQuery(strQuery, strSQL)
      'Output customized report to PDF
      DoCmd.OutputTo objecttype:=acOutputReport, _
         objectname:=strReportName, _
         outputformat:=acFormatPDF, _
         outputfile:=strReportFile, _
      'Create new mail message and send to employee
      Set msg = appOutlook.CreateItem(olMailItem)
      With msg
         .To = strEMailAddress
         .Subject = strSubject
         .Body = strBody
         .Attachments.Add strReportFile
      End With
   strTitle = "Done"
   strPrompt = lngEmployeeCount & " PDFs created and emailed"
   MsgBox prompt:=strPrompt, _
      buttons:=vbInformation + vbOKOnly, _

   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in SendPDFEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 7-28-2002
'Last modified 2-4-2003

On Error Resume Next

   Dim qdf As DAO.QueryDef
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      CreateAndTestQuery = .RecordCount
   End With
   Exit Function

   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
End Function

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
So only one email needed for the selected trader?

Or do you want all traders to get an individualized email automatically without having to select "each" trader one at a time...
In other words, click one button and all traders get their individualized emails...
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

jtbold2Author Commented:
I'd like to click one button and each trader gets their individualized report
Jeffrey CoachmanMIS LiasonCommented:
Then I think Helen has got you covered...
jtbold2Author Commented:
thanks I will give it a shot
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.