Im Loop'd - pls Help

[Alloyd]

I attempted to get this done myself and I do not really understand looping and checking record sets to ensure there is a value. SO, I am asking the guru's to take a look at this project and see I am even worthy of being helped.

I tried using a loop sendobject email out but it is not working. It sends one record and thats it. I am also wanting a CC added by looking up in quesrry as well and i can not get that to work so i took it out of the code. Please take a look at my database and HELP!!! My code reads as follows which i attempted to copy from a differnt Thread.

Public Function SendallEMail()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strRef As String
Dim strMessage As String
Dim strEmail2 As String

Set rsEmail = CurrentDb.OpenRecordset("qry_Time_Followup")

strRef = rsEmail.fields("Subject").Value
strEmail = rsEmail.fields("Email_Address").Value
strMessage = rsEmail.fields("Journal_Notes").Value

rsEmail.MoveNext

Do While Not rsEmail.EOF
strRef = rsEmail.fields("Subject").Value
strEmail = rsEmail.fields("Email_Address").Value
strMessage = rsEmail.fields("Journal_Notes").Value
rsEmail.MoveNext
Loop

rsEmail.MoveFirst
rsEmail.MoveNext

Do While Not rsEmail.EOF
strRef = rsEmail.fields("Subject").Value
strEmail = rsEmail.fields("Email_Address").Value
strMessage = rsEmail.fields("Journal_Notes").Value

rsEmail.MoveNext
Loop

DoCmd.SendObject , , , strEmail, , , strRef, strMessage, False, False

Set rsEmail = Nothing
MsgBox "The emails are in your emails Outbox and will be sent automatically during the next send recieve"
End Function

 

[DB7]

Public Function SendallEMail()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strRef As String
Dim strMessage As String
Dim strEmail2 As String

Set rsEmail = CurrentDb.OpenRecordset("qry_Time_Followup")

Do Until rsEmail.EOF

strRef = rsEmail.fields("Subject").Value
strEmail = rsEmail.fields("Email_Address").Value
strMessage = rsEmail.fields("Journal_Notes").Value

DoCmd.SendObject , , , strEmail, , , strRef, strMessage, False, False

rsEmail.MoveNext

Loop

rsEmail.Close: Set rsEmail = Nothing

MsgBox "The emails are in your emails Outbox and will be sent automatically during the next send recieve"

End Function

 

[Alloyd]

I did it - works great except for 1 thing

If my querry has a null record for [add_email_to] then it give me an error. Is it possible that if it is null, then not CC anyone and to continue without an error. I know in the Query, I can add an email using the iff statement, but I would rather not see in the log where it emailed someone. Here is what I added for the CC in the code so far and it works great as long as I have someones email address in the [add_email_to] field.

Public Function SendallEMail()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strRef As String
Dim strMessage As String
Dim strEmail2 As String

Set rsEmail = CurrentDb.OpenRecordset("qry_Time_Followup")

Do Until rsEmail.EOF

strRef = rsEmail.fields("Subject").Value
strEmail = rsEmail.fields("Email_Address").Value
strMessage = rsEmail.fields("Journal_Notes").Value
strEmail2 = rsEmail.fields("Add_Email_To").Value

DoCmd.SendObject , , , strEmail, strEmail2, , strRef, strMessage, False, False

rsEmail.MoveNext

Loop

rsEmail.Close: Set rsEmail = Nothing

MsgBox "The emails are in your emails Outbox and will be sent automatically during the next send recieve"

End Function

 

[DB7]

I'm not sure if SendObject, accepts empty strings, but if so...

strRef = rsEmail.fields("Subject").Value
strEmail = rsEmail.fields("Email_Address").Value
strMessage = rsEmail.fields("Journal_Notes").Value
strEmail2 = Nz(rsEmail.fields("Add_Email_To").Value,"")

 

[Alloyd]

that solved the Null issue

That does allow the Carbon Copy field to be null and still sent out the email. That was great.

I am glad to see people who help... you are by far an excellent resource of the Forums. Thanks a million... when I am done, my database will allow me to set a date and time to followup on and if the followup has not been done, it will automatically send me an email reminder and with the ability to make alias email accounts that will forward to my primary email, secondary email and my call phone... I might just not forget to keep in contact with a client when I say I am going to call. THIS IS AWESOME....

Many props and much appreciation to you DB7... you ROCK! (No icon for a BEER, but I will have one just for you while watching superbowl)

 

[Alloyd]

Once AGAIN... i need some advice

I had made a form and set timer interval to 60000 (once a Minute). I then added my code for module and now my system is doggie slow. It is a P4 3.2 with 1GIG Ram running Server2000... all i use it for is my personal TELNET server to my application. Did I go and make a BOOBOO with something that causes this to run slow? Here is my final code I am running.

Public Function SendallEMail()

Dim rsEmail As DAO.Recordset
Dim strEmail As String
Dim strRef As String
Dim strMessage As String
Dim strEmail2 As String
Dim strB1 As String
Dim strB2 As String


Set rsEmail = CurrentDb.OpenRecordset("qry_Time_Followup")

If rsEmail.RecordCount > 0 Then

DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_Time_Followup_Add_LOG"
DoCmd.SetWarnings True

Do Until rsEmail.EOF

strRef = rsEmail.fields("Subject").Value
strEmail = rsEmail.fields("Email_Address").Value
strMessage = rsEmail.fields("Journal_Notes").Value
strEmail2 = Nz(rsEmail.fields("Add_Email_To").Value, "")
strB1 = rsEmail.fields("B1Contact").Value
strB2 = rsEmail.fields("B2Contact").Value

' NOTE: The line right above this for strEmail2 with the Nz allows it to continue regardless if there is no Carbon Coby email address placed.

DoCmd.SendObject , , , strEmail, strEmail2, , strRef, strMessage & vbCrLf & vbCrLf & strB1 & vbCrLf & strB2, False, False

rsEmail.MoveNext

Loop

rsEmail.Close: Set rsEmail = Nothing

'MsgBox "The emails are in your emails Outbox and will be sent automatically during the next send recieve"

Else

rsEmail.Close: Set rsEmail = Nothing

'MsgBox "No emails to Send"

End If

End Function

 

[DB7]

I'm not positive Alloyd, but I would consider puting a DoEvents command, within eother the recordset loop, or, at least, the timer loop. Maybe both?

Quote;

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.

DoEvents is most useful for simple things like allowing a user to cancel a process after it has started, for example a search for a file. For long-running processes, yielding the processor is better accomplished by using a Timer or delegating the task to an ActiveX EXE component.. In the latter case, the task can continue completely independent of your application, and the operating system takes case of multitasking and time slicing.

Caution Any time you temporarily yield the processor within an event procedure, make sure the procedure is not executed again from a different part of your code before the first call returns; this could cause unpredictable results. In addition, do not use DoEvents if other applications could possibly interact with your procedure in unforeseen ways during the time you have yielded control.