Need to add "Date email Sent"

[Hillbillie]

I am sending bulk emails as attached snap, "thanks to this site", now I want to add the date the emails was sent to each donor. Here is my code.

Private Sub SendEmailButton_Click()
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM emailquery WHERE email = -1"
Set rs = CurrentDb.OpenRecordset(strSQL)

With rs

Do While Not rs.EOF
Me.email1 = ![Email Address]
If ![Email Address] <> "" Then DoCmd.SendObject acSendReport, "WCRC Letter", acFormatSNP, ![Email Address], , , !eSubject, Me.eMessage & Me.eMessageFooter, False

.MoveNext
Loop
End With
End Sub

-------------------
I will add a "EmailSentDate" field to post the date in.

Thanks,

Larry

 

[hermanlaksko]

Do While Not rs.EOF
Me.email1 = ![Email Address]
If ![Email Address] <> "" Then DoCmd.SendObject acSendReport, "WCRC Letter", acFormatSNP, ![Email Address], , , !eSubject, Me.eMessage & Me.eMessageFooter, False

rs.edit
rs!EmailSentDate=date
rs.update
.MoveNext
Loop
End With
End Sub


Herman
Say no to macros

 

[Hillbillie]

Thank hermanlaksko for the quick response.

After further thought, here is what I relly need to do so I can see who was sent an email, what was the name of the letter and when. This will be in a seperate table, with the following fields.

TableName = MembersEmailsSent
ID = autonumber
MemberID = ID from emailquery above
Letter Name = me.ereport from the form
DateEmailSent = Date()

Each time an email is sent, this table is updated until EOF from above.

Thanks,

Larry

 

[hermanlaksko]

There is no stopping you is there :-D

Ok here goes.

strSQL = "SELECT * FROM SendtEmail"
Set rs2 = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF
Me.email1 = ![Email Address]
If ![Email Address] <> "" Then DoCmd.SendObject acSendReport, "WCRC Letter", acFormatSNP, ![Email Address], , , !eSubject, Me.eMessage & Me.eMessageFooter, False
rs2.addnew
rs2!TableName = MembersEmailsSent
'rs2!ID (autonumber)
rs2!MemberID = ID '(from emailquery above)
rs2!LetterName = me!ereport '(from the form)
rs2!DateEmailSent = Date()
rs2.update

rs.edit
rs!EmailSentDate=date
rs.update
.MoveNext
Loop
End With
End Sub

Something like that.

Herman
Say no to macros

 

[Hillbillie]

Thanks for your help. Keep in mind that I know very little. What I have learned is from trial and error.

I tried this and replaced my code with this in my form VB Code. It gave me the following error " Compile error: Invalid or unqualified reference" and highlists the ![Email Address] in Me.email1 = ![Email Address]

Let me give all my code from the form and from the module.
From Form:
----------------------------------------------------------
Private Sub SendEmailButton_Click()
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM emailquery WHERE email = -1"
Set rs = CurrentDb.OpenRecordset(strSQL)

With rs

Do While Not rs.EOF
Me.email1 = ![Email Address]
If ![Email Address] <> "" Then DoCmd.SendObject acSendReport, "WCRC Letter", acFormatSNP, ![Email Address], , , !eSubject, Me.eMessage & Me.eMessageFooter, False

.MoveNext
Loop
End With
End Sub
_________________________________________________________
from module named email
----------------------------------------------------------
Public Function SendReport(sName As String) As Boolean
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM emailquery WHERE eReport = '" & sName & "'"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF Then 'no record for that report
Set rs = Nothing
MsgBox "No email entry for that report.", vbOKOnly, "No Email"
Exit Function
End If

With rs
.MoveFirst

' DoCmd.SendObject acSendReport, sName, , ![Email Address], !eCC, _
' !eBbcc, !eSubject, !eMessage, False
DoCmd.SendObject acSendReport, sName, , ![Email Address], , _
, !eSubject, !eMessage, False


MsgBox "Email sent to " & ![Email Address] & vbCrLf & !eCC & _
vbCrLf & !eBcc & ".", vbOKOnly, "Email Sent"
End With
SendReport = True
Set rs = Nothing
End Function
---------------------------------------------------------
The form currently refers to a query named EmailQuery
The fields and tables used in the query are:
Field: Table:
LastName MembersInfo
FirstName MembersInfo
EmailAddress MembersInfo
email MembersInfo
ID MembersInfo
ereport email
esubject email
emessage email
-----------------------------------

This all work very good. I can sent out emails great with the attached snap.

Now, I have another table that I want to create a new record for each email I send out.

TableName = MembersEmailsSent
ID = autonumber
MemberID = ID from emailquery above
Letter Name = me.ereport from the form
DateEmailSent = Date()

----------------------------------------------------
I know my module doesnt 100% match some of the code in my form, but so far no errors. Do I have to use the module?

This is getting pretty complicated.

Thanks,

Larry

 

[hermanlaksko]

Hey Larry

We are here to help!

I note that you tend to DOT in stead of BANG.... (what is he on about???)

I.e. Me.email1 = ![Email Address]
Should be Me!email1 = Me![Email Address]
Bang (!)=Your fieds
Dot(.)=refrences to access objects ie rs.EOF

Also I note that when you name your fields, you use Email Address - this space will force you into using [Email Address] to ease your programming concidder using EmailAddress as your name instead mixing capitals in naming will give you the same "EasyRead" as ![Email Address]
but make it simpler while programming. Just a hint. ;-)

And from your code you imediate problem could be laying here.

Let me know if this helps.
All the best




Herman
Say no to macros

 

[Hillbillie]

Well that got it. A special thanks to Herman,

Below is the code for my form and module.

FORM CODE-----------------------------------------
Private Sub SendEmailButton_Click()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim strMessage As String
Dim strNameSent As String
strSQL = "SELECT * FROM Membersinfo WHERE email = -1"
strSQL2 = "SELECT * FROM MembersEmailsSent"
Set rs = CurrentDb.OpenRecordset(strSQL)
Set rs2 = CurrentDb.OpenRecordset(strSQL2)
With rs
Do While Not rs.EOF
strNameSent = [Dear] & " " & ![Firstname] & " " & ![LastName]
strMessage = strNameSent & ", " & vbCrLf & vbCrLf & Me.eMessage & vbCrLf & vbCrLf & Me.eMessageFooter
Me.email1 = ![Email Address]
Me.Dear1 = strNameSent
rs2.AddNew
rs2!MemberID = !ID
rs2![Letter Name] = Me!ereport
rs2!DateEmailSent = Now()
rs2.Update
If ![Email Address] <> "" Then DoCmd.SendObject acSendReport, "WCRC Letter", acFormatSNP, ![Email Address], , , eSubject, strMessage, False
.MoveNext
Loop
End With
End Sub


MODULE CODE---------------------------------------------
Public Function SendReport(sName As String) As Boolean
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim strNameSent As String
strSQL = "SELECT * FROM Membersinfo WHERE eReport = '" & sName & "'"
strSQL2 = "SELECT * FROM MembersEmailsSent"
Set rs = CurrentDb.OpenRecordset(strSQL)
Set rs2 = CurrentDb.OpenRecordset(strSQL2)
If rs.EOF Then 'no record for that report
Set rs = Nothing
MsgBox "No email entry for that report.", vbOKOnly, "No Email"
Exit Function
End If
With rs
.MoveFirst
DoCmd.SendObject acSendReport, sName, , ![Email Address], , _
, eSubject, strMessage, False
MsgBox "Email sent to " & ![Email Address] & vbCrLf & !eCC & _
vbCrLf & !eBcc & ".", vbOKOnly, "Email Sent"
End With
SendReport = True
Set rs = Nothing
End Function

--------------------------------------------
This is a great website with LOTS of knowledgeable people.

Larry