Send Emails Automatically

[cbrsix]

I know there are a million different threads out there concerning this topic but I am looking for some more add-ons to the current code below.

Private Sub SendEmail_Click()
On Error GoTo SendEmail_Err
    Dim myOlApp As Object
    Dim myNameSpace As Object
    Dim myFolder As Object
    Dim myItem As Object
    Dim myAttachments, myRecipient As Object
    Dim recipient As String
    Dim file_name As String
    Dim mySubject As Object
    Dim dbs As Object
    Dim rst As Object
    Dim strSQL As String
    
    strSQL = "Query1"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    rst.MoveFirst
    While Not rst.EOF
        recipient = rst!myemail
        Set myOlApp = CreateObject("Outlook.Application")
        Set myItem = myOlApp.CreateItem(olMailItem)
        Set myAttachments = myItem.Attachments
        Set myRecipient = myItem.Recipients.Add(recipient)
        '"Message Subject String Here"
        myItem.Subject = "Test"
        '"Put Message Body Text Here"
        myItem.Body = rst!myname & "," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "You are approaching the Expiration/Renewal Date for the following: " & rst!mydate & _
        myItem.Display
        myItem.Send
        rst.MoveNext
    Wend
    Set myRecipient = Nothing
    Set myAttachments = Nothing
    Set myItem = Nothing
    Set myOlApp = Nothing
    Set rst = Nothing
SendEmail_Exit:
    Exit Sub
SendEmail_Err:
    MsgBox Err.Description
    Resume SendEmail_Exit
End Sub

Here is a little bit about my table:

Table: Reps (myname)
Fields: DueDate - Date/Time (mydate)
ExpirationDate (for ex NJ) - Date/Time
ExpirationDate (for ex Pa) - Date/Time

Basically, I have a table that has multiple fields with different expiration dates. I have a query set up that pulls the dates that are from today to 30 days in the future. Any of the info in that query is used to generate the emails going out.

SOLVED Issue 1:
I want to only have the emails sent out one time when they first meet the criteria of the query. I have tried this in my code to have the query update the table but I can't get it to work.

myItem.Display
myItem.Send
.Edit
Emailed = True 'This is a Yes/No field in the Table that I want to have updated so that once the email has been sent for that specific date, it no longer shows in the query and thus is not emailed out again.
.Update
rst.MoveNext
Wend

SOLVED Issue 2:
I can't figure out how to get the field name to show up before the date. For example, I want it to look like this: You are approaching the Expiration/Renewal Date for the following: NJ 10/20/2012.
I can get the date to show up but not the field name.

I think that's enough for now. Any help would be appreciated.

[pbaldy]

I don't see that you have a With block running and you didn't specify the recordset field. Try

rst.Edit 
rst!Emailed = True 
rst.Update

[cbrsix]

Thank you, that worked. I knew it was something stupid I was missing.

[pbaldy]

No problem.

[cbrsix]

I mentioned before that I have multiple columns with dates in each one. My dillema now is that when the first Emailed is updated to True, the only way that I could think of getting rst!mydate to change to the next date (mydate2) was to add an iif() statement.

 myItem.Body = rst!myname & "," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "You are approaching the Expiration/Renewal Date for the following: " & IIf(rst!Emailed = True, rst!mydate2, rst!mydate) & _

Is this the best way to do this?

[cbrsix]

Database11.zip

here is my sample database

[pbaldy]

Your table is not normalized. The date and emailed fields should be in a related table, with a record for each combination. That makes your query simpler.

[cbrsix]

@pbaldy - I know that it is not normalized. I set this sample db up as my normal db is set up right now. I am actually in the process of changing that over to the proper normalization that should be used. It was not set up properly before. I was just curious if there was a way to accomplish my goal with the way it is now so I would have this function available for use now and not have to wait until I am done normalizing it.

[pbaldy]

A UNION query is common way of pulling non-normalized data into a normalized format that can be worked with more easily.

[cbrsix]

I haven't spent much time at all working with UNION queries. I am just going to proceed with fixing the db to work more efficiently. Thanks for the advice though.

Thanks for all of your help. It is working exactly how I want it to right now.

[pbaldy]

No problem.