Solved Email from Record Searches (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 10:29
Joined
Jan 14, 2017
Messages
18,186
dumb da dumb dumb dummmmmmmmb.

first round s = 4th Friday
second round s = 4th Friday4th Friday
third round s = 4th Friday4th Friday4th Friday

Reset the values each round and bingo

I usually set a message box to display these types of things, but didn't this time for some reason!!

thanks for your help !!

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
 

NearImpossible

Registered User.
Local time
Today, 05:29
Joined
Jul 12, 2019
Messages
225
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 !!

Thanks again !!
 

NearImpossible

Registered User.
Local time
Today, 05:29
Joined
Jul 12, 2019
Messages
225
Is there anyway to specify a "From" line in the email code?

This will use the individuals email to send it, however I would like any replies to go to a group email address.

Any thoughts?
 

NearImpossible

Registered User.
Local time
Today, 05:29
Joined
Jul 12, 2019
Messages
225
Is there anyway to specify a "From" line in the email code?

This will use the individuals email to send it, however I would like any replies to go to a group email address.

Any thoughts?

I figured it out,

.SendOnBehalfOfName ="email address"
 

sxschech

Registered User.
Local time
Today, 03:29
Joined
Mar 2, 2010
Messages
791
but for some reason its not playing nice and when I hit F8

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:

how to reverse the function keys on hp
 

NearImpossible

Registered User.
Local time
Today, 05:29
Joined
Jul 12, 2019
Messages
225
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:

how to reverse the function keys on hp

Good catch !!

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 !!
 

zeroaccess

Active member
Local time
Today, 05:29
Joined
Jan 30, 2020
Messages
671
Good catch !!

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 !!
Press F1, F8, or Del early during boot up (depends on brand) to go into the BIOS. There, look for your function key setting.
 

Users who are viewing this thread

Top Bottom