?
Solved

email reports from access through outlook

Posted on 2013-10-28
6
Medium Priority
?
589 Views
Last Modified: 2013-11-22
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."
0
Comment
Question by:jtbold2
  • 2
  • 2
  • 2
6 Comments
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 39607191
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, _
         Title:=strTitle
      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, _
         autostart:=False
      
      'Create new mail message and send to employee
      Set msg = appOutlook.CreateItem(olMailItem)
      With msg
         .To = strEMailAddress
         .Subject = strSubject
         .Body = strBody
         .Attachments.Add strReportFile
         .Send
      End With
   
NextEmployee:
      rstEmployees.MoveNext
   Loop
   
   strTitle = "Done"
   strPrompt = lngEmployeeCount & " PDFs created and emailed"
   MsgBox prompt:=strPrompt, _
      buttons:=vbInformation + vbOKOnly, _
      Title:=strTitle

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   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
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

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

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39609324
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...
0
 

Author Comment

by:jtbold2
ID: 39609507
I'd like to click one button and each trader gets their individualized report
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 39609562
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
      .Display
      'To send automatically
      .Send
   End With
   
ErrorHandlerExit:
   Exit Sub

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

End Sub

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39609599
Then I think Helen has got you covered...
0
 

Author Comment

by:jtbold2
ID: 39670123
thanks I will give it a shot
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question