Solved Email from Record Searches (1 Viewer)

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.

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:

FacilityNameEmailWeekDay Time
Facility 1test@mail.com4thFriday1am
Facility 1test2@mail.com4thFriday1am
Facility 3test3@mail.com4thFriday12pm
Facility 3test4@mail.com4thFriday12pm
Facility 3test5@mail.com4thFriday12pm
 
Last edited:

Ranman256

Well-known member
Local time
Yesterday, 20:03
Joined
Apr 9, 2015
Messages
4,339
change .Display ""
to
.display FALSE

see if that continues thru the rst.
 

Ranman256

Well-known member
Local time
Yesterday, 20:03
Joined
Apr 9, 2015
Messages
4,339
oh, where's the
.Send

(after .display)
 

vba_php

Forum Troll
Local time
Yesterday, 19:03
Joined
Oct 6, 2019
Messages
2,884
What I currently have is only generating an email for the first record.
that's probably because you are being sidetracked by an access quirk. try this code:
Code:
rs.movelast
rs.movefirst
While Not rs.EOF
instead of just this:
Code:
While Not rs.EOF
 

NearImpossible

Registered User.
Local time
Yesterday, 19:03
Joined
Jul 12, 2019
Messages
225
that's probably because you are being sidetracked by an access quirk. try this code:
Code:
rs.movelast
rs.movefirst
While Not rs.EOF
instead of just this:
Code:
While Not rs.EOF

same results, just the first record
 

vba_php

Forum Troll
Local time
Yesterday, 19:03
Joined
Oct 6, 2019
Messages
2,884
same results, just the first record
no kidding!? hmmm...your code looks OK to me. care to upload a file for one of us to simulate the problem? but the other thing you should NOT do is destroy the outlook object after every loop. that makes no sense whatsoever. destroy the memory at the END. furthermore, are you absolutely sure that the DISPLAY property isn't the issue? perhaps outlook can only display one message at a time when doing it through code like this? perhaps it's a z-index issue? although that's prolly not the case, it's none-the-less a possibility. office, after all, isn't the greatest software package in the world....
 

NearImpossible

Registered User.
Local time
Yesterday, 19:03
Joined
Jul 12, 2019
Messages
225
not letting me upload a .accdb file, so its in a zip.

I commented out the query and just made the table that it is looking at
 

Attachments

  • EmailTest.zip
    33 KB · Views: 252

vba_php

Forum Troll
Local time
Yesterday, 19:03
Joined
Oct 6, 2019
Messages
2,884
the issue is not with outlook. this is the offending line of code:
Code:
                If s = TodayIsNthDay(Date) Then
every loop after LOOP 1 doesn't get inside the IF statement. you need to check your logic again. see the following image for proof that the first message is indeed generated:

first_email_generated.jpg



break your code appropriately, and step through it, like this:

code_not_looping_correctly.jpg
 

Attachments

  • first_email_generated.jpg
    first_email_generated.jpg
    52.9 KB · Views: 228

NearImpossible

Registered User.
Local time
Yesterday, 19:03
Joined
Jul 12, 2019
Messages
225
no kidding!? hmmm...your code looks OK to me. care to upload a file for one of us to simulate the problem? but the other thing you should NOT do is destroy the outlook object after every loop. that makes no sense whatsoever. destroy the memory at the END. furthermore, are you absolutely sure that the DISPLAY property isn't the issue? perhaps outlook can only display one message at a time when doing it through code like this? perhaps it's a z-index issue? although that's prolly not the case, it's none-the-less a possibility. office, after all, isn't the greatest software package in the world....

The outlook object closure is only there because I copied the code from another section where I am sending an email only to 1 person and then closing it.

I did forget to remove that from here, but I did try that and still the same result.

I was wondering on the outlook thing too, but if that were the case, would the 2nd email not pop up once the first is closed?
 

vba_php

Forum Troll
Local time
Yesterday, 19:03
Joined
Oct 6, 2019
Messages
2,884
would the 2nd email not pop up once the first is closed?
see my previous post to correct your error. but NO, if you DISPLAY more than one email in a single routine, then should all show up. every email client I've ever worked with allows that to happen, either through the interface or through automation. so you shouldn't have a problem with doing it.
 

NearImpossible

Registered User.
Local time
Yesterday, 19:03
Joined
Jul 12, 2019
Messages
225
see my previous post to correct your error. but NO, if you DISPLAY more than one email in a single routine, then should all show up. every email client I've ever worked with allows that to happen, either through the interface or through automation. so you shouldn't have a problem with doing it.

Guess i'm lost on the corrective actions.

Apparently i'm not aware of how to do it as neither the Step Into or Run to Cursor is working....
 

vba_php

Forum Troll
Local time
Yesterday, 19:03
Joined
Oct 6, 2019
Messages
2,884
Apparently i'm not aware of how to do it as neither the Step Into or Run to Cursor is working....
the key to press in order to run code line by line and stop on every line is F8. how much experience do you have working the VBA IDE (IDE = integrated development environment)??

I don't have time right now to debug the entire code for you, but I can do it tonight if you can't fix it before then. I'm off to work soon. setting breakpoints, like I showed you in the photo, is just a left click of the mouse on the left side of the coding window, at the same horizontal point where the code lines appear. follow?
 

NearImpossible

Registered User.
Local time
Yesterday, 19:03
Joined
Jul 12, 2019
Messages
225
the key to press in order to run code line by line and stop on every line is F8. how much experience do you have working the VBA IDE (IDE = integrated development environment)??

I don't have time right now to debug the entire code for you, but I can do it tonight if you can't fix it before then. I'm off to work soon. setting breakpoints, like I showed you in the photo, is just a left click of the mouse on the left side of the coding window, at the same horizontal point where the code lines appear. follow?

i'm aware of F8 and setting breakpoints, but for some reason its not playing nice and when I hit F8 it brings up my monitor settings, even though i'm in the VB window, so I tried to just click Debug > Step into which isn't doing anything either
 

vba_php

Forum Troll
Local time
Yesterday, 19:03
Joined
Oct 6, 2019
Messages
2,884
click the PLAY button, and set breakpoints on every line of code:

breakpoints.jpg
 

NearImpossible

Registered User.
Local time
Yesterday, 19:03
Joined
Jul 12, 2019
Messages
225
Got it, so now the question is why does the IF value not hold true for any other records....
 

vba_php

Forum Troll
Local time
Yesterday, 19:03
Joined
Oct 6, 2019
Messages
2,884
Got it, so now the question is why does the IF value not hold true for any other records....
that's something you will have to work on yourself. post back here if you get it solved. I will check in with you in a few hours. I have to go to work. your code is nowhere near difficult. you should be able to get it if you work at it. =)
 

NearImpossible

Registered User.
Local time
Yesterday, 19:03
Joined
Jul 12, 2019
Messages
225
that's something you will have to work on yourself. post back here if you get it solved. I will check in with you in a few hours. I have to go to work. your code is nowhere near difficult. you should be able to get it if you work at it. =)

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 !!
 
Last edited:

Users who are viewing this thread

Top Bottom