CODEWITHSUNDEEP
sqlvbams-accessoutlookms-access-2010
chris
chris

Reputation: 73

Access 2010/2007 VBA email daily report to companys based on table with email addresses

Basically I need to send a report each day to dealers we disburse funds to. I have a table that has the dealer names, ID number, and their email addresses.

For each report I need to loop through this table and send an email to the corrisponding dealer by ID number.

The Dealer information:

SELECT DealerDetail.dealer_id, DealerDetail.dealer_name, DealerDetail.email
FROM DealerDetail;

The query my report uses:

SELECT LoanDetail.Book_Date, LoanDetail.DLR_Name, 
  LoanDetail.DLR_ID, LoanDetail.Cust_ID, LoanDetail.Amt_Fin, 
  LoanDetail.APR, LoanDetail.Buy_Rate, LoanDetail.Pmt_Amt, 
  LoanDetail.TERM, LoanDetail.Part, LoanDetail.[Flat Fee]
FROM LoanDetail
WHERE (((LoanDetail.Book_Date)=Date()-1));

Upvotes: 0

Views: 2692

Answers (2)

Mattheu McBarnett
Mattheu McBarnett

Reputation: 46

Another way would be to loop through and create an outlook instance to send the email, shown in the code below

Private Function SendEmail(attachment, subject,strContactEmail,strEmailText,strCc)

Dim olLook As Object                     'Start MS Outlook
Dim olNewEmail As Object                 'New email in Outlook


Set olLook = CreateObject("Outlook.Application")
Set olNewEmail = olLook.createitem(0)


   With olNewEmail   'Attach template
      .To = strContactEmail
      .cc = strCc
      .body = strEmailText
      .subject = subject
  .attachments.Add (attachment)
  .display
   End With
 .send


End Function

Hope this helps!

Upvotes: 2

Fionnuala
Fionnuala

Reputation: 91306

One way would be to loop through the dealer table and use SendObject to send the report:

Dim rs AS DAO.Recordset

   Set rs = CurrentDB.OpenRecordset("SELECT email FROM DealerDetail")

   Do While Not rs.EOF
      DoCmd.SendObject acReport, "ReportName", _
           acFormatPDF, rs!Email, , , "Report", "Here is the report", True
      rs.MoveNext
   Loop

Upvotes: 1

Related Questions