Send email with attachments using VBA

[MC42015]

I have been working in copy and paste for years and would love to automate the bi-monthly invoice summary e-mails that I send to my customers!

My Access 2013 database creates summaries for customers exported to an excel spreadsheet.
With the help of Ron De Bruin code, I was able to have excel split the worksheet into separate workbooks.

Back in Access, I have a parameter query that will pull a list of these customers and the path and file to their summary, like this
STID CustID ACCT APCEmail STMTAP STDATE STMTPATH
30 740 999999 [e-mail] TRUE 05-Jan-19 C:\User\Summaries\EMAIL\999999.xlsx

I'm not sure where to begin!!
Do I start with DAO to set database and recordset, then loop through with MoveFirst & DoUntil, build the e-mail name, subject and message with string and SendObject?

[Nauticalgent]

I do something similar to what it is you're trying to do. Here is the code I use. Hopefully you can adapt it to your needs.

 
Private Sub SendEmailPass()
 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rs1 As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim strTo As String
    Dim strCC As String
    Dim strSubject As String
    Dim strBody As String
    Dim strMaintActy As String
    Dim strShopCode As String
    Dim strShipName As String
    Dim intPassCode As Integer
    Dim strPassCode As String
    Dim intWC As Integer
    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim intPE As Integer
    Dim intCSPE As Integer
 
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
 
    If outApp Is Nothing Then
        Set outApp = CreateObject("Outlook.Application")
    End If
 
    intWC = Forms![frmJobPasses]![frmAssignments].Form![AssignActy]
    intPassCode = Nz(Me![frmAssignments].Form![AssignPassCode], 0)
    intPE = Nz(Forms![frmJobPasses]![sfrmUnitInfo].Form![cboPE], 0)
    intCSPE = Nz(Forms![frmJobPasses]![sfrmUnitInfo].Form![cboCSPE], 0)
    strPassCode = Nz(DLookup("PassDescrip", "tblPassCodes", "PassCodeID = " & intPassCode))
    strShopCode = Nz(DLookup("ShopCode", "tblMaintActyUnits", "UnitID = " & intWC))
    strMaintActy = Nz(DLookup("Activity", "tblMaintActyUnits", "UnitID = " & intWC))
    strShipName = Nz(DLookup("ShipFullName", "qryShipName", "UnitID = " & Me.UnitID))
 
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qrySelectEmail")
    Set rs1 = db.OpenRecordset("qrySelectCC")
 
    strTo = vbNullString
    With qdf
        .Parameters("intUnitID") = intWC
        Set rs = .OpenRecordset
        rs.MoveFirst
        Do Until rs.EOF
            strTo = strTo & rs.Fields(0) & ";" & vbCrLf
            rs.MoveNext
        Loop
    End With
    strTo = strTo & GetPE(intPE, intCSPE)
 
    With rs1
        strCC = vbNullString
        rs1.MoveFirst
        Do Until rs1.EOF
            strCC = strCC & .Fields(0) & ";" & vbCrLf
            rs1.MoveNext
        Loop
        strCC = strCC & ";" & Me.UnitPOC_Email
    End With
 
 
    strSubject = "** EMAIL PASS ** FROM FDRMC TO " & strMaintActy & " - " & strShopCode _
                & " FOR " & strShipName & " / " & Me.JobEquip & " / " & Replace(Me.JobCat, "C", "CAT") _
                & " / CASREP: " & Me.JobNum & " / DTG: " & Me.JobDTG
 
    strBody = strMaintActy & ": Please reply to all and confirm acceptance of support and assigned technician," _
            & " and include the FDRMC Naples CDO <mailto:[e-mail] > on all related email traffic." & vbCrLf _
            & vbCrLf _
            & "1. Equipment: " & Me.JobEquip & vbCrLf _
            & "2. For detailed information see DTG: " & Me.JobDTG & vbCrLf _
            & "3. Job Number: " & Me.JCN & vbCrLf _
            & "4. Requested Action: " & Me.JobAction & vbCrLf _
            & "5. Reason for pass: " & strPassCode & vbCrLf _
            & "6. Ship's POC information: " & Me.UnitPOC & " / " & Me.UnitPOC_Email & vbCrLf _
            & "7. Request CC <[e-mail]> on all correspondence for tracking purposes."
    strBody = strBody & vbCrLf _
            & vbCrLf _
            & "Very Respectfully,"
 
 
    Set outMail = outApp.CreateItem(olMailItem)
    With outMail
        .To = strTo
        .CC = strCC
        .Subject = strSubject
        .Body = strBody
        .Display
 
    End With
 
exit_handler:
    rs.Close
    rs1.Close
    Set rs = Nothing
    Set db = Nothing
    Set rs = Nothing
    Set outMail = Nothing
    Set outApp = Nothing
 
End Sub
 

[twinnyfo]

If you are using Outlook, as very helpful article is here.

Yes, the basics are this:

Option Compare Database
Option Explicit
 
Private Sub SendEmail_Click()
On Error GoTo EH
    Dim db              As DAO.Database
    Dim rst             As DAO.Recordset
    Dim strSendTo       As String
    Dim strSubject      As String
    Dim strEMailBody    As String
    Dim strCCLine       As String
    Dim strBCCLine      As String
    Dim strOnBehalfOf   As String
    Dim strAtchs        As String
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("YourQuery")
    With rst
        If Not (.BOF And .EOF) Then
            Call .MoveFirst
            Do While Not .EOF
                strSendTo = !EmailAddress
                '...
                'build the rest of your e-mail
                '...
 
                'Generate and Display the E-Mail
                Call SendAnEMail(olSendTo:=strSendTo, _
                                 olSubject:=strSubject, _
                                 olEMailBody:=strEMailBody, _
                                 olDisplay:=True, _
                                 olCCLine:=strCCLine, _
                                 olBCCLine:=strBCCLine, _
                                 olOnBehalfOf:=strOnBehalfOf, _
                                 olAtchs:=strAtchs, _
                                 SendAsHTML:=False)
 
                Call .MoveNext
            Loop
        End If
        Call .Close
    End With
    db.Close
    Set rst = Nothing
    Set db = Nothing
 
EH:
    Set rst = Nothing
    Set db = Nothing
    Call MsgBox(Prompt:="There was an Error", _
                Buttons:=vbOKOnly, _
                Title:="Call teh DBA!")
End Sub

Hope this hepps!