I've searched for solutions on emailing from Access and cannot find a solution

[Vetgeorge]

I read countless threads about emailing from Access and tried some of the methods. However, I haven't found a thread that fits the criteria I would like
I now know I need to loop through a recordset of email addresses to get the multiple addresses into the "To:" field in Outlook.

I have a report and would like that one report sent as a PDF attachment to multiple recipients based on the query that is the source of the report. So, only people listed in the report will get an email. I would like to use the docmd sendobject method, since I have used it before for simpler emailing at work (one recipient), and I know it works for everyone in the office.

here is the code I have currently on the command button in the report. It works, but leaves the To field in Outlook blank. I would like the email addresses inserted automatically from the query

Private Sub cmdEmail_Click()
On Error GoTo cmdEmail_Click_Err

DoCmd.SendObject acReport, "rptPendingResolution", "PDFFormat(*.pdf)", "", "", "", "Pending Resolutions requiring your attention", "Please see attachment", True, ""

cmdEmail_Click_Exit:
Exit Sub


cmdEmail_Click_Err:
MsgBox Error$


Resume cmdEmail_Click_Exit

End Sub


I can post a simple version of the database if necessary.

[orange]

See this post by burrina it may be helpful https://www.accessforums.net/showthr...ple+recipients

[June7]

You might want to use BCC for multiple emails. Depends on your recipients. Should they be able to see each other's email address?


Fairly simple to open recordset object, loop records, concatenate address field. The trick is reproducing the report filter.

How are you filtering the report records? Do users enter criteria into controls on form? Is there a query object with dynamic parameters (I never user dynamic parameterized query)?

Something like:

Dim rs As DAO.Recordset
Dim strAdd As String
Set rs = CurrentDb.OpenRecordset("SELECT Email FROM Employees WHERE DepartmentID =" & Me.cbxDept)
While Not rs.EOF
    strAdd = strAdd & rs!Email & ";"
    rs.MoveNext
Wend
strAdd = Left(strAdd, Len(strAdd)-1)

[Vetgeorge]

I've seen samples like that which requires someone to manually add addresses. I would rather make it more "automated," since people could forget to add someone to the list or add someone that doesn't need to see the attachment. I had thought of trying something like that as a solution.

[Vetgeorge]

It doesnt matter if the recipients see the address of others, they all work in the same department. My query for the report is straightforward and doesn't require any input from the users. I have a table for the events (various fields for input), and other tables for comboboxes (not related to any email function), and a table for the user (name/email address)
Report:

query for report

another view query for report

subquery from report query to group email addresses

another view of the subquery

[Vetgeorge]

with fake names, made up email addresses

[June7]

Before I download db, have you attempted code as I suggested?

[Vetgeorge]

Let me preface my answer with "I'm a vb amateur" I tried your example, but I am getting an error:

So then I tried changing the line to:
Set rs = CurrentDb.OpenRecordset("SELECT Email FROM tblUsers WHERE UserID =" &

which got rid of the error. Now, how would I incorporate this into my existing code:
DoCmd.SendObject acReport, "rptPendingResolution", "PDFFormat(*.pdf)", "", "", "", "Pending Resolutions requiring your attention", "Please see attachment", True, ""

[Vetgeorge]

Update, after further searching, I found this post in Programming: https://www.accessforums.net/showthr...0send%20object

His post marked as solved, so I modified his code since it was exactly my scenario which is to email one pdf attachment to multiple recipients:

Sub cmdMail_Click()

Dim rs As Recordset
        Set rs = CurrentDb.OpenRecordset("select [Email] from qryReportEmail")
        If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do Until rs.EOF
            If IsNull(rs![Email]) Then
            rs.MoveNext
            Else
                strAddresses = rs![Email] & ";" & strAddresses
                rs.MoveNext
        End If
        Loop
        End If


DoCmd.SendObject acSendReport, "qryPendingResolution", "PDFFormat (*.pdf)", strAddresses, "", "", "Pending Resolutions requiring your attention", "Please see attachment", True, ""


End Sub

But when I try it, I get a Runtime error 2059, Access cannot find the object '|1' What am I missing??

[Vetgeorge]

The error was coming from the incorrectly named report in my doCmd line. Once I fixed it, it worked!
The addresses listed in the report are inserted


Here is the code:

Sub cmdMail_Click()On Error GoTo cmdEmail_Click_Err
Dim rs As Recordset
        Set rs = CurrentDb.OpenRecordset("select [Email] from qryReportEmail")
        If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do Until rs.EOF
            If IsNull(rs![Email]) Then
            rs.MoveNext
            Else
                strAddresses = rs![Email] & ";" & strAddresses
                rs.MoveNext
        End If
        Loop
        End If


DoCmd.SendObject acSendReport, "rptPendingResolution", "PDFFormat (*.pdf)", strAddresses, "", "", "Pending Resolutions requiring your attention", "Please see attachment", True, ""


cmdEmail_Click_Exit:
    Exit Sub


cmdEmail_Click_Err:
    MsgBox Error$
    Resume cmdEmail_Click_Exit
End Sub

[orange]

Glad you have it resolved and that you posted your working code.

[June7]

And sorry for typo in my suggested code. Now edited.


However, can simplify the code by only retrieving records that have an email address:

Set rs = CurrentDb.OpenRecordset("SELECT [Email] FROM qryReportEmail WHERE NOT [Email] Is Null")