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.
Code:
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.
Code:
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.