Send Multiple Emails with different attachments from MS Access (1 Viewer)

SteveBradshaw

New member
Local time
Today, 19:24
Joined
May 12, 2020
Messages
4
I have designed an Access Database that works out commissions and Overrides for about 400 Advisers. We run this every 2 weeks for a commission run.
At the end of each commission run statements are sent out from the database to each Adviser from the Access database. At present they are sent out one at a time.
To do this there is a form with a combo box whereby the Adviser is chosen and this then searches the chosen Adviser on the form and a filter by selection occurs, a statement pdf is created and an email with the pdf attached is created. This is then sent and we go back to the form, choose the next adviser and repeat the process.
I am trying to work out how to send them all in one go? Any ideas?
 

cheekybuddha

AWF VIP
Local time
Today, 19:24
Joined
Jul 21, 2014
Messages
2,237
What code are you using to send the emails at the moment? Please post it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:24
Joined
Oct 29, 2018
Messages
21,358
Hi. Does the combo really filters the form or merely navigates it to the desired record? I'd like to see the code behind the combo, please.
 

SteveBradshaw

New member
Local time
Today, 19:24
Joined
May 12, 2020
Messages
4
As I am not great with VBA code, I did this with a macro. I do realise that VBA Code may be needed to achieve what I need.
MacroSnapshot.png
MacroSnapshot.png
 

SteveBradshaw

New member
Local time
Today, 19:24
Joined
May 12, 2020
Messages
4
The code behind the Combo: After update

Option Compare Database

Private Sub Combo29_AfterUpdate()

'Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Advisor Name] = """ & Me![Combo29] & """"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:24
Joined
Oct 29, 2018
Messages
21,358
The code behind the Combo: After update

Option Compare Database

Private Sub Combo29_AfterUpdate()

'Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Advisor Name] = """ & Me![Combo29] & """"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
Hi. Thanks. Just as I thought. So, the simplest approach is to navigate the form one record at a time and clicking the email button each time - using code, of course.
 

bastanu

AWF VIP
Local time
Today, 12:24
Joined
Apr 13, 2010
Messages
1,401
I would save your form as a report, change its record source to limit it to one advisor using a custom VBA function that a Public variable to return the AdvisorID:

Public lCurrent_Email_Record As Long
'use this in the criteria row of the reports record source to limit the AdvirorID
Public Function vcCurrent_Email_Record()
vcCurrent_Email_Record = lCurrent_Email_Record
End Function


To use that you set up a recordset and loop through one record at the time changing the public variable and calling an emailing sub:
Code:
Private Sub cmdEmailMultiple_Click()

On Error GoTo Err_cmdEmailMultiple_Click
Dim stDocName As String, sFileName As String, lEmails As Long, sEmail As String
Dim rst As DAO.Recordset


Set rst = CurrentDb.OpenRecordset("SELECT AdvisorID FROM tblAdvisors;", dbOpenSnapshot)

On Error Resume Next

DoCmd.Close acReport, "IndividualStatementsrpt"
lCurrent_Email_Record = 0

Do Until rst.EOF
    sEmail = rst("email")
    sFileName = Forms![MainSwitchboardForm]![PDF_Folder] & "\" & rst("AdvisorLastName") & "_"  & Format(Date, "YYYY_MM_DD") & ".pdf"
    lCurrent_Email_Record = CLng(Nz(rst("Student Number"), 0))
    DoCmd.OutputTo acOutputReport, ""IndividualStatementsrpt"", "PDF Format (*.pdf)", sFileName, False
    Call vcSendEmail_Outlook("Your individual Statement.", sEmail, , , sFileName, "")
rst.MoveNext
Loop

rst.Close
Set rst = Nothing

Exit_cmdEmailMultiple_Click:
    Exit Sub

Err_cmdEmailMultiple_Click:
    MsgBox Err.Description
    Resume Exit_butHealthClaim_Click   
End Sub


Public Function vcSendEmail_Outlook(sSubject As String, sTo As String, Optional sCC As String, Optional sBcc As String, Optional sAttachment As String, Optional sBody As String)
    Dim OutApp As Object
    Dim OutMail As Object
  
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
 
    Set OutMail = OutApp.CreateItem(0)
 
    OutMail.To = sTo
    If sCC <> "" Then OutMail.CC = sCC
    If sBcc <> "" Then OutMail.BCC = sBcc
    OutMail.Subject = sSubject
    OutMail.HTMLBody = sBody
    OutMail.Attachments.Add (sAttachment)
    OutMail.Send 'Send | Display
    Set OutMail = Nothing
End Function

Cheers,
Vlad
 

cheekybuddha

AWF VIP
Local time
Today, 19:24
Joined
Jul 21, 2014
Messages
2,237
Where do you add the attachment?

Or does it attach an pdf image of the form?
 

Users who are viewing this thread

Top Bottom