e.mail all "assigned to" (1 Viewer)

rainbows

Registered User.
Local time
Today, 06:50
Joined
Apr 21, 2017
Messages
425
1635623147067.png


hi , the form above is what I use for my action requests, this form shows all active records . at this time is I run the open issues report it sends a email to the individuals in the assigned to field . can you please help me with the code below to sent all issues to all the people in the assigned field at one time

thanks steve



Code:
Private Sub cboReports_AfterUpdate()

 Dim strCriteria As String
    Dim thisCriteria As String
    Dim eMail As String
    Dim Title As String
    Dim Message As String
    Dim i As Integer
    DoCmd.OpenForm FormName:="ReportDialog", WindowMode:=acDialog
    '* check if [TextReportOption] <> 0
    If Me.TextReportOption <> 0 Then
        strCriteria = "(1=1)" '(1=1) means it is always True, therefore returns all records
        Select Case Me.TextReportOption
        Case Is = 1 'Print Preview
            '* Open the report with criteria
            DoCmd.OpenReport REPORTNAME:=cboReports, VIEW:=acViewReport, WhereCondition:=strCriteria
        Case Is = 2 'Print
            '* Open the report with criteria
            DoCmd.OpenReport REPORTNAME:=cboReports, VIEW:=acViewNormal, WhereCondition:=strCriteria
        Case Is = 3 'Email report
        
            If cboReports = "Open Issues" Then
          
                strCriteria = "[STATUS]=""ACTIVE"""
                If DCount("1", "Issues", "Status=""Active""") <> 0 Then
                    With CurrentDb.OpenRecordset("SELECT DISTINCT [Assigned To] FROM [Issues] " & _
                                        "WHERE [Status]=""Active""")
                        If Not (.BOF And .EOF) Then .MoveFirst
                        i = 0
                        While Not .EOF
                            i = i + 1
                            thisCriteria = strCriteria & " And [Assigned To]=" & Nz(.Fields(0).Value, 0)
                            
                         ' eMail = DLookup("[E-mail Address]", "Contacts", "[ID]=" & Nz([Text306], 0))
                          eMail = DLookup("[E-mail Address]", "Contacts", "ID=" & Nz(.Fields(0).Value, 0))
                            '* close report if already open
                            If SysCmd(acSysCmdGetObjectState, acReport, cboReports.Value) <> 0 Then
                                DoCmd.Close acReport, cboReports.Value
                            End If
                            DoCmd.OpenReport REPORTNAME:=cboReports, VIEW:=acViewPreview, WhereCondition:=thisCriteria
                            '* this is a test
                            '* DoCmd.OutputTo acOutputReport, cboReports.Value, acFormatPDF, Environ("userprofile") & "\documents\rpt" & i & ".pdf", True
                            DoCmd.SendObject acSendReport, cboReports.Value, acFormatPDF, eMail, , , "Open Issues", _
                                "Your prompt action is required for the following issues!"
                            DoCmd.Close acReport, cboReports.Value
                            .MoveNext
                        Wend
                     End With
                End If
 

bastanu

AWF VIP
Local time
Today, 06:50
Joined
Apr 13, 2010
Messages
1,401
You are getting close... You need to use the Docmd.OutputTo line that you have commented out (I would use a common temporary name for the resultant Pdf file unless you need to keep them on your network for future reference) in order to get the reports to only show the current "Assigned To" . Once you have the report saved as PDF you need to use automation to create a new Outlook mail message and attach the file or use CDO mail, there are many examples of both approaches.

Alternatively you can use the Docmd.SendObject but you will have to modify the reports source to only show one "Assigned To" at the time; you can use tempvars or a public variable to do that (see this post for sample code: https://www.access-programmers.co.u...tachments-from-ms-access.311530/#post-1694146).

Cheers,
 

Users who are viewing this thread

Top Bottom