Looks like you worked it out whilst I was investigating
Here's my solution:
SQL:
Option Compare Database
'Option Explicit
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
Dim I As Integer
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "FacilityMXEmails"
Set rs = CurrentDb.OpenRecordset("Select * from [FacilityMXEmail]'", dbOpenDynaset, dbSeeChanges)
' rs.MoveLast
' rs.MoveFirst
'Open and start email
Set oOutlook = CreateObject("Outlook.application")
Set oEmailItem = oOutlook.CreateItem(olmailitem)
s = rs![Week] & " " & rs![Day]
If s = TodayIsNthDay(Date) Then
MsgBox "Generating Email"
I = 0
While Not rs.EOF
n = rs![Facility Name]
e = rs!Email
t = rs![Time]
With oEmailItem
Debug.Print n, e, t 'check it works
.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
' .send
End With
I = I + 1
rs.MoveNext
Wend
End If
'Clears Email settings
Set oEmailItem = Nothing
Set oOutlook = Nothing
MsgBox I & " emails have been generated", vbInformation, "Emails completed!"
End Sub
NOTE:
1. I recommend using Debug.Print to check what's happening
2. You have several fields which use reserved words Week, Day, Time. This is a bad idea. Betterto rename as e.g. EWeek, eDay, eTime.
3. Similarly avoid using spaces in field names
4. ALWAYS use Option Explicit. If so, you will see olMailItem isn't defined
Looks like you worked it out whilst I was investigating
Here's my solution:
SQL:
Option Compare Database
'Option Explicit
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
Dim I As Integer
'DoCmd.SetWarnings False
'DoCmd.OpenQuery "FacilityMXEmails"
Set rs = CurrentDb.OpenRecordset("Select * from [FacilityMXEmail]'", dbOpenDynaset, dbSeeChanges)
' rs.MoveLast
' rs.MoveFirst
'Open and start email
Set oOutlook = CreateObject("Outlook.application")
Set oEmailItem = oOutlook.CreateItem(olmailitem)
s = rs![Week] & " " & rs![Day]
If s = TodayIsNthDay(Date) Then
MsgBox "Generating Email"
I = 0
While Not rs.EOF
n = rs![Facility Name]
e = rs!Email
t = rs![Time]
With oEmailItem
Debug.Print n, e, t 'check it works
.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
' .send
End With
I = I + 1
rs.MoveNext
Wend
End If
'Clears Email settings
Set oEmailItem = Nothing
Set oOutlook = Nothing
MsgBox I & " emails have been generated", vbInformation, "Emails completed!"
End Sub
NOTE:
1. I recommend using Debug.Print to check what's happening
2. You have several fields which use reserved words Week, Day, Time. This is a bad idea. Betterto rename as e.g. EWeek, eDay, eTime.
3. Similarly avoid using spaces in field names
4. ALWAYS use Option Explicit. If so, you will see olMailItem isn't defined
Thanks for the feed back and I get not using reserved words as Field Names, this was just one of those things I threw together to get it working. I will rename the table columns on my Production DB.
Also a good idea to state that the process is completed !!
Usually this is caused by the keyboard settings. To temporarily do an F8, or any Function key for that matter, Press the Fn key and keep it held down then press the F8 or whichever function key.
Since that can be annoying after awhile, you might consider reversing/inverting how the function keys work, so that pressing F8 directly will do what we expect in Access, while pressing FN+F8 would then do the other thing it currently is doing.
Because computer manufacturers may have different ways of altering the keyboard mapping, you will probably want to do a search based on your computer's brand. Such as:
Usually this is caused by the keyboard settings. To temporarily do an F8, or any Function key for that matter, Press the Fn key and keep it held down then press the F8 or whichever function key.
Since that can be annoying after awhile, you might consider reversing/inverting how the function keys work, so that pressing F8 directly will do what we expect in Access, while pressing FN+F8 would then do the other thing it currently is doing.
Because computer manufacturers may have different ways of altering the keyboard mapping, you will probably want to do a search based on your computer's brand. Such as:
i'm used to my personal laptop where I can turn the Function Lock on and off so I don't even think about have to hold the Fn key. That was the exact issue here as my work laptop doesn't have the Function Lock !!
i'm used to my personal laptop where I can turn the Function Lock on and off so I don't even think about have to hold the Fn key. That was the exact issue here as my work laptop doesn't have the Function Lock !!