Method for email each analysts ONLY their items...

[redbull]

Alright. So I have an application that looks at a table... it cycles thru each record set, then performs some IE automation to generate a new URL for each client.. Historically I have emailed the finished table to a distribution list... They did not like this and now want each record emailed directly to the analysts email found on the table... So this is where I need help...

Public sub Loops()
Dim WebFrm As InternetExplorer
Dim Number, Name As String
Dim r As DAO.Recordset
 
 
strReportName = "21 Day Installs"
strOutputFormat = ".xls"
strMessageSubject = "Interface Clients 21 Days Completed"
Set r = CurrentDb.OpenRecordset("Select * from tbl_name_we_want") ' Query or Table records to loop thru
  'Check to see if the recordset actually contains rows
     If Not (r.EOF And r.BOF) Then
       r.MoveFirst
     r.Edit
   Do Until r.EOF = True
'Start code for each record to do here
 
Set WebFrm = Login("website we are doing automation at.com", False)
  Call SleepIE(WebFrm) ' Call IE sleep to allow pageload time
    WebFrm.Document.getelementbyID("name").Value = r![Client Number] & " - " & r![Client Name] ' enters acct# and name with " - "   'Just fills out the webform online with record data
      PauseApp 1 ' Hard pause because Java is a pain and does not send ready status to IE.
        WebFrm.Navigate "javascript:doSubmit(encode())"  ' RUn this Javascript
          PauseApp 1  'Pause for page load, JS does not display ready states so Sleep does not work here
            r.Edit
          r![URLCreated] = WebFrm.Document.all.Item("result").Value  'Put the newly created URL into the r.
          r![DateUrlCreated] = Date  ' Put in todays date into this column as well.
        r.Update
                                                     '''''''Above works great, below I am trying to utilize r. to email each analyst as part of this loop.....      
    strAnalystName = r!Analyst
    strAnalystEmail = r!Email
    Forms!frm_IA_email!txtIA = strAnalystName   ' This is a form that kind of acts as a translation table, not 100% sure it is needed any longer but it seems to work...
   
    strMessageText = "This is the mail body that was completed for: " & strAnalystName
    strMessageSubject = strMessageSubject & " - " & strAnalystName
   
    DoCmd.SendObject acSendReport, strReportName, strOutputFormat, strAnalystEmail, , , strMessageSubject, strMessageText, 0
 
    r.MoveNext
    WebFrm.Quit
   
  Loop
Else
    ' send a specific email or shut that mother down!
End If
 
r.Close
Set r = Nothing
End Sub

Alright so now you have a code breakdown... The only thing I really need at this point is a report that ONLY includes the current record... ORR we could remove this email routine entirely and just email each analysts at the end of the record set...

I ramble horribly...

Goal : To email the the current record to the email address on the current record. This will do what we want, however some people might receive more then email a day if they appear more then once on a list. If johh smith is on the list 3 times today he will receive 3 different emails, each with a different record.

Alternative Goal : At the end of this loop processed, after the table is complete... go thru and email each analysts a group of records that include their email... If John smith is on the list 3 times today, he will receive one email with all 3 records.


Any advice is appreciated.

[Dal Jeanis]

What I'd do is just
(1) clone the report to one which only reports one analyst, taken from a temp variable
(2) load the temp variable with StrAnalystName
(3) there is no three.

[Dal Jeanis]

I'm not sure how the web form code interacts with the report, though. Presumably it's updating the tables that are underlying the reports?

You might need to create a temporary table (or array) for this process, that keeps the info about which analysts are associated with which records. Then the report could use that temp table in generating the single-analyst report.

[redbull]

Yea, the webform stuff is just updating a table with some information I am generating... do you have any examples or anything? I am still pretty lost about this.

[Dal Jeanis]

According to this code, there is a report in the database called "21 Day Installs".

1) Clone that report to a new report "21 Day Installs for One Analyst". Don't change anything else yet.

2) Clone the query underlying the original report and look at how it picks its data. Make sure the tables have the information about which analyst should be getting that part of the report. Presumably, it's getting its data from the same table as this line -> CurrentDb.OpenRecordset("Select * from tbl_name_we_want")

3) If it's all there already, then you may be able to just code a filter onto the existing report. ATTACH THE CLONED QUERY TO THE CLONED REPORT AND DO YOUR TESTING IN THE CLONE. And all this should be in a play copy of the database. You can start by hard-coding the name of an analyst, then when that's working, put the analyst name into a temp variable.

Let us know of any issues you find in cloning the report, or if the identification of the analyst isn't in the tbl_name_we_want.