Mass Email - Not Working..

[Basia]

Greetings:

I am trying to send out a personalized email to any given number of people. While the loop is working, only the first person on the list gets an actual email. Is some flag in the SendObject turning off?

Here is my code:

Set rst = Me.RecordsetClone

rst.MoveFirst

Do Until rst.EOF

varSalutation = rst!Salutation
varNameLast = rst!NameLast
varIntro = Me.Intro
varBody = Me.Body
varClosing = Me.Closing
varSignature = Me.Signature
varEmail = rst!Email

EmailInfo = "Dear " & varSalutation & " " & varNameLast & ":" & vbCrLf
EmailInfo = EmailInfo & vbCrLf
EmailInfo = EmailInfo & varIntro & vbCrLf
EmailInfo = EmailInfo & vbCrLf
EmailInfo = EmailInfo & varBody & vbCrLf
EmailInfo = EmailInfo & vbCrLf
EmailInfo = EmailInfo & varClosing & vbCrLf
EmailInfo = EmailInfo & vbCrLf
EmailInfo = EmailInfo & "Sincerely," & vbCrLf
EmailInfo = EmailInfo & varSignature & vbCrLf

DoCmd.SendObject acSendNoObject, " ", acFormatRTF, rst!Email, , , "Update Information", EmailInfo, False

rst.MoveNext
Loop

rst.Close
str1 = "Number of emails sent = " & rst.RecordCount
MsgBox str1, vbInformation, "Email Successful"

HELP!!!!!

Thanks...Basia

 

[TudorSmith]

Does the loop drop out aftert he first record? What is the RecordCount in the Message box at the end of the process?Without copying the code and trying it...it looks fine. However, you may want to consider this:

varIntro = Me.Intro
varBody = Me.Body
varClosing = Me.Closing
varSignature = Me.Signature

Change the recordset object identifiers. Instead of Me.Intro, try Me!Intro, assuming Intro is an element in your recordset, and not an object of the record set.

For all four lines, change the "." for an "!" and try again.

To shorten the process you could do this and remove the variables!!

EmailInfo = "Dear " & rst!Salutation & " " & rst!NameLast & ":" & vbCrLf
EmailInfo = EmailInfo & vbCrLf
EmailInfo = EmailInfo & Me!Intro & vbCrLf
EmailInfo = EmailInfo & vbCrLf
EmailInfo = EmailInfo & Me!Body & vbCrLf
EmailInfo = EmailInfo & vbCrLf
EmailInfo = EmailInfo & Me!Closing & vbCrLf
EmailInfo = EmailInfo & vbCrLf
EmailInfo = EmailInfo & "Sincerely," & vbCrLf
EmailInfo = EmailInfo & Me!Signature & vbCrLf

birklea

 

[Basia]

Thanks for the feedback. The loop is working okay - I followed the loop through each time and it set up the SendObject string correctly, but it did not actually send any emails after the first one.(i.e. There were 7 records and it sent #1, but not 2-7)

Do you know if there is some flag on SendObject?

Thanks...Basia

 

[TudorSmith]

Hi Basia,

I did something similar...but instead of Do Until Loop, I looped through with a "For Next Loop"

Let me show you:
=-=-=-=-=-=-=-=Codey Bit=-=-=-=-=-=-
Dim rst As Recordset
Dim JSrst As Recordset
Dim i, Cancel As Integer
Dim strMessage, strCVLocation, strCC, strTo As String

i = 0
Set JSrst = gdb.OpenRecordset("tblJSAppliedFor&quot

If JSrst.RecordCount = 0 Then
MsgBox "There are no records in this table. Cannot email at this time!", 16, "Email recipient"
Exit Sub
Else
JSrst.MoveFirst
strTo = JSrst!EMail
JSrst.MoveNext
For i = 0 To JSrst.RecordCount + 1
strCC = strCC & JSrst!EMail & ";"
i = i + 1
JSrst.MoveNext
Next i
If Right(strCC, 1) = ";" Then
strCC = Left(strCC, Len(strCC) - 1)
End If
JSrst.Close
Set rst = gdb.OpenRecordset("SELECT * FROM zstblProfile WHERE Key = '" & "EmailCV" & "'&quot
strCVLocation = rst!Value
strMessage = rst!Description
rst.Close

DoCmd.SendObject acSendNoObject, , , strTo, strCC, , "Contracting Availability", strMessage
End If

=-=-=- End of Codey Bit=-=-=-=-=-=

You can see from my If statement, that I firstly check if there are any records, and if not...bomb out! Next, I capture the first record, and place that recipient in the strTo variable. All remainder records ar captured in the strCC bit, and my code basically builds a CC Recipient list such (a@b.com;c@d.com;e@f.com etc). You could be sneaky here and pu tall your recipients as BCC sono one knows who any of the recipeints are!!!

Now, this method allowed me to send one message to multiple recipients, but I think you are trying to send one message per recipient, in which case, study my FOR NEXT loop, and consider using that method instead of the Do Until.EOF. It may well work! Otherwise you can try Do While Not rst.EOF instead of Do Until.

birklea