I've got the attached query, which shows several rows (there is more data) but only 2 email address. I would like to send the corresponding rows to their relevant email address. All these details change every week, and there will be more (and sometimes less) email addresses, so I need to keep the data coming from this form (rather than static addresses). I've tried making a report, and can manage to send that to the email address, but I get multiple forms/emails for this report. The query will show a lot of records, so this is not an acceptable solution.
Any help would be gratefully received.
EDIT - I've attached the tables which show the data. I just want to send each company their own few rows by any means possible. Please help :)
My Full code:
Dim rS As DAO.Recordset Dim dbS As DAO.Database Dim Filepath As String Dim Folderpath As String Dim oOutlook As Outlook.Application Dim oEmailItem As MailItem Set dbS = CurrentDb() Set rS = dbS.OpenRecordset("Select DISTINCT email, [CompName], [MainContact] FROM q66NonConfirmedTimesheets") Do While Not rS.EOF myemail = rS!email mycompname = rS!CompName myMainContact = rS!MainContact 'My Email the report Folderpath = "C:\Reports\" Folderpath = Folderpath & "WeeklyTimesheet, " & [mycompname] & " - " & Format(date, "dd mmm yyyy") & ".pdf" DoCmd.OpenReport "Rpt01UnconfirmedTimesheets", acViewPreview, , "email = '" & myemail & "'" DoCmd.OutputTo acOutputReport, , "PDFFormat(*.pdf)", Folderpath, False DoCmd.Close acReport, "Rpt01UnconfirmedTimesheets" If oOutlook Is Nothing Then Set oOutlook = New Outlook.Application End If Set oEmailItem = oOutlook.CreateItem(olMailItem) With oEmailItem .to = [myemail] .Subject = "Unconfirmed Timesheets" .Body = "Automatic email from my database" .Attachments.Add Folderpath .Display End With 'End of my emailing report rS.MoveNext Loop Set oEmailItem = Nothing Set oOutlook = Nothing Set rS = Nothing Set dbS = Nothing End Sub
and I've added into my "Onload" event on the Report:
Me.Filter = "[email]='" & myemail & "'"