Loop

[Gerrit.B]

I have a access2000 database and in 1 table i have all AirWayBillNumbers.
The query will select the numbers.
After that a macro is mailing the list of numbers.
Still 1 problem to solve.
HowTo create a mail with only 99 numbers folowed by a second mail with the following 99 record till the end of the list.
A module is creating the mail.

How to create a loop to seperate the file in mails of max 99 records.

Option Compare Database
Option Explicit

Public cnn As New ADODB.Connection

Function fncMailTrackTraceFile()

Set cnn = CurrentProject.Connection

Dim rstList As New ADODB.Recordset
Dim strMsgTxt As String

rstList.Open "CreateTrackTraceMailFile", cnn, adOpenStatic, adLockReadOnly

strMsgTxt = ""

rstList.MoveFirst
Do Until rstList.EOF
strMsgTxt = strMsgTxt & rstList.Fields(0).Value & Chr$(13)
rstList.MoveNext
Loop

DoCmd.SendObject acSendNoObject, , , "records@mailto.com", , , , strMsgTxt



End Function

 

[mdusoe]

Maybe a change like this:

Option Compare Database 
Option Explicit 

Public cnn As New ADODB.Connection 

Function fncMailTrackTraceFile() 

Set cnn = CurrentProject.Connection 

Dim rstList As New ADODB.Recordset 
Dim strMsgTxt As String 
Dim x As Integer

rstList.Open "CreateTrackTraceMailFile", cnn, adOpenStatic, adLockReadOnly 

strMsgTxt = "" 
x = 0
rstList.MoveFirst 
Do Until rstList.EOF 
   If x = 99 then
      DoCmd.SendObject acSendNoObject, , , "records@mailto.com", , , , strMsgTxt 
      x=0
      strMsgTxt = ""
   End if
   strMsgTxt = strMsgTxt & rstList.Fields(0).Value & Chr$(13) 
   rstList.MoveNext 
   x = x + 1
Loop 

If strMsgTxt <> "" Then
   DoCmd.SendObject acSendNoObject, , , "records@mailto.com", , , , strMsgTxt 
End If


End Function

Does this help?

Mike.

 

[Gerrit.B]

Works perfect

Is it also possible to define the email address of the sender because i do not want to use my default email address.

Thanx