SendEmail Code having problems...

[ItsMe]

So we are not looping yet. We are going to hard code in an email address to send TO. Edit the
strEmail = "MyEmail@Email.com"
to match a test email you can monitor and see what the report looks like. Make sure the email looks the way tou want.

Dim strEmail As String
Dim lngPK As Long
Dim strName As String
Dim strCriteria As String
Dim strDate As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Q_WeeklyEmployeeDispatch_Today", dbOpenDynaset)
strDate = "#10/13/2013#"
strCriteria = "[Date]=" & strDate
strEmail = "MyEmail@Email.com"
    rs.FindFirst strCriteria
    
        If Not rs.EOF Then
        DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , strCriteria, acHidden
        DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, strEmail, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
        DoCmd.Close acReport, "R_WeeklyDispatch_Today"
        
        Else
        MsgBox "No record Found"
        
        End If
    rs.Close
    
Set rs = Nothing

[ItsMe]

I notice that we don't have email sending as well. How I send the report based on the email field in the T_Inspectors table? Based on this query.

We may have to run two loops. Have them nested......
Lets test the hard coded version first. Then we can look at the query and adjust the criteria as needed.

[wnicole]

Worked Perfect! Sent all 4 jobs based on query Date() to my email address. So how do we pull it from the Table T_Inspectors?

[ItsMe]

So I still don't understand how you need this email formatted. I understand PDF. Does each employee get the same report or do you need a special report for each individual?

If each employee gets the same report, then the report is what the query is? Everything relative to the date specified is what the report consists of? Then it is just a matter of looping through all of the employee email addresses?

[wnicole]

Yes they get the same report with their individual job information.
But its not just one date set as we have it in the code. The query I have pull the date.
I just dont know what to remove from the code (testing part)?

[wnicole]

Its not a set email address as we have in code either...but need to pull from T_Inspectors table to get the email. This code is new for me so I'm not sure how to replace? Sorry.

[ItsMe]

Well don't get too anxious. Is everything you need in the one query? Is there anything in table T_Inspectors that is not in the query?

I am looking at your original code and can't understand what the report looks like. If you select a specific date and a specific Employee id wil that create the apropriate Reprt/Email

SELECT ID1 and Job Date FROM query then send to email address Email@soandSO

[wnicole]

Yes...I dont have the T_Inspectors connected to the query. So I have to pull from that table to get the email address. Thats the only thing.
The report looks like this. Where would I put the code to do this. I will be heading off to sleep but will check back first thing in the morning. I really appreciate you taking the time with me on this one.

[wnicole]

Keep in mind the query sets the Date() so I think I would need to remove it the 10/1/2013 from code correct?
Thanks

[ItsMe]

Still not quite sure, but this loops through each record in T_Inspectors and grabs the email address. This will be the TO address. No search criteria.

Then the report opens to the FULL query. No filtered results. No criteria

Dim strCriteria As String
Dim strDate As String
Dim strEmail As String
'strDate = "#10/13/2013#" 'we need to retreive this from a control on a form
strCriteria = "[Date]=" & strDate   'This is what will generate the PDF. It only matches the date and not the Employee ID1
'DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, ,strDate , acHidden  'THis depends on a date assigned to strDate
DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , , acHidden  'This depends on the query to select the correct date and report
Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("T_Inspectors", dbOpenDynaset) 'We need an email address
        rs.MoveFirst
        
            Do While Not rs.EOF 'Loop through every record in T_Inspectors and retreive emails
            strEmail = rs![Email]
            DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, strEmail, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
            
        rs.MoveNext
            
            Loop
            
    rs.Close
    
    Set rs = Nothing
DoCmd.Close acReport, "R_WeeklyDispatch_Today"

[wnicole]

Hi...I placed the code below. Everything is works (pull the right report, has the right Employee associated)
The only thing missing is the email address is empty in Outlook. It not pulling the email address. Can you take a look to make sure the email part is correct?
Needs to pull the email address from the T_Inspectors table.

Private Sub Command5_Click()

Dim strCriteria As String
Dim strDate As String
Dim strEmail As String

strCriteria = "[Date]=" & strDate

DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , , acHidden
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("T_Inspectors", dbOpenDynaset) 'We need an email address
rs.MoveFirst

Do While Not rs.EOF 'Loop through every record in T_Inspectors and retreive emails

DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Employee]='" & rs![Last Name] & "'"

DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, strEmail, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True

rs.MoveNext

Loop

rs.Close

Set rs = Nothing
DoCmd.Close acReport, "R_WeeklyDispatch_Today"





End Sub

[ItsMe]

Why did you leave that part out?

[wnicole]

I didnt know if you were available for my question because the code still send it to the first email in the Inspectors list and not the email address associated to the query.
I just put your code back exactly as above.
Everything works. The right report and everything however, it is still pulling up the wrong email address.

I just don't know if the code is pulling from the table. Jay...@gmail is the first email address in the T_Inspectors table however, he has nothing to do with the jobs on 10/14/2014. It should come to me.

Any ideas?

[ItsMe]

No, I have no more ideas. Creating databases is not a big problem for me. However, understanding what it is you are trying to accomplish is not something I am able to do.

I tried to walk through it with you to no avail. The last code provided goes through each record in the table. You will need to figure out a way to only grab the emails you need.

[wnicole]

I really appreciate your time...you have been so helpful. I'll try to figure out this last leg...thanks so much!