Email function skipping entries...what is going on? 2

[ChrisCalvert]

I, through a great deal of interaction here, have created a function that sends an email to all email addresses in a recordset. However, this seems to be randomly skipping people. Here is the code it uses:
---------------------------------------------
Private Sub SendBttn_Click()

Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Dim strTextLine As String
Dim strTemplate As String
Dim myTextFile As New TextFile
Dim intError As Integer
strTemplate = ""
myTextFile.FileName = "r:\iwelctesttemp.txt"
myTextFile.StripLeadingSpaces = False
myTextFile.StripTrailingSpaces = False
intError = myTextFile.cfOpenFile
If intError = 0 Then
While Not myTextFile.EndOfFile
myTextFile.csGetALine
strTextLine = myTextFile.Text
strTemplate = (strTemplate + (Chr$(13))) + strTextLine
Wend
Else
MsgBox Error(intError)
End If
myTextFile.cfCloseFile
Set myTextFile = Nothing

Set rst = db.openrecordset("Select * from EmailsToday&quot
'Cycle through the table, sending it to each person on the list
Do While Not rst.EOF
DoCmd.SendObject acSendNoObject, , acFormatRTF, rst!Email, , , "Welcome to SERVICE.", strTemplate, False
DoEvents
If Not rst.EOF Then
rst.MoveNext
End If
Loop

rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
-----------------------------------------------

Now, I have conducted several trials, and the system seems to skip people when sending out email. It's not that they don't get them, I check the outlook 'sent items' and these are just not there.

I have results from some trials listed below. The best conjecture I have at this point is that it is somehow related to overloading something and my emails are just skipped. I cannot say for sure. Here is the trial data.
The names lised correspond to addresses in the table EmailsToday (from where this reads what to send).
------------------------------------------------------
Trial1
Me-Sent
Me-Sent
Me-Sent
Me-Sent
Me-Sent
Me-Sent
Me-Sent

Trial2a,b,c (same results 3 times)
Me-Sent
Chad-Sent
Donnie-Sent
Marissa- NOT SENT
Shontae- NOT SENT
Sharon- Sent
Pam- Sent


Trial3
Me-Sent
Marissa-Sent
Donnie-Sent
Chad-Sent
Sharon-Sent
Shontae- NOT SENT
Erika- NOT SENT

Trial4
Kevin-Sent
Shontae-Sent
Marissa-Sent
--------------------------------------------------------

I tried adding the do events in the loop, on the basis of my 'overload' guess. Well, ideas or suggestions would be looked upon with fervent thanks.

 

[Junior1544]

in stead of sending out that many different emails, why not make the loop just get the people's names/email address's here is an example of that look as i would make it...

this way you are sending one email, you can even move the variable strEMail to the bcc if there is a problem with to many people in one to box...

just my idea

--James


dim strEMail as string

Do While Not rst.EOF
strEMail = rst!Email & "; " & strEMail
rst.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , acFormatRTF, strEMail, , , "Welcome to SERVICE.", strTemplate, False
JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[ChrisCalvert]

Thanks, I shall certainly try that.
Seems like it should work. I will let you know Monday. (Too sick of looking at this to do more today.)

 

[Junior1544]

i know the feeling...

--James JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[LLudden]

I recently had this same problem. For some reason the docmd.sendobject just ignored some names, and never anything consistant I could nail down. No errors, nothing..just wouldn't send. I ended up using the following code:

Set objOutlook = CreateObject("Outlook.Application&quot
Set MyItem = objOutlook.CreateItem(olMailItem)
MyItem.To = CStr(SendTo)
MyItem.Subject = Forms!frmEmail!cbxEmailSubject.Column(1)
MyItem.Body = vbCrLf & Forms!frmEmail!txtEmailMessage
MyItem.Send
Set objOutlook = Nothing

This has worked every time, but requires outlook to be installed.

 

[Junior1544]

this looks good to me... it may run a little slower, but still, it also looks more reliable...

--James JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[ChrisCalvert]

Just a quick question about the loop.
All of this in the loop or


Set objOutlook = CreateObject("Outlook.Application&quot
------------loop begin------------
Set MyItem = objOutlook.CreateItem(olMailItem)
MyItem.To = CStr(SendTo)
MyItem.Subject = Forms!frmEmail!cbxEmailSubject.Column(1)
MyItem.Body = vbCrLf & Forms!frmEmail!txtEmailMessage
MyItem.Send
----------loop end----------------
Set objOutlook = Nothing


or something else entirely?

Thanks again to everyone for the assistance.

-chris

 

[LLudden]

If the same message is going to everyone, then you could do:

'rs = recordset with all names
Set objOutlook = CreateObject("Outlook.Application&quot
Set MyItem = objOutlook.CreateItem(olMailItem)
MyItem.Subject = "Subject"
MyItem.Body = vbCrLf & strMessage

rs.MoveFirst
Do While Not rs.EOF
MyItem.To = CStr(rs!EmailAddress)
MyItem.Send
rs.MoveNext
Loop

Set objOutlook = Nothing

This should work ok, if not, then move everthing except the first line (defining the outlook object) into the loop. If you do that, then you will want to add "set MyItem = Nothing" in the line before the end of the loop.

 

[ChrisCalvert]

Well, thanks to the both of you (Lludden, Junior) and others who helped before my process is working like a charm. Sends much faster than my original method, as well as sending to everyone.
Here, for the sake of completeness, is the final code.


----------------------------------------------

Private Sub SendBttn_Click()

Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Dim strTextLine As String
Dim strTemplate As String
Dim myTextFile As New TextFile
Dim intError As Integer
strTemplate = ""
myTextFile.FileName = "r:\iwelctesttemp.txt"
myTextFile.StripLeadingSpaces = False
myTextFile.StripTrailingSpaces = False
intError = myTextFile.cfOpenFile
If intError = 0 Then
While Not myTextFile.EndOfFile
myTextFile.csGetALine
strTextLine = myTextFile.Text
strTemplate = (strTemplate + (Chr$(13))) + strTextLine
Wend
Else
MsgBox Error(intError)
End If
myTextFile.cfCloseFile
Set myTextFile = Nothing
Set rst = db.openrecordset("Select * from EmailsToday&quot
Set objOutlook = CreateObject("Outlook.Application&quot


'Cycle through the table, sending it to each person on the list
Do While Not rst.EOF
Set MyItem = objOutlook.CreateItem(olMailItem)
MyItem.Subject = "Test Email, Please Delete."
MyItem.Body = vbCrLf & strTemplate
MyItem.To = CStr(rst!Email)
MyItem.Send
If Not rst.EOF Then
rst.MoveNext
End If
Loop
Set objOutlook = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
---------------------------------------------- Young Programmer: But, sir, the users are revolting!

Old Programmer: They've always been revolting, now they're just rebellin'!

 

[grnzbra]

I tried LLudden's first set of code and it gave me a not defined error message for objOutlook and my item. I defined objOutlook as Object, but there seems to be no "Item" to uses in the Dim statement. It also didn't like ofMailItem.
What's missing?

 

[ChrisCalvert]

I think you are missing the part where you refenence the outlook object library. Click on tools, references, and then choose Microsoft Outlook 9.0 Object Library.

-chris John, I've been bad
and they're coming after me.
Done someone Wrong,
and I fear that it was me. -TMBG