question

noahjohn-6383 avatar image
0 Votes"
noahjohn-6383 asked thedbguy commented

Is there a way to really automate sending of emails through outlook mail without having to click on the send button for every record in Microsoft Access?

Hi I have made a Microsoft access System wherein it will automatically generate the reports as a pdf and automatically send it as an attachment to multiple individual emails through outlook mail. It's in a loop too. Is there a way wherein I will not need to click on the send button for every record? I appreciate all the help I can get.

Here is my code:
Option Compare Database

Private Sub Command36_Click()
Dim rs As DAO.Recordset
Dim rpt As Access.Report
Dim sFolder As String
Dim sFile As String
Dim mailto As String
Dim ccto As String
Dim bccto As String

 Const sReportName = "Payslip_Report"

 On Error GoTo Error_Handler

 'The folder in which to save the PDFs
 sFolder = "C:\Users\DPPI IT\Desktop\payslip\"
 'Set rs = CurrentDb.OpenRecordset("SELECT [ID],[last_name] FROM [Qry1]", dbOpenSnapshot)
    
 Set rs = CurrentDb.OpenRecordset("SELECT [ID],[last_name] FROM [Payslip_upload]", dbOpenSnapshot)
 With rs
     If .RecordCount <> 0 Then    'Make sure we have record to generate PDF with
         'Open the Report
         DoCmd.OpenReport sReportName, acViewPreview, , , acHidden
         'Define a report object so we can manipulate it below
         Set rpt = Reports(sReportName).Report
         .MoveFirst
         Do While Not .EOF
             'Build the PDF filename we are going to use to save the PDF with
             sFile = Nz(![last_name], "") & ".pdf"
             sFile = sFolder & sFile
             'filter the report to the specific record or criteria we want
             rpt.Filter = "[ID]=" & ![ID]
             rpt.FilterOn = True
             DoEvents 'This is critical!!!!
             'Print it out as a PDF
             DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sFile, , , , acExportQualityPrint
             DoCmd.SetWarnings (False)
             mailto = [email]
             ccto = ""
             bccto = ""
             emailmsg = "Hi '[last_name]'," & vbNewLine & vbNewLine & "Please find the report attached"
             mailsub = "payslip"

             On Error Resume Next
             'acFormatpdf will export the result of query into pdf format and will add the pdf as attachment
             DoCmd.SendObject acSendReport, sReportName, acFormatPDF, mailto, ccto, bccto, mailsub, emailmsg, True
             DoCmd.SetWarnings (True)
             'If you wanted to create an e-mail and include an individual report, you would do so now
             .MoveNext
         Loop
         'Close the report now that we're done with this criteria
         DoCmd.Close acReport, sReportName
     End If
 End With

 'Open the folder housing the PDF files (Optional)
 Application.FollowHyperlink sFolder

Error_Handler_Exit:
On Error Resume Next
If Not rpt Is Nothing Then Set rpt = Nothing
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub

Error_Handler:
If Err.Number <> 2501 Then 'Let's ignore user cancellation of this action!
MsgBox "The following error has occured" & vbCrLf & vbCrLf &
"Error Number: " & Err.Number & vbCrLf &

"Error Source: Command0_Click" & vbCrLf &
"Error Description: " & Err.Description &

Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
End If
Resume Error_Handler_Exit
End Sub

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Hi. In your DoCmd.SendObject line, change the last argument from True to False.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

noahjohn-6383 avatar image
0 Votes"
noahjohn-6383 answered thedbguy commented

Thank You very much. It worked

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi. You're welcome. Glad to hear it worked for you. Good luck with your project.

1 Vote 1 ·