Access/Outlook Mass Emailer Part 2

[ghloid]

Hello all you guru's,

I'm hoping someone can help me out with this (possibly Irish1957). I want to create a fairly simple mass email form using Access and Outlook. Right now, I have a table in my Access database called SendEmailTbl. This table has fields with LAST_NAME, FIRST_NAME, EMAIL, DATE_EMAILED, and EMAILCHK.

I would like to create a routine to send an email out to everyone in that table using a form button. I have looked at this thread: thread181-555313 which has given me MUCH insight into doing this (Irish1957 offered a nice little piece of code to accomplish such a feat). I am trying to utilze the code offered by Irish, but I'm having trouble. It seems to be something with the recordset perhaps. Here's my code:

Private Sub cmdEmail_Click()
Dim DBdb1 As Database ' Database containing all the emails addresses to be sent to
Dim rst As Recordset
Dim strEmail As String
Dim FirstName As String ' Persons First name extracted from data tale
Dim Surname As String
Dim attachment As String
Dim mev3 As String
Dim objSafeMsg As Redemption.SafeMailItem

Dim objOutlook As New Outlook.Application
Dim objRem As MailItem
Dim flag As Boolean
On Error Resume Next
' read send list name from form This allows you to use several different send lists
mev3 = Me.txtQuerySQL

' indicates sending emails
Set DBdb1 = CurrentDb
'attachment = Me![Texrt8] ' This is the full path name of the attachment
Set rst = DBdb1.OpenRecordset(mev3, dbOpenDynaset)
With rst
.MoveFirst
Do While Not rst.EOF
If rst!EMAIL <> &quot;&quot; Then

strEmail = !

Surname = ![FIRST_NAME] & &quot; &quot; & ![LAST_NAME] & &quot;,&quot;

Set objRem = objOutlook.CreateItem(olMailItem)
objRem.To = strEmail
objRem.Subject = Me.txtEmailSubj ' Subject line from driving form
objRem.Body = &quot;Dear &quot; & Surname & Chr(13) & Chr(13) & Me.txtEmailMsg
'If attachment <> &quot;nil&quot; Then
' objRem.Attachments.Add attachment
'End If
Set objSafeMsg = CreateObject(&quot;Redemption.SafeMailItem&quot
objSafeMsg.Item = objRem
objSafeMsg.Send
'objRem.Send
'on fail to send displays email to allow hand edit
If Err.Number <> 0 Then
objRem.display
Err.Clear
End If

End If

.MoveNext
Loop

End With

End Sub

I added a few lines to account for Outlook security prompts (the redemption references there). Perhaps that is the issue I'm seeing? I don't think so, but I'll tell you what happens currently when I click the form button. Right now, it creates the email just fine, but it seems to do it NON STOP, and it does not SEND any of the emails, rather it just creates an email with a subject and the message body as defined on the text box on the form. The email address and the Surname never get populated.

On my form, I have defined the recordset (rst) using a SQL string in a field on the form. The SQL string simply selects all the records from the SendEmailTbl table. It's a simple string that goes like this:

SELECT SendEmailTbl.LAST_NAME, SendEmailTbl.FIRST_NAME, SendEmailTbl.EMAIL
FROM SendEmailTbl;

Is the problem related to that SQL code maybe? The code is placed in a text field on the form called txtQuerySQL which is referenced in the code above. It would seem to me that the problem probably lies in that recordset somehow. The code seems to do a non stop loop when the Email address isn't found perhaps.

One FINAL thing I would like to do somehow in the recordset loop is to check off the EMAILCHK box and put in the Now() date in the DATE_EMAILED fieldin the SendEmailTbl table. I'm not quite sure how that would be done.

If anyone has any ideas, I'd greatly appreciate it. Sorry for the long post, but I'm trying to explain everything as best as I can.

I'll be working with it to see what I can come up with.

THANKS EVERYONE!!!

 

[ghloid]

I think I've got it now. A friend who is familiar with recordsets in Access helped me out with it. Apparently, the code I was using with the recrodset WAS the problem. We also put the SQL statement defining the recordset directly in the code rather than as a value in a text box on the form. The code looks like this now:

Private Sub cmdEmail_Click()
'Dim DBdb1 As Database ' Database containing all the emails addresses to be sent to
Dim rst As ADODB.Recordset
Dim strEmail As String
Dim FirstName As String ' Persons First name extracted from data tale
Dim Surname As String
Dim attachment As String
Dim mev3 As String
Dim objSafeMsg As Redemption.SafeMailItem

Dim objOutlook As New Outlook.Application
Dim objRem As MailItem
Dim flag As Boolean
On Error Resume Next
' read send list name from form This allows you to use several different send lists
'mev3 = Me.txtQuerySQL
mev3 = &quot;SELECT LAST_NAME, FIRST_NAME, EMAIL FROM SendEmailTbl;&quot;

' indicates sending emails
'Set DBdb1 = CurrentDb
'attachment = Me![Texrt8] ' This is the full path name of the attachment
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open mev3, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'Set rst = DBdb1.OpenRecordset(mev3, dbOpenDynaset)
With rst
.MoveFirst
MsgBox .RecordCount
Do While Not .EOF
If (.Fields(&quot;EMAIL&quot) <> &quot;&quot; Then
strEmail = .Fields(&quot;EMAIL&quot
Surname = .Fields(&quot;FIRST_NAME&quot & &quot; &quot; & .Fields(&quot;LAST_NAME&quot & &quot;,&quot;
Set objRem = objOutlook.CreateItem(olMailItem)
objRem.To = strEmail
objRem.Subject = Me.txtEmailSubj ' Subject line from driving form
objRem.Body = &quot;Dear &quot; & Surname & Chr(13) & Chr(13) & Me.txtEmailMsg
'If attachment <> &quot;nil&quot; Then
' objRem.Attachments.Add attachment
'End If
Set objSafeMsg = CreateObject(&quot;Redemption.SafeMailItem&quot
objSafeMsg.Item = objRem
objSafeMsg.Send
'objRem.Send
'on fail to send displays email to allow hand edit
If Err.Number <> 0 Then
objRem.display
Err.Clear
End If
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
End Sub


The only thing I would now like to do is to somehow mark the EMAILCHK field and the DATE_EMAILED field in the SendEmailTbl table with this code. That SHOULD be easy enough, but I still have no clue. If anyone out there DOES have a clue, please let me know. Otherwise I'll just try to nag my friend once more.

THANKS!!