How to create a macro in Access

While it is true that Access macros are simpler than VBA to create, it turns out that sometimes they are so simple they are just not up to the task required. And that's why we need to learn how to create procedures in VBA.

You're going to get great assistance from DBG, so I'd highly recommend following his lead here.

Ok, I will

Since you want to send out multiple emails one by one, you will need to use a "loop," and I am not sure how to accomplish that using macros. It might still be possible, but I'll have to give it a try. Are you able to share a sample db for testing?

it will take me a little bit to do that. but sure

DBG,

I used coplot and got this.

Sub EmailCustomerStatements()

    Dim rs As DAO.Recordset

    Dim strCustomerID As String, strCompany As String, strCity As String, strState As String, strEmail As String

    Dim strPDFPath As String

    Dim olApp As Object, olMail As Object

    Dim strSubject As String, strBody As String

     ' Open recordset

    Set rs = CurrentDb.OpenRecordset("SELECT CustomerID, CompanyName, City, State, [A/PEmail] FROM CustomerStatements WHERE [A/PEmail] Is Not Null")

   ' Create Outlook instance

    Set olApp = CreateObject("Outlook.Application")

 Do While Not rs.EOF

        strCustomerID = rs!CustomerID

        strCompany = rs!CompanyName

        strCity = rs!City

        strState = rs!State

        strEmail = rs![A/PEmail]

  ' Export report to PDF

        strPDFPath = "C:\Temp\Statements" & strCustomerID & ".pdf"

        DoCmd.OpenReport "rptCustomerStatement", acViewPreview, , "CustomerID='" & strCustomerID & "'"

        DoCmd.OutputTo acOutputReport, "rptCustomerStatement", acFormatPDF, strPDFPath, False

        DoCmd.Close acReport, "rptCustomerStatement"

 ' Create and send email

        Set olMail = olApp.CreateItem(0)

        strSubject = "Statement for " & strCompany

        strBody = "Dear Accounts Payable," & vbCrLf & vbCrLf & _

                  "Attached is the latest statement for your account (" & strCustomerID & ")." & vbCrLf & _

                  "Location: " & strCity & ", " & strState & vbCrLf & vbCrLf & _

                  "Please let us know if you have any questions." & vbCrLf & vbCrLf & _

                  "Best regards," & vbCrLf & "Your Company Name"

  With olMail

            .To = strEmail

            .Subject = strSubject

            .Body = strBody

            .Attachments.Add strPDFPath

            .Send

        End With

  rs.MoveNext

    Loop

  rs.Close

    Set rs = Nothing

    Set olMail = Nothing

    Set olApp = Nothing

 MsgBox "All statements have been emailed.", vbInformation

End Sub

But I'm not able to make it work for some reason:

Can we make this work? I'm not sure how to add VBA

That's VBA. I thought you said you wanted to stick with macros. In any case, to make sure it works, I might still need to see a sample file. Otherwise, what was the error message you were getting with this code? Maybe we can troubleshoot it together.