Sending E-mails in macro;No memory

P

Peter Adema

In Access I try to send automatically several e-mails.
I have a data table with ± 2500 records and an adres table
with 34 records.
In visual basic I do the next steps:
I open a query and filter on the branch I need. The I send
the mail as an XLS format to the found recipient.
After two mails, I get an error message, that I do not
have enough memory. According to out IT-people this is
not the case.
I can send two mails, but if the Address table has more addressen, I get a
memory error.
This is a part of the VB coding:

Private Sub mail_Click()
On Error GoTo Err_mail_Click
Dim kantoor As String
Dim mail As String
Dim mail2 As String

Dim dbsyti As DAO.Database
Dim rstadres As DAO.Recordset
DoCmd.SetWarnings off
Set dbsyti = CurrentDb()
Set rstadres = dbsyti.OpenRecordset("adres", dbOpenDynaset)
With rstadres
Do Until .EOF
mail = !
kantoor = ![ktr]
mail2 = ![email2]
If mail2 <> "nvt" Then
DoCmd.OpenQuery "q_output", acViewNormal,
acEdit
DoCmd.ApplyFilter , "branch =""" & kantoor
& """"
DoCmd.SendObject acSendQuery, "Q_output",
acFormatXLS, mail, mail2, , "Nog te factureren dossiers
kantoor """ & kantoor & """", " **AUTOMATISCHE MAIL**" ,
False
Else
DoCmd.OpenQuery "q_output", acViewNormal, acEdit
DoCmd.ApplyFilter , "branch =""" & kantoor
& """"
DoCmd.SendObject acSendQuery, "Q_output",
acFormatXLS, mail, , , "Nog te factureren dossiers
kantoor """ & kantoor & """", "**AUTOMATISCHE MAIL**",False
End If
DoCmd.Close
.MoveNext
Loop
End With
rstadres.Close
DoCmd.SetWarnings warningson
Exit_mail_Click:
Exit Sub
Err_mail_Click:
MsgBox Err.Description
Resume Exit_mail_Click

End Sub
 
J

John Nurick

Hi Peter,

A couple of thoughts:

1) Make sure you have installed the service pack(s) for your versions of
Access and (especially) the Jet database engine. See
http://www.microsoft.com/downloads/search.aspx?

2) Set a breakpoint near the start of your code and step through it to
find out which statement produces the out of memory error. Then search
the Microsoft knowledgebase or the newsgroup archive at
http://groups.google.com/advanced_group_search? for information on the
particular problem.

3) Check out Tony Toews's Access email FAQ at
http://www.granite.ab.ca/access/email.htm


On Wed, 13 Oct 2004 02:03:02 -0700, "Peter Adema" <Peter
In Access I try to send automatically several e-mails.
I have a data table with ± 2500 records and an adres table
with 34 records.
In visual basic I do the next steps:
I open a query and filter on the branch I need. The I send
the mail as an XLS format to the found recipient.
After two mails, I get an error message, that I do not
have enough memory. According to out IT-people this is
not the case.
I can send two mails, but if the Address table has more addressen, I get a
memory error.
This is a part of the VB coding:

Private Sub mail_Click()
On Error GoTo Err_mail_Click
Dim kantoor As String
Dim mail As String
Dim mail2 As String

Dim dbsyti As DAO.Database
Dim rstadres As DAO.Recordset
DoCmd.SetWarnings off
Set dbsyti = CurrentDb()
Set rstadres = dbsyti.OpenRecordset("adres", dbOpenDynaset)
With rstadres
Do Until .EOF
mail = !
kantoor = ![ktr]
mail2 = ![email2]
If mail2 <> "nvt" Then
DoCmd.OpenQuery "q_output", acViewNormal,
acEdit
DoCmd.ApplyFilter , "branch =""" & kantoor
& """"
DoCmd.SendObject acSendQuery, "Q_output",
acFormatXLS, mail, mail2, , "Nog te factureren dossiers
kantoor """ & kantoor & """", " **AUTOMATISCHE MAIL**" ,
False
Else
DoCmd.OpenQuery "q_output", acViewNormal, acEdit
DoCmd.ApplyFilter , "branch =""" & kantoor
& """"
DoCmd.SendObject acSendQuery, "Q_output",
acFormatXLS, mail, , , "Nog te factureren dossiers
kantoor """ & kantoor & """", "**AUTOMATISCHE MAIL**",False
End If
DoCmd.Close
.MoveNext
Loop
End With
rstadres.Close
DoCmd.SetWarnings warningson
Exit_mail_Click:
Exit Sub
Err_mail_Click:
MsgBox Err.Description
Resume Exit_mail_Click

End Sub[/QUOTE]
Click to expand...
 
P

Peter Adema

John, thnaks for your suggestions. I updated my service packs.
Than I ran the VB step by step. At the following point it stopped:
DoCmd.ApplyFilter , "branch =""" & kantoor & """"
I got a runtime error 2491, something about a form/report which is not bound
to a table or query.
I have searched via Google, and on this site, but found nothing like my
error. Can you help me again?
Thanks.


John Nurick said:
Hi Peter,

A couple of thoughts:

1) Make sure you have installed the service pack(s) for your versions of
Access and (especially) the Jet database engine. See
http://www.microsoft.com/downloads/search.aspx?

2) Set a breakpoint near the start of your code and step through it to
find out which statement produces the out of memory error. Then search
the Microsoft knowledgebase or the newsgroup archive at
http://groups.google.com/advanced_group_search? for information on the
particular problem.

3) Check out Tony Toews's Access email FAQ at
http://www.granite.ab.ca/access/email.htm


On Wed, 13 Oct 2004 02:03:02 -0700, "Peter Adema" <Peter
In Access I try to send automatically several e-mails.
I have a data table with ± 2500 records and an adres table
with 34 records.
In visual basic I do the next steps:
I open a query and filter on the branch I need. The I send
the mail as an XLS format to the found recipient.
After two mails, I get an error message, that I do not
have enough memory. According to out IT-people this is
not the case.
I can send two mails, but if the Address table has more addressen, I get a
memory error.
This is a part of the VB coding:

Private Sub mail_Click()
On Error GoTo Err_mail_Click
Dim kantoor As String
Dim mail As String
Dim mail2 As String

Dim dbsyti As DAO.Database
Dim rstadres As DAO.Recordset
DoCmd.SetWarnings off
Set dbsyti = CurrentDb()
Set rstadres = dbsyti.OpenRecordset("adres", dbOpenDynaset)
With rstadres
Do Until .EOF
mail = !
kantoor = ![ktr]
mail2 = ![email2]
If mail2 <> "nvt" Then
DoCmd.OpenQuery "q_output", acViewNormal,
acEdit
DoCmd.ApplyFilter , "branch =""" & kantoor
& """"
DoCmd.SendObject acSendQuery, "Q_output",
acFormatXLS, mail, mail2, , "Nog te factureren dossiers
kantoor """ & kantoor & """", " **AUTOMATISCHE MAIL**" ,
False
Else
DoCmd.OpenQuery "q_output", acViewNormal, acEdit
DoCmd.ApplyFilter , "branch =""" & kantoor
& """"
DoCmd.SendObject acSendQuery, "Q_output",
acFormatXLS, mail, , , "Nog te factureren dossiers
kantoor """ & kantoor & """", "**AUTOMATISCHE MAIL**",False
End If
DoCmd.Close
.MoveNext
Loop
End With
rstadres.Close
DoCmd.SetWarnings warningson
Exit_mail_Click:
Exit Sub
Err_mail_Click:
MsgBox Err.Description
Resume Exit_mail_Click

End Sub[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

No memory while sending mail HELP! 0
Run-time error 2004 out of memory.Help 0
E-mail query to recipients in table - performance and security 1
Multiple DoCmd.SendObject only sending first e-mail 1

Top