NearImpossible
Registered User.
- Local time
- Yesterday, 19:03
- Joined
- Jul 12, 2019
- Messages
- 225
I have a query that builds a table called FacilityMXEmail and then I search for day/week validation and if that is true, I need to send an email to all contacts that match. What I currently have is only generating an email for the first record.
Table Sample Below:
Code:
Private Sub EmailTest_Click()
Dim rs As Recordset
Dim s As String
Dim t As String
Dim e As String
Dim n As String
Dim oOutlook As Object
Dim oEmailItem As Object
DoCmd.OpenQuery "FacilityMXEmails"
Set rs = CurrentDb.OpenRecordset("Select * from [FacilityMXEmail]'", dbOpenDynaset, dbSeeChanges)
While Not rs.EOF
s = s & rs("[Week]") & " " & rs("[Day]")
If s = TodayIsNthDay(Date) Then
MsgBox "Generating Email"
n = n & rs("[Facility Name]")
e = e & rs("[Email]")
t = t & rs("[Time]")
'Opens and starts email
Set oOutlook = CreateObject("Outlook.application")
Set oEmailItem = oOutlook.CreateItem(olMailItem)
With oEmailItem
.To = e
.Subject = n & " Scheduled Maintenance"
.HTMLBody = "This is a reminder that the Maintenance is scheduled for tomorrow at " & t & "<br><br> If you need to reschedule, please reply to this email and let us know <br><br> Thank You"
.display ""
End With
'Clears Email settings
Set oEmailItem = Nothing
Set oOutlook = Nothing
End If
rs.MoveNext
Wend
End Sub
Table Sample Below:
FacilityName | Week | Day | Time | |
Facility 1 | test@mail.com | 4th | Friday | 1am |
Facility 1 | test2@mail.com | 4th | Friday | 1am |
Facility 3 | test3@mail.com | 4th | Friday | 12pm |
Facility 3 | test4@mail.com | 4th | Friday | 12pm |
Facility 3 | test5@mail.com | 4th | Friday | 12pm |
Last edited: