Code to Update date and time email sent

[sarguzm]

I am trying to get my E Sent field to update whenever the automated Email button is clicked on the Customers form; however I running into a brick wall and don't know what I am doing wrong :banghead: I was able to get it to update the first record, but not any after.

Option Compare Database
Option Explicit

Private Sub E_Click()
Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.mailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("SELECT * FROM MailingList WHERE [Category] = 'X'")
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e- message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EAddress]
With objOutlookMsg
' Add the To recipients to the e- message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "This is the body of the message." & vbCrLf & vbCrLf

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If

Me!E_Sent = Now()

Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I sincerely appreciate any and all help.

Thanks,
sarguzm

 

[jdraw]

What exactly is Me!E_Sent? Are you trying to update MyRs!E_Sent?

The outlook SentOn property will be completed when the message actually gets set from Outlook.

You could try putting in some Debug.Print statements and stepping through the code for debugging in detail.

 

[sarguzm]

Hi jDraw,
Thank you for your quick reply! It is actually Me!Email_Sent, but since I am a new member I cannot spell out email. I am trying to update the Email Sent date and time field whenever the automated email is sent to a customer based on set criteria. Since it has been quite a few years since I have used Access VB code, I am quite a bit rusty.
Thanks,
sarguzm

 

[jdraw]

So your form is bound to the MyRS recordset?
In my view, if you want to save the time the email was sent into your recordset, you should have a MyRs.Edit and a MyRs.Update surrounding your intended update. But if the form is bound, and the recordset is updateable, then maybe moving off the record will update the recordset.

Me!E_Sent = Now() should update the value in that control on the form, but it may need to be refreshed/requeried.

I still suggest you add some debugging statement and step through the code to see what values exists where and when.

Good luck.

 

[Solo712]

So your form is bound to the MyRS recordset?
In my view, if you want to save the time the email was sent into your recordset, you should have a MyRs.Edit and a MyRs.Update surrounding your intended update. But if the form is bound, and the recordset is updateable, then maybe moving off the record will update the recordset.

Me!E_Sent = Now() should update the value in that control on the form, but it may need to be refreshed/requeried.

I still suggest you add some debugging statement and step through the code to see what values exists where and when.

Good luck.

I agree with jdraw in that the code appears to be confused about the current form (Me!) recordset and the named recordset (MyRS). There is no link between them such that one could expect Me!E_sent to copy the timestamp into all recipient records.

But I also see another issue: The For Each - Next and Do Until Loops are also badly mixed up. I take it that you want to: (1)create the recipient set from the recordset WHERE, (2) resolve the individual names of recipients , and finally (3) supply the subject and single text and send the message. To accomplish (1) you should go thru the MyRS recordset with the Do Until, build the .Recipients object, an also stamp the individal MyRS!E_Sent fields, then (2) resolve the names with the For-Next loop and then (3) compose the subject and message and send the message once, outside of either loop ! This may not be the optimal handling of the tasks but at least you will not end up in tangled up loops.

Good Luck !

Best,
Jiri

 

[rzw0wr]

  Option Compare Database
Option Explicit

Private Sub E_Click()
Dim MyDB As Database
Dim MyRS As Recordset
Dim TheAddress As String
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("SELECT * FROM MailingList WHERE [Category] = 'X'")

Do Until MyRS.EOF
With objOutlookMsg



MyRS.MoveNext
Loop
  MyRS.close
  MyDB.close
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I am not sure if this is what you are trying to do.
Above is the DAO recordset with the odd stuff stripped out.
No where in this code are you trying to add or edit any records in the recordset that I see.
You do have Me!E_Sent = Now()
??
If you are tying to add this to the recordset the syntax is
MyRS!E_Sent =Now()
You will also need to tell the recordset if you this is an edit or addnew.
Then you need to save the recordset with an Update command.

Am I close to what you are wanting to do or did I miss something.

Dale

 

[sarguzm]

Jdraw, Jiri, & Dale,

I add the debugging statement and stepped through the code to see what values exists where and when. I added MyRS.edit and MyRS.update before and after the Me!E_Sent = Now() and it works perfectly! I am starting to get the hang of this once again...

Thank you very much for being such a big help to me!
sarguzm