Email Party list of records associated to them

[Tonesterman]

I am attempting to write some VB code that will look at a table and extract the following elements, Associate Email, Associates Name, Reference Number, Amount, Days outstanding, and Managers Email, from a table. Then I would like access to send an email to the Associate with a CC to the Manager. I would like the email to define all of Reference Numbers, Amounts, and Days Outstanding and just populate it in the email. For Example
To: Joe@someplace.com
CC: JoesManager@someplace.com
Subject: Notification of outstanding
Body:
Dear Joe,
The following is outstanding:
Reference Amount Outstanding
*****************************************
ABCD 25.00 10
CDE 45.00 15
DIDID 15.00 10

Then send the next email to the next party in the table and their manager defining their reference numbers.

To: Sam@someplace.com
CC: SamsManager@someplace.com
Subject: Notification of outstanding
Body:
Dear Sam,
The following is outstanding:
Reference Amount Outstanding
*****************************************
IJUK 25.00 10
HHHH 45.00 15
EEEEE 15.00 10

Etc....

Also I would like the email to state another party as the sender (if possible)

Can this be done?

Please note my knowledge of VB is very basic, I have looked on here lots and thanks to another post I can create the email but am unable to send all of the transactions associated to them, it will only do one email per transaction.

Here is the code that I have found on here that has helped me so far:

Private Sub Command12_Click()
On Error GoTo Err_Command12_Click

Dim email As String
Dim body As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rec As DAO.Recordset

Set dbs = CurrentDb
strSQL = "SELECT * FROM Query1"

Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rs.EOF

body = "Dear " & rec![name] & "," & Chr(10)
body = body & "The following is outstanding:" & Chr(10)
body = body & "Reference Number Amount Outstanding" & Chr(10)
body = body & "*****************************************************************************" & Chr(10)
body = body & rec![ref] & " " & rec![outstandingamount] & " " & rec![outstandingdate] & Chr(10)

email = rec!
manager = rec![manager]
DoCmd.SendObject acSendNoObject, , , email, manager, , "Notification of outstanding", body, True
rec.MoveNext

Loop

rec.Close
Set rec = Nothing

Exit_Command12_Click:

Err_Command12_Click:
End Sub

 

[Remou]

You may find it best to use DoCmd.SenObject and a report. Anything else is likely to require a bit of coding.

Access is VBA, not VB.

 

[Tonesterman]

Remou,

Thanks so much for responding. Sorry about the VBA, thought I had put that A in there.

Withe the DoCmd.SendObject doesn't that send it as an attachment? I would prefer that the data not be presented in attachment form but body of the email.

 

[Remou]

You can certainly do what you show, but it will require some coding. You will need to add a second recordset to the code you show above to loop through the transaction table. Very roughly:

Set dbs = CurrentDb
strSQL = "SELECT * FROM Query1"

Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rs.EOF

    email = rec![email]
    Manager = rec![Manager]
    
    Body = "Dear " & rec![Name] & "," & Chr(10)
    Body = Body & "The following is outstanding:" & Chr(10)
    
    strSQL2 = "Select * From transactions where ID=" & rec!ID
    Set rec2 = dbs.OpenRecordset(strSQL2, dbOpenDynaset)
    
    Body = Body & "Reference Number         Amount                         Outstanding" & Chr(10)
    Body = Body & "*****************************************************************************" & Chr(10)
    
    Do While Not rec2.EOF
        Body = Body & rec![ref] & "                    " & rec![outstandingamount] & "                          " & rec![outstandingdate] & Chr(10)
        rec2.MoveNext
    Loop
    
    DoCmd.SendObject acSendNoObject, , , email, Manager, , "Notification of outstanding", Body, True
    rec.MoveNext
 
Loop

I think you will need to use Outlook or CDO to send as someone else.