Need VBA for sending email from query result by recordset

[twinnyfo]

Do you have queries (or tables) names "distinct Email" and "EMail"? The only reason those names are there is because I have to make assumptions about the names of your data, as you have not provided it.

Concerning Post #14, above, you must build the code to create the body of the multiple lined e-mails. I can't even begin to build it since I don't know the data that is in your tables--nor do I want to. These are things that we request our posters to work through themselves. We are glad to assist with trouble-shooting, but I want you to understand the concepts I am presenting (even more than I want your project to work). If you understand the concepts, you will be able to expand on those concepts and build more robust projects in the future.

[aflores41]

Yes for the first question. I did upload the file from the initial inquiry however, i'll reattach it and rename the queries. The query for distinct is [qry_email distinct] and query for email is [qry_email].

I really hope to get this code to work. I'm researching as well and keep hitting a wall.

Thank you so much for being patient with me.

[twinnyfo]

Just modify the code provided so that the recordsets are drawing their data from YOUR queries. This should at least get us moving in the right direction.

[aflores41]

I did modify the code

Public Function CreateRIT_ReportEmail()
    Dim OlApp As Object
    Dim OlMail As Object
    Dim ToRecipient As String
    Dim db As DAO.Database
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strSQL As String
 
 
 
    'rst1 is the distinct email addresses
    Set db = CurrentDb
    Set rst1 = db.OpenRecordset("qry_Email distinct")
    If Not rst1.RecordCount = 0 Then
        rst1.MoveFirst
        Do While Not rst1.EOF
            Set OlMail = OlApp.CreateItem(olMailItem)
            OlMail.To = rst1!Email
            OlMail.Subject = rst1![Account Number (Short)]
            strSQL = "SELECT * FROM qry_EMAIL WHERE [email] = '" & rst1![Email] & "'"
            Set rst2 = db.OpenRecordset(strSQL, dbOpenDynaset)
            If Not rst2.RecordCount = 0 Then
                rst2.MoveFirst
                Do While Not rst2.EOF
                    'Build the body of your message here
                    rst2.MoveNext
                Loop
            End If
            rst2.Close
            Set rst2 = Nothing
            'Send your e-mail here
            rst1.MoveNext
        Loop
    End If
    rst1.Close
    db.Close
    Set rst1 = Nothing
    Set db = Nothing
End Function

error came out for line 15 as run-time error '91':
object variable or with block variable not set.

[twinnyfo]

Do you have the lines:

Option Compare Database
Option Explicit

At the top of your modules? If not, place them there. Then debug your code (Debug Menu, Compile). Then fix any blatant errors.

Also, you may want to change line 14:

Set rst1 = db.OpenRecordset("qry_Email distinct", dbOpenDynaset)

It "shouldn't" make a difference, but it might.

[aflores41]

Error on line 23,
error code: run-time error '91':
object variable or with block variable not set.

Option Compare Database
Option Explicit


Public Function CreateRIT_ReportEmail()
    Dim OlApp As Object
    Dim OlMail As Object
    Dim olMailItem As Object
    Dim ToRecipient As String
    Dim db As DAO.Database
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strSQL As String
 
 
 
    'rst1 is the distinct email addresses
    Set db = CurrentDb
    Set rst1 = db.OpenRecordset("qry_Email distinct", dbOpenDynaset)
    If Not rst1.RecordCount = 0 Then
        rst1.MoveFirst
        Do While Not rst1.EOF
            Set OlMail = OlApp.CreateItem(olMailItem)
            OlMail.To = rst1!Email
            OlMail.Subject = rst1![Account Number (Short)]
            strSQL = "SELECT * FROM qry_EMAIL WHERE [email] = '" & rst1![Email] & "'"
            Set rst2 = db.OpenRecordset(strSQL, dbOpenDynaset)
            If Not rst2.RecordCount = 0 Then
                rst2.MoveFirst
                Do While Not rst2.EOF
                    'Build the body of your message here
                    rst2.MoveNext
                Loop
            End If
            rst2.Close
            Set rst2 = Nothing
            'Send your e-mail here
            rst1.MoveNext
        Loop
    End If
    rst1.Close
    db.Close
    Set rst1 = Nothing
    Set db = Nothing
End Function

I added Dim olMailItem As Object, thinking it might fix the problem but didn't.

[aflores41]

I might have found a work around but I might need some ideas with this. I was thinking of creating subdatasheet to group the records by email. Question is how do I send the subdatasheet records that's under the email when expanded?

Thanks. Still the same error from the above code.

[Rabbit]

Line 23, OlApp, you never set that to anything. It has nothing to work on. You may have declared the variable. But the variable isn't set to anything.

[aflores41]

I've set OlApp, however OlMail errors out on line 16. Here's the code below.
error is run-time error 13, type mismatch.
olapp is set in line 10.
Thank you.

Public Function CreateRIT_ReportEmail()
    Dim OlApp As Object
    Dim OlMail As Object
    Dim olMailItem As Object
    Dim ToRecipient As String
    Dim db As DAO.Database
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim strSQL As String
    Set OlApp = CreateObject("Outlook.Application")
    Set db = CurrentDb
    Set rst1 = db.OpenRecordset("qry_Email distinct", dbOpenDynaset)
    If Not rst1.RecordCount = 0 Then
        rst1.MoveFirst
        Do While Not rst1.EOF
            Set OlMail = OlApp.CreateItem(olMailItem)
            OlMail.To = rst1!Email
            OlMail.Subject = rst1![Account Number (Short)]
            strSQL = "SELECT * FROM qry_EMAIL WHERE [email] = '" & rst1![Email] & "'"
            Set rst2 = db.OpenRecordset(strSQL, dbOpenDynaset)
            If Not rst2.RecordCount = 0 Then
                rst2.MoveFirst
                Do While Not rst2.EOF
                    'Build the body of your message here
                    rst2.MoveNext
                Loop
            End If
            rst2.Close
            Set rst2 = Nothing
            'Send your e-mail here
            rst1.MoveNext
        Loop
    End If
    rst1.Close
    db.Close
    Set rst1 = Nothing
    Set db = Nothing
End Function

Also, your name was mentioned before on the previous post and was wondering if you could help me.

The goal is really this:
The macro to send 3 emails rather than 4

For example:

Column1 Column2 Column3 Column4 Column5 Column6
123 John Doe $12345 $54321 af@af.com
124 Jane Doe $54321 $12345 af@af.com
125 Jane Jane $55555 $22222 mx@mx.com
126 Joe Joe $12321 $23232 gs@gs.com

So the goal is the macro would send an email to the following:
af@af.com
Body:
message...:
123 John Doe $12345 $54321
124 Jane Doe $54321 $12345

signature
aklsfhasklfh

then another email sent to
mx@mx.com
body:
message...:
125 Jane Jane $55555 $22222
signature
asfdasfa

then next and so on...

Thank you!


- I'm playing around with subdatasheet and trying to figure out if I could email the subdatasheet of a query but haven't found anything so far.

[Rabbit]

olMailItem is an object that you declared. The CreateItem method expects an integer, not an object.

[twinnyfo]

Delete line 4 and line 16 should then work.

[Rabbit]

You need line 16, you just need to find out what the constant is mapped to and put that in because the outlook constants are out of scope.

[zmbd]

read this: Bulk-Batch Email From Microsoft Access

The sendkey workaround... yuck.
Once the email object is created it can be sent using the send property. refer to the article.

[aflores41]

Thanks zmbd. Hopefully, I could understand the coding and alter it to my use. I wanted to send the concatnated records as a group by to one email address though but I guess I could try and do it manually. I was hoping there was a code out there that would do this automatically. I'll post updates if any.

[zmbd]

should be able to do that... let me re-read the thread here and see what hits me, been a madhouse on my end and very little time to accomplish anything without interruptions..