CODEWITHSUNDEEP
databasewindowsvbams-accessemail
Chris Music
Chris Music

Reputation: 109

Access query to email

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 :)

Table Layout

Thanks.

Querydetails

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 & "'"

Upvotes: 0

Views: 2215

Answers (2)

AVG
AVG

Reputation: 1462

You need to open a recordset in VBA that contains the unique email addresses. Loop through the recordset, opening your report, filtering on the email address and emailing the report. Air code:

Dim rS as DAO.Recordset
Dim dbS as DAO.Database
Set dbS = CurrentDb()
Set rS = dbS.OpenRecordset("Select DISTINCT emailaddress FROM <yourquery>",DbOpenSnapshot)
Do While Not rS.EOF
<sometempvar> = rS!emailaddress
'your code to email filtered report using <sometempvar>'
rS.MoveNext
Loop
Set rS = Nothing
Set dbS = Nothing

Upvotes: 1

luk2302
luk2302

Reputation: 57114

You need to create a query to only return the emails and specify the query to return unique values only, there is an option for that in the query editor:

enter image description here

Upvotes: 0

Related Questions