Access automation with Outlook stops before finishing

[Lisa]

I posted the following email under Outlook VBA newsgroup
with no response, but noticed this newsgroup today which
better fits my question. I need to find a solution or
workaround ASAP. Any ideas would be appreciated. Thanks!!

I have an Access 2000 database creating attachments and
then mailing them out to users via Outlook (2000 SR-1). I
have everything working except the program needs to send
out 47 and it stops at 33 (for one month) and another
month stops at 30 instead of the total 41. The program
runs without any errors.

Is there any restrictions on using office automation to
send emails through Outlook? Is there any sort of session
time out? Is there some sort of security setting that I
should know about? Below is the code I am using to create
the file and email.

Private Sub cmdExport_Click()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim olApp As New Outlook.Application
Dim olSpace As Outlook.NameSpace
Dim olMsg As Outlook.MailItem
Dim olAttach As Outlook.Attachment
Dim strSQL As String
Dim strFileName As String
Dim intExportCount As Integer

If Not IsNull(Me.cboMonth) Then
intExportCount = 0
strSQL = "SELECT BpnForm.BpnFormID,[OfficeID] &
[OfficeID4] AS FileName, [ID Requests].SubmitterEmail " & _
"FROM StatusTracking INNER JOIN ([ID
Requests] INNER JOIN BpnForm ON [ID Requests].RequestID =
BpnForm.RequestID) ON StatusTracking.RequestID =
BpnForm.RequestID " & _
"WHERE (((Format([ExpirationDate],'mmmm'))='"
& Me.cboMonth & "'));"

Set cnn = CurrentProject.Connection
rs.Open strSQL, cnn, adOpenStatic
Set olSpace = olApp.GetNamespace("MAPI")
olSpace.Logon FMPRG, "", False, False
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
glngIDForExport = .Fields(0)
strFileName = "D:\" & .Fields(1) & ".xls"
DoCmd.OutputTo
acOutputQuery, "qry_Export", acFormatXLS, strFileName
formatfile (strFileName)
Set olMsg = olApp.CreateItem(olMailItem)
olMsg.To = .Fields(2)
olMsg.Subject = "Registration
Recertification"
olMsg.Body = "Last test..." & vbCrLf &
vbCrLf & strFileName
olMsg.Importance = olImportanceHigh
Set olAttach = olMsg.Attachments.Add
(strFileName)
olMsg.Display
Set olAttach = Nothing
Set olMsg = Nothing
intExportCount = intExportCount + 1
.MoveNext
Loop
Select Case intExportCount
Case 1
MsgBox "Export Complete" & vbCrLf &
intExportCount & " Record Processed"
Case Else
MsgBox "Export Complete" & vbCrLf &
intExportCount & " Records Processed"
End Select
Else
MsgBox "No Records to Export"
End If
.Close
End With

Set rs = Nothing
cnn.Close
Set cnn = Nothing
Set olApp = Nothing

Else
MsgBox "Select Month Before Exporting"
End If

End Sub

 

[Cheryl Fischer]

I saw your thread in the Outlook newsgroup and, like Sue, am a little
stumped. Your code looks as if it should do exactly what you want.

One question: when you say that the routine should send out 47 emails but
sends only 33, how are you making that determination? Do you have some
other source that indicates you need 47 emails, but the loop through your
recordset iterates only 33 times, with your counter, intExportCount,
producing a number equalling 33? Or, does the loop iterate 47 times,
displaying 47 emails, and in Outlook you can only find 33 of them in the
Sent Items folder?



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I posted the following email under Outlook VBA newsgroup
with no response, but noticed this newsgroup today which
better fits my question. I need to find a solution or
workaround ASAP. Any ideas would be appreciated. Thanks!!

I have an Access 2000 database creating attachments and
then mailing them out to users via Outlook (2000 SR-1). I
have everything working except the program needs to send
out 47 and it stops at 33 (for one month) and another
month stops at 30 instead of the total 41. The program
runs without any errors.

Is there any restrictions on using office automation to
send emails through Outlook? Is there any sort of session
time out? Is there some sort of security setting that I
should know about? Below is the code I am using to create
the file and email.

Private Sub cmdExport_Click()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim olApp As New Outlook.Application
Dim olSpace As Outlook.NameSpace
Dim olMsg As Outlook.MailItem
Dim olAttach As Outlook.Attachment
Dim strSQL As String
Dim strFileName As String
Dim intExportCount As Integer

If Not IsNull(Me.cboMonth) Then
intExportCount = 0
strSQL = "SELECT BpnForm.BpnFormID,[OfficeID] &
[OfficeID4] AS FileName, [ID Requests].SubmitterEmail " & _
"FROM StatusTracking INNER JOIN ([ID
Requests] INNER JOIN BpnForm ON [ID Requests].RequestID =
BpnForm.RequestID) ON StatusTracking.RequestID =
BpnForm.RequestID " & _
"WHERE (((Format([ExpirationDate],'mmmm'))='"
& Me.cboMonth & "'));"

Set cnn = CurrentProject.Connection
rs.Open strSQL, cnn, adOpenStatic
Set olSpace = olApp.GetNamespace("MAPI")
olSpace.Logon FMPRG, "", False, False
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
glngIDForExport = .Fields(0)
strFileName = "D:\" & .Fields(1) & ".xls"
DoCmd.OutputTo
acOutputQuery, "qry_Export", acFormatXLS, strFileName
formatfile (strFileName)
Set olMsg = olApp.CreateItem(olMailItem)
olMsg.To = .Fields(2)
olMsg.Subject = "Registration
Recertification"
olMsg.Body = "Last test..." & vbCrLf &
vbCrLf & strFileName
olMsg.Importance = olImportanceHigh
Set olAttach = olMsg.Attachments.Add
(strFileName)
olMsg.Display
Set olAttach = Nothing
Set olMsg = Nothing
intExportCount = intExportCount + 1
.MoveNext
Loop
Select Case intExportCount
Case 1
MsgBox "Export Complete" & vbCrLf &
intExportCount & " Record Processed"
Case Else
MsgBox "Export Complete" & vbCrLf &
intExportCount & " Records Processed"
End Select
Else
MsgBox "No Records to Export"
End If
.Close
End With

Set rs = Nothing
cnn.Close
Set cnn = Nothing
Set olApp = Nothing

Else
MsgBox "Select Month Before Exporting"
End If

End Sub

 

[Lisa]

Cheryl-
Thank you for the recommendation. I did add the code in
there and it does say total records 47. I then took a
look at the way the data was being pulled. It wasn't
being created in order, so I reviewed the way the file was
created. The file is a meld of data from a few different
tables. Apparently, the "missing" files were not being
created because a corresponding field in one of the tables
was not populated even though data existed from other
tables that would be used in the file as well. (hopefully
you followed that...)

Therefore, I added info in this table and the routine
completes perfectly. I apologize to you and Sue for the
oversight and hope you didn't spend much time looking into
it. As I have only been doing coding for about two
months, I assumed it was something with my code or the
automation. I guess I should never assume...

Sorry and thanks again!! Problem solved.

-----Original Message-----
Lisa,

I am not aware of any built-in limitation on the number of emails you can
send and a memory problem would cause an ugly crash. At this point, since
your routine loops through your recordset without error, I am suspicious
that your ADO recordset is simply not returning all of the records you think
it should. Let's try this:

After this line:

olSpace.Logon FMPRG, "", False, False

insert the following:

rs.Movelast
Msgbox "Total records returned: " & rs.RecordCount

If the number returned is not 47, then you will need to compare the query
that you use to tell the users how many offices need to recertify with the
query that you use to build your ADO recordset.

Post back and let us know,

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I hope this clarification helps you and Sue.

I created a query which would let user could type in the
month (ie. February) and it would tell them how many
offices have to recertify that month. Therefore, I know
47 (the total number of recerts for Feb.) emails should be
created by the routine and sent. The routine only creates
33 files/emails. This 33 is the number that
intExportCount states, the number of emails created in
Outlook, and the number of files the routine created
(which I can verify because I didn't kill strFileName).

Could this be a memory issue or Microsoft limitation?
This morning I altered the code to Send instead of Display
to see if I received any different results and it did the
same thing.

What would you suggest as a workaround if no one can
figure this out? A flag that is checked when a message is
created, then after the first routine finishes have
another run to do the remaining??

Thanks for looking at this problem!!

-----Original Message-----
I saw your thread in the Outlook newsgroup and, like

Sue,
am a little

stumped. Your code looks as if it should do exactly

what
you want.

One question: when you say that the routine should

send
out 47 emails but

sends only 33, how are you making that determination? Do you have some
other source that indicates you need 47 emails, but the loop through your
recordset iterates only 33 times, with your counter, intExportCount,
producing a number equalling 33? Or, does the loop iterate 47 times,
displaying 47 emails, and in Outlook you can only find

33
of them in the

Sent Items folder?



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


I posted the following email under Outlook VBA newsgroup
with no response, but noticed this newsgroup today which
better fits my question. I need to find a solution or
workaround ASAP. Any ideas would be appreciated. Thanks!!

I have an Access 2000 database creating attachments and
then mailing them out to users via Outlook (2000 SR- 1). I
have everything working except the program needs to send
out 47 and it stops at 33 (for one month) and another
month stops at 30 instead of the total 41. The program
runs without any errors.

Is there any restrictions on using office automation to
send emails through Outlook? Is there any sort of session
time out? Is there some sort of security setting that I
should know about? Below is the code I am using to create
the file and email.

Private Sub cmdExport_Click()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim olApp As New Outlook.Application
Dim olSpace As Outlook.NameSpace
Dim olMsg As Outlook.MailItem
Dim olAttach As Outlook.Attachment
Dim strSQL As String
Dim strFileName As String
Dim intExportCount As Integer

If Not IsNull(Me.cboMonth) Then
intExportCount = 0
strSQL = "SELECT BpnForm.BpnFormID,[OfficeID] &
[OfficeID4] AS FileName, [ID

Requests].SubmitterEmail "
& _

"FROM StatusTracking INNER JOIN ([ID
Requests] INNER JOIN BpnForm ON [ID

Requests].RequestID
=

BpnForm.RequestID) ON StatusTracking.RequestID =
BpnForm.RequestID " & _
"WHERE (((Format

([ExpirationDate],'mmmm'))
='"

& Me.cboMonth & "'));"

Set cnn = CurrentProject.Connection
rs.Open strSQL, cnn, adOpenStatic
Set olSpace = olApp.GetNamespace("MAPI")
olSpace.Logon FMPRG, "", False, False
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
glngIDForExport = .Fields(0)
strFileName = "D:\" & .Fields(1) & ".xls"
DoCmd.OutputTo
acOutputQuery, "qry_Export", acFormatXLS, strFileName
formatfile (strFileName)
Set olMsg = olApp.CreateItem (olMailItem)
olMsg.To = .Fields(2)
olMsg.Subject = "Registration
Recertification"
olMsg.Body = "Last test..." & vbCrLf &
vbCrLf & strFileName
olMsg.Importance = olImportanceHigh
Set olAttach = olMsg.Attachments.Add
(strFileName)
olMsg.Display
Set olAttach = Nothing
Set olMsg = Nothing
intExportCount = intExportCount + 1
.MoveNext
Loop
Select Case intExportCount
Case 1
MsgBox "Export Complete" & vbCrLf &
intExportCount & " Record Processed"
Case Else
MsgBox "Export Complete" & vbCrLf &
intExportCount & " Records Processed"
End Select
Else
MsgBox "No Records to Export"
End If
.Close
End With

Set rs = Nothing
cnn.Close
Set cnn = Nothing
Set olApp = Nothing

Else
MsgBox "Select Month Before Exporting"
End If

End Sub



.

.

 

[Cheryl Fischer]

Lisa,

No apologies necessary and I am sure that Sue Mosher would agree! This is
what we are here for; sometimes, even if we cannot come up with a quick
answer, we can ask questions that will get you and others with questions
looking in the right direction.

I am happy to hear that your further investigation resolved the problem.
Good luck with your project.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Cheryl-
Thank you for the recommendation. I did add the code in
there and it does say total records 47. I then took a
look at the way the data was being pulled. It wasn't
being created in order, so I reviewed the way the file was
created. The file is a meld of data from a few different
tables. Apparently, the "missing" files were not being
created because a corresponding field in one of the tables
was not populated even though data existed from other
tables that would be used in the file as well. (hopefully
you followed that...)

Therefore, I added info in this table and the routine
completes perfectly. I apologize to you and Sue for the
oversight and hope you didn't spend much time looking into
it. As I have only been doing coding for about two
months, I assumed it was something with my code or the
automation. I guess I should never assume...

Sorry and thanks again!! Problem solved.

-----Original Message-----
Lisa,

I am not aware of any built-in limitation on the number of emails you can
send and a memory problem would cause an ugly crash. At this point, since
your routine loops through your recordset without error, I am suspicious
that your ADO recordset is simply not returning all of the records you think
it should. Let's try this:

After this line:

olSpace.Logon FMPRG, "", False, False

insert the following:

rs.Movelast
Msgbox "Total records returned: " & rs.RecordCount

If the number returned is not 47, then you will need to compare the query
that you use to tell the users how many offices need to recertify with the
query that you use to build your ADO recordset.

Post back and let us know,

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I hope this clarification helps you and Sue.

I created a query which would let user could type in the
month (ie. February) and it would tell them how many
offices have to recertify that month. Therefore, I know
47 (the total number of recerts for Feb.) emails should be
created by the routine and sent. The routine only creates
33 files/emails. This 33 is the number that
intExportCount states, the number of emails created in
Outlook, and the number of files the routine created
(which I can verify because I didn't kill strFileName).

Could this be a memory issue or Microsoft limitation?
This morning I altered the code to Send instead of Display
to see if I received any different results and it did the
same thing.

What would you suggest as a workaround if no one can
figure this out? A flag that is checked when a message is
created, then after the first routine finishes have
another run to do the remaining??

Thanks for looking at this problem!!

-----Original Message-----
I saw your thread in the Outlook newsgroup and, like Sue,
am a little
stumped. Your code looks as if it should do exactly what
you want.

One question: when you say that the routine should send
out 47 emails but
sends only 33, how are you making that determination?
Do you have some
other source that indicates you need 47 emails, but the
loop through your
recordset iterates only 33 times, with your counter,
intExportCount,
producing a number equalling 33? Or, does the loop
iterate 47 times,
displaying 47 emails, and in Outlook you can only find 33
of them in the
Sent Items folder?



--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


message
I posted the following email under Outlook VBA newsgroup
with no response, but noticed this newsgroup today which
better fits my question. I need to find a solution or
workaround ASAP. Any ideas would be appreciated.
Thanks!!

I have an Access 2000 database creating attachments and
then mailing them out to users via Outlook (2000 SR-
1). I
have everything working except the program needs to send
out 47 and it stops at 33 (for one month) and another
month stops at 30 instead of the total 41. The program
runs without any errors.

Is there any restrictions on using office automation to
send emails through Outlook? Is there any sort of
session
time out? Is there some sort of security setting that I
should know about? Below is the code I am using to
create
the file and email.

Private Sub cmdExport_Click()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim olApp As New Outlook.Application
Dim olSpace As Outlook.NameSpace
Dim olMsg As Outlook.MailItem
Dim olAttach As Outlook.Attachment
Dim strSQL As String
Dim strFileName As String
Dim intExportCount As Integer

If Not IsNull(Me.cboMonth) Then
intExportCount = 0
strSQL = "SELECT BpnForm.BpnFormID,[OfficeID] &
[OfficeID4] AS FileName, [ID Requests].SubmitterEmail "
& _
"FROM StatusTracking INNER JOIN ([ID
Requests] INNER JOIN BpnForm ON [ID Requests].RequestID
=
BpnForm.RequestID) ON StatusTracking.RequestID =
BpnForm.RequestID " & _
"WHERE (((Format ([ExpirationDate],'mmmm'))
='"
& Me.cboMonth & "'));"

Set cnn = CurrentProject.Connection
rs.Open strSQL, cnn, adOpenStatic
Set olSpace = olApp.GetNamespace("MAPI")
olSpace.Logon FMPRG, "", False, False
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
glngIDForExport = .Fields(0)
strFileName = "D:\" & .Fields(1)
& ".xls"
DoCmd.OutputTo
acOutputQuery, "qry_Export", acFormatXLS, strFileName
formatfile (strFileName)
Set olMsg = olApp.CreateItem (olMailItem)
olMsg.To = .Fields(2)
olMsg.Subject = "Registration
Recertification"
olMsg.Body = "Last test..." & vbCrLf &
vbCrLf & strFileName
olMsg.Importance = olImportanceHigh
Set olAttach = olMsg.Attachments.Add
(strFileName)
olMsg.Display
Set olAttach = Nothing
Set olMsg = Nothing
intExportCount = intExportCount + 1
.MoveNext
Loop
Select Case intExportCount
Case 1
MsgBox "Export Complete" & vbCrLf &
intExportCount & " Record Processed"
Case Else
MsgBox "Export Complete" & vbCrLf &
intExportCount & " Records Processed"
End Select
Else
MsgBox "No Records to Export"
End If
.Close
End With

Set rs = Nothing
cnn.Close
Set cnn = Nothing
Set olApp = Nothing

Else
MsgBox "Select Month Before Exporting"
End If

End Sub



.

.