enter date in field in addition to sending email

[ferociousllama]

Hi Everyone.

I have been a lurker on this forum for a very long time and I've gotten much great advice and information. I'm happy there are communities like this one!

I'm not that new to Access but my skill level is certainly novice. I have been using it for a couple years for mostly simple things and am just now getting into using VBA to do some of the more awesome things Access is capable of.

I have code below that emails each email address on a query with an attached file from another field for that same record. It is set to display the email before sending but I will be changing that to send directly once I'm able to get it to do everything I want it to do and test to make sure it's doing everything correctly.

Sub email_exp1_Click()
 
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set MyDb = CurrentDb
Set qdf = MyDb.QueryDefs("Table1 Query")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next
Set rsEmail = qdf.OpenRecordset()
Set oLook = CreateObject("Outlook.Application")
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
        myRecipient = .Fields(1)
        invoice = .Fields(0)
            If IsNull(myRecipient) = False Then
                Set oLook = CreateObject("Outlook.Application")
                Set oMail = oLook.CreateItem(0)
                With oMail
                    .To = myRecipient
                    .Body = "See attached"
                    .Subject = "Test Email"
                    .Attachments.Add invoice
                    .Display
                End With
            End If
                    .MoveNext
        Loop
End With
Set oMail = Nothing
Set oLook = Nothing
End Sub

It works great but after the emails are sent to everyone on the list I have to go back and manually change the status field from "Not sent" to "Sent". I like the idea of making the update to the status automatically so I (or whoever is using the database) doesn't forget.

Along the same line, I'd like to enter todays date into another field to indicate when the email was sent.

Is it possible to make these two pieces of information enter as this code is running?

I apologize if this is a super simple problem Thank you to everyone in advance for your help!

 

[Cronk]

It would be of no small assistance to know where your Status field (if indeed that is the name of the field) is located.

Just maybe, it is in the data set that is included in your rsEmail.

If so, you could edit the record, if indeed the recordset is updatable (because you don't give any information about the sql used in query [Table1 Query])

In which case, the code you would use would be
.edit
!Status ="Sent"
.update

 

[ferociousllama]

That worked like a charm. Thanks!

There is a query that combines the email addresses with pretty much all the other information. The status field is included in the query.

Set qdf = MyDb.QueryDefs("Table1 Query")

"Table1 Query" is the name of the query where the recordset is coming from.

The "sent date" field is not included in the query. How would I go about having the current date entered into a field called "senddate" on a table named "Table1"

From the looks of this situation so far, it seems that i can just add the "senddate" field to the query used for the recordset (am I saying that right) and use the same code I used to update the status? Is there a down side to that?

 

[Cronk]

The easiest way is to include SentDate in your query and then make the edit section

.edit
!Status ="Sent"
!Sentdate = Date() 'insert current system date
.update

 

[ferociousllama]

I will use that! You're a life saver!

 

[ferociousllama]

That code worked great in my test database.

Unfortunately the name for the "Sentdate" field is actually "Sent date". It has a space and the space causes an error.

how do I reference a field name with a space in it below. The situation is the Sentdate field is actually Sent date with a space.

.edit
!Status ="Sent"
!Sentdate = Date() 'insert current system date
.update

I hope what I'm asking makes sense

 

[Minty]

put square brackets around [Sent Date]

 

[ferociousllama]

Works good. Thanks!