How to use SendObject Macro

[jmofstead]

Hello all,
I am attempting to create a SendObject Macro that sends an email to managers who have not completed certain assignments.
I run a query that pulls out those managers and their email addresses and create a report with that information.
How can I get the To action arguments to pull from the query/report to obtain those managers email addresses and not have a set distribution list.

Thanks in advance!
Julie

 

[boblarson]

Julie:

You'll probably want to jump into VBA for this. You can then iterate through a recordset of the email addresses and send the reports.

Let us know if you are willing to "take the plunge" so-to-speak and we'll do our best to help with that.

 

[jmofstead]

That's what I thought.
I would appreciate the help tremendously.
I've looked online at this forum and the codes aren't exactly what I want since I need to have the addresses pulled from the Query/Report. I can also turn the query into a "make table" query if that would be best.
Bob, your help would be great.
BTW...How's the weather in Portland? I used to live there until I was relocated to Utah. Boy do I really miss Portland, it's the best city!

 

[boblarson]

Okay, so if you have a query that pulls the managers' email addresses, and you can limit the report to just their stuff by using a Where Clause in the SendObject code.

So, I'm going to make up a couple of things since I don't have your actual names of objects.

I'll call the query you have to get their names and email addresses "qryMgrNames" and then the report I will call "rptMgrAssignments" and the field that has their name in it I will call "MgrName" (hopefully you will see what I'm doing and can replace things with your actual names of the objects)

Dim rst As DAO.Recordset
Dim strEmail As String

Set rst = CurrentDb.OpenRecordset("qryMgrNames")

With rst
   Do Until .EOF
       strEmail = !MgrEmail
       DoCmd.SendObject acSendReport, "rptMgrAssignments", acFormatXLS, _
strEmail, , , "Assignments to Complete", "The attached report shows assignments you have yet to complete", False
      .MoveNext
   Loop
.Close
End With

Set rst = Nothing

Also, I chose acFormatXLS but if you want a snapshot it would be acFormatSNP.

Let me know if you need any other assistance.

 

[boblarson]

Oops! Error - Error
I forgot to limit the report just that person's stuff.

The SendObject does not have the ability I previously said it did.

We would need to modify the QueryDef first. So, hang on a moment and I'll get that included.

 

[jmofstead]

Cool
Thanks again

 

[boblarson]

Okay, first of all go here:

http://www.jstreettech.com/files/basJStreetSQLTools.zip

and download that zip file. Unzip it and then import the module basSQLTools, from that file that MVP Armen Stein so graciously provides, into your database.

That will make replacing the Where Clause of your query real easy.

Second, here is the code:

Function SendEmails()
    Dim rst As DAO.Recordset
    Dim strEmail As String
    Dim qdf As QueryDef

    Set rst = CurrentDb.OpenRecordset("qryMgrNames")
    Set qdf = CurrentDb.QueryDefs("YourReportQueryNameHere")

    With rst
        Do Until .EOF
            strEmail = !MgrEmail
            qdf.SQL = ReplaceWhereClause(qdf.SQL, "[ManagerName]=" & Chr(34) & !ManagerName & Chr(34))
            qdf.Close
            DoCmd.SendObject acSendReport, "rptMgrAssignments", acFormatXLS, _
                             strEmail, , , "Assignments to Complete", "The attached report shows assignments you have yet to complete", False
            .MoveNext
        Loop
        .Close
    End With

    Set qdf = Nothing
    Set rst = Nothing


End Function

And this part ( !ManagerName ) refers to the field in your query for the list of managers and their email addresses. I am using this because I don't know what you've called everything. So, when it says
[ManagerName] = Chr(34) & !ManagerName & Chr(34)

it is looking to change the criteria for the report which in my example has a field called ManagerName and then so does the query we are using for the recordset to get each email address.

I hope that is not too confusing.

 

[jmofstead]

Thanks Bob,
I'll try to work on this ASAP. I have a quick question, the code Armen Stein wrote, do I need to replace any elements, or is it a general code? (sorry if this is a stupid question)
And your code, Do I just add this to the bottom of the code by Armen Stein?
Thanks!
Julie

 

[boblarson]

Thanks Bob,
I'll try to work on this ASAP. I have a quick question, the code Armen Stein wrote, do I need to replace any elements, or is it a general code? (sorry if this is a stupid question)
And your code, Do I just add this to the bottom of the code by Armen Stein?
Thanks!
Julie

It is generic code that Armen wrote so you need not make any changes to it.

I would put the function I wrote into a DIFFERENT Standard Module to keep the items separate.

 

[jmofstead]

Okay Bob, thank you for your patience, I have made the appropriate changes to your code you gave me and I go into create a new macro and choose "RunCode"
I then select the function from Armen Stein, which I relabeled Email Test and I open up that function and it gives me four choices "ModifyWhereClause", "ReplaceHavingClause", "ReplaceOrderByClause" and "ReplaceWhereClause". So I select ReplaceWhereClause like you said above,
Here is what it looks like so far
ReplaceWhereClause («varSQL», «varNewWHERE»)
So then do I put function you gave me into the Parenthesis or outside of the parenthesis or do I create a new line in the Macro?
Thanks
Julie