Send email If

[Jonny45wakey]

Hi I have a query "qryAppointments7days" with a field called [Emailsent7day], what i would like is when On Form Open runs, the VBA checks all records in the query to see if the [Emailsent7day] contains "No", if it does then email will be sent and [Emailsent7day] field updated to "YES".

I've got the email VBA working fine (see below), how and where would i incorporate the above check please?

 

[arnelgp]

Do While Not rs.EOF
    If rs!Emailsent7day = False Then
       rs.Edit
       rs!Emailsent7day = True
       rs.Update
         ... <move your email code here>
    End If
    rs.Move
Loop
...
...

 

[Gasman]

I would just bring in those still set to No.

    strSQLEmail = "SELECT  Format([TransactionDate],""yyyymmdd"") & Format([ID],""000000"") AS UKey, Emails.*, tblClient.ClientDivision From Emails "
    strSQLEmail = strSQLEmail & "LEFT JOIN tblClient ON Emails.CMS = tblClient.ClientCMS "
    strSQLEmail = strSQLEmail & "WHERE (((Emails.EmailStatus) = 'Yes')) " ' AND (Emails.DelayEmail = False)) "
    'strSQLEmail = strSQLEmail & "ORDER BY Emails.Client, Emails.TranType, Emails.ID, Emails.TransactionDate;"
    strSQLEmail = strSQLEmail & "ORDER BY Emails.Client, Emails.TranType, Format([TransactionDate],""yyyymmdd"") & Format([ID],""000000"") ;"

Note, my two options were Yes and Sent, so equivalent to your No and Yes.
I updated the records to Sent, when sent.

 

[Gasman]

Hi I have a query "qryAppointments7days" with a field called [Emailsent7day], what i would like is when On Form Open runs, the VBA checks all records in the query to see if the [Emailsent7day] contains "No", if it does then email will be sent and [Emailsent7day] field updated to "YES".

I've got the email VBA working fine (see below), how and where would i incorporate the above check please?

View attachment 123677

Please post the actual code, not a picture of it. Within code tags as well please.

You have been asked before to post code and not a picture previously in your other thread.

 

[Jonny45wakey]

Please post the actual code, not a picture of it. Within code tags as well please.

You have been asked before to post code and not a picture previously in your other thread.

Thanks Gasman

Your code worked perfectly

Code
' Sends an email if the date in a field equals today's date + 1 day
Public Sub SendEmailIfDateIs1Day()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim olApp As Object
Dim olMail As Object
Dim recipient As String


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT EmailAddress, EmployeeName, ApptLocation,ApptStart, EmailDate,Emailsent1day,Email1daysentDTG FROM qryAppointments1day")
Set olApp = CreateObject("Outlook.Application")

Do While Not rs.EOF
If rs!emailsent1day = "No" Then
rs.Edit
rs!emailsent1day = "Yes"
rs.Update
recipient = Nz(rs!emailAddress, "")
Set olMail = olApp.CreateItem(0)
With olMail
.To = rs!emailAddress
.Subject = "Reminder for tomorrows scheduled task:-" & " " & " " & (rs!ApptLocation)
.Body = "Hello " & Nz(rs!EmployeeName, "") & "," & " " & "this is a reminder for your planned task" & " " & (rs!ApptLocation) & " " & "scheduled for" & " " & "-" & " " & (rs!ApptStart) & " " & "-" & " " & "is due within 1 day, please access the HKUK Task Scheduling Database and complete your tasks"
.Send
End With
End If
End If

rs.MoveNext


Loop

rs.Close
Set rs = Nothing
Set db = Nothing
Set olApp = Nothing

End Sub

 

[Gasman]

Why read in ALL the records, only to find a small percentage are No?

I had criteria in my select to ONLY bring in records I KNEW needed to be updated and sent.

 

[June7]

You did not use CODE tags for posting code. CODE tags retain indentation and readability. Use the </> icon on toolbar.