OVERFLOW ERROR; doesn’t say where or what line or anything.

[mbelcher]

Hi All,

OVERFLOW ERROR; doesn’t say where or what line or anything.

This Access Application has been working for years without any issue and now it gets an overflow error on the first email. It uses outlook to send emails out. I am having an issue tracking it down as no one has changed the code in anyway before this error started taking place. I do know you can get an overflow error on dividing by zero of a number field that has a number to large over "32,768" in size. I can't seem to find an error like that anywhere so far.

It basically goes through all the beginning processes without a problem. When it sends the first email that is when the overflow error happens.


[Clicking Send Button] --------------------------------------------------------------------------
Private Sub cmdSend_Click()
'On Error GoTo Err_cmdSend_Click

Dim sMsg As String
Dim iTotalEmailsSent As Integer

gCancelSendEmail = False

If Len(nts(Me.CampaignDate)) = 0 Then
sMsg = "Please enter campaign date." & nl()
End If
If Len(nts(Me.CampaignName)) = 0 Then
sMsg = sMsg & "Please enter campaign name." & nl()
End If
If Len(sMsg) > 0 Then
MsgBox sMsg, , "Validation"
Exit Sub
End If

sMsg = "You are about to send a large number of emails! Are you sure you want to continue?"
If vbYes = MsgBox(sMsg, vbYesNo, GC_APPLICATION_TITLE) Then
DoCmd.RunCommand (acCmdSaveRecord)
DoCmd.OpenForm "frm_Progress"
DoEvents
'main send email routine
SendEmail Me.CampaignID, iTotalEmailsSent 'SendEmail is doing the mailing.
'cleanup
Me.txtEmailsSent.Requery
DoCmd.Close acForm, "frm_Progress"
End If

Exit_cmdSend_Click:
Exit Sub

Err_cmdSend_Click:
MsgBox Err.Description, vbOKOnly, GC_APPLICATION_TITLE
GoTo Exit_cmdSend_Click

End Sub
--------------------------------------------------------------------------


[SENDMAIL] ---------------------------------------------------------------
Function SendEmail(lCampaignID As Long, iTotalEmailsSent As Integer)
On Error GoTo Err_SendEmail

Dim rst As ADODB.Recordset
Dim sTemplate As String
Dim sTemplateFileName
Dim sBody As String
Dim sEmail As String
Dim sSubject As String
Dim sEmailHTML As String
Dim oUtils As Object
Dim iErrorNumber As Integer
Dim sErrorDescription As String
Dim sDebugMode As String
Dim sDebugEmailAddress As String
Dim iDebugMaxEmailsToSend As Integer

sDebugMode = GetRegistry("DebugMode")
If sDebugMode = "on" Then
sDebugEmailAddress = GetRegistry("DebugEmailAddress")
iDebugMaxEmailsToSend = GetRegistry("DebugMaxEmailsToSend")
End If

miTotalEmailsSent = 0
gCancelSendEmail = False
sSubject = GetRegistry("EmailSubject")
'get external main email template file
sTemplateFileName = GetRegistry("TemplatePath") & "/" & GetRegistry("TemplateEmailFileName")
GetTemplate sTemplateFileName, sTemplate

'build table cache (performance only)
'note that the invoice_header table when "not" cached actually crashed the PC when running a query
Forms("frm_Progress").ProgressCaption = "Caching data ..."
If GetRegistry("CacheTables") <> "off" Then
BuildCache lCampaignID
End If

'Delete unsubscribes
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdl_Unsubscribe", acViewNormal
DoCmd.SetWarnings True

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseClient
Forms("frm_Progress").ProgressCaption = "Getting data ..."
DoEvents
rst.Open "qsl_Renewal", , adOpenDynamic, adLockReadOnly, adCmdStoredProc
DoEvents
rst.MoveLast
DoEvents
Forms("frm_Progress").ProgressMax = rst.RecordCount
Forms("frm_Progress").ProgressCaption = "Processing magazine"
rst.MoveFirst

While Not rst.EOF And Not gCancelSendEmail And Not (sDebugMode = "on" And miTotalEmailsSent >= iDebugMaxEmailsToSend)
GetBody lCampaignID, rst, sEmail, sBody
sEmailHTML = Replace(sTemplate, "###BODY###", sBody)
sEmailHTML = Replace(sEmailHTML, "###IMAGE_PATH###", GetRegistry("ImagePath"))

If sDebugMode = "off" Then
'MsgBox "Sending Email To Real Address!"
iErrorNumber = OutlookSendEmailSafe(sEmail, sSubject, sEmailHTML)
Else
iErrorNumber = OutlookSendEmailSafe(sDebugEmailAddress, sSubject, sEmailHTML)
End If
If iErrorNumber <> 0 Then
ErrorAdd lCampaignID, sEmail, iErrorNumber, sErrorDescription
End If

miTotalEmailsSent = miTotalEmailsSent + 1

DoEvents
Wend

If gCancelSendEmail Then
ErrorAdd lCampaignID, "", -1000, "User clicked the cancel button"
Forms("frm_Main").txtCancel = "Send Email Cancelled!"
End If

rst.Close
Set rst = Nothing

'force delivery of emails now (note bug in some outlook versions means send/receive needs to be pressed)
Set oUtils = CreateObject("Redemption.MAPIUtils")
oUtils.DeliverNow
Set oUtils = Nothing

iTotalEmailsSent = miTotalEmailsSent

Exit_SendEmail:
Exit Function

Err_SendEmail:
MsgBox Err.Description, vbOKOnly, GC_APPLICATION_TITLE
GoTo Exit_SendEmail

End Function

Function BuildCache(lCampaignID As Long)

Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command

DoCmd.SetWarnings False
DoCmd.OpenQuery "qdl_FInvoice_Header_Cached", acViewNormal
DoEvents
'use local access table instead of SQL table
DoCmd.OpenQuery "qap_FInvoiceHeader_Cached", acViewNormal
DoEvents
'delete emails for people who have unsubscribed
DoCmd.OpenQuery "qdl_EmailUnsubscribe", acViewNormal
DoEvents
'use local access table instead of SQL table
DoCmd.OpenQuery "qdl_FStock_Cached", acViewNormal
DoEvents
DoCmd.OpenQuery "qap_FStock_Cached", acViewNormal
DoEvents
'use local access table instead of SQL table
DoCmd.OpenQuery "qdl_TitleMaster_Cache", acViewNormal
DoEvents
DoCmd.OpenQuery "qap_TitlesMaster_Cache", acViewNormal
DoEvents
DoCmd.SetWarnings True

'delete emails that have already been sent out for campaign (i.e. resend)
Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command


cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Procedures("qdl_EmailResend").Command
cmd.Parameters("[par_CampaignID]").Value = lCampaignID
cmd.Execute
Set cmd = Nothing
Set cat.ActiveConnection = Nothing
Set cat = Nothing

End Function
---------------------------------------------------------------


Looking for any ideas on where to start; I do know it happens when the first email is being sent. That would eliminate a lot I would think.

Any help is appreciated,

Thanks

[pbaldy]

First thing I'd do is comment out the On Error line and see what line is throwing the error. While in debug mode you can hover over variables and see what they hold. This may also help:

http://www.baldyweb.com/Debugging.htm

[mbelcher]

Hi All,

When running debug I found out where the error happens.

<code ---------------------------------------------------------- >
Function SendEmail(lCampaignID As Long, iTotalEmailsSent As Integer)
On Error GoTo Err_SendEmail

Dim rst As ADODB.Recordset
Dim sTemplate As String
Dim sTemplateFileName
Dim sBody As String
Dim sEmail As String
Dim sSubject As String
Dim sEmailHTML As String
Dim oUtils As Object
Dim iErrorNumber As Integer
Dim sErrorDescription As String
Dim sDebugMode As String
Dim sDebugEmailAddress As String
Dim iDebugMaxEmailsToSend As Integer

sDebugMode = GetRegistry("DebugMode")
If sDebugMode = "on" Then
sDebugEmailAddress = GetRegistry("DebugEmailAddress")
iDebugMaxEmailsToSend = GetRegistry("DebugMaxEmailsToSend")
End If

miTotalEmailsSent = 0
gCancelSendEmail = False
sSubject = GetRegistry("EmailSubject")
'get external main email template file
sTemplateFileName = GetRegistry("TemplatePath") & "/" & GetRegistry("TemplateEmailFileName")
GetTemplate sTemplateFileName, sTemplate

'build table cache (performance only)
'note that the invoice_header table when "not" cached actually crashed the PC when running a query
Forms("frm_Progress").ProgressCaption = "Caching data ..."
If GetRegistry("CacheTables") <> "off" Then
BuildCache lCampaignID
End If

'Delete unsubscribes
DoCmd.SetWarnings False
DoCmd.OpenQuery "qdl_Unsubscribe", acViewNormal
DoCmd.SetWarnings True

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseClient
Forms("frm_Progress").ProgressCaption = "Getting data ..."
DoEvents
rst.Open "qsl_Renewal", , adOpenDynamic, adLockReadOnly, adCmdStoredProc
DoEvents
rst.MoveLast
DoEvents
Forms("frm_Progress").ProgressMax = rst.RecordCount
Forms("frm_Progress").ProgressCaption = "Processing magazine"
rst.MoveFirst

While Not rst.EOF And Not gCancelSendEmail And Not (sDebugMode = "on" And miTotalEmailsSent >= iDebugMaxEmailsToSend)
GetBody lCampaignID, rst, sEmail, sBody
sEmailHTML = Replace(sTemplate, "###BODY###", sBody)
sEmailHTML = Replace(sEmailHTML, "###IMAGE_PATH###", GetRegistry("ImagePath"))

If sDebugMode = "off" Then
'MsgBox "Sending Email To Real Address!"
iErrorNumber = OutlookSendEmailSafe(sEmail, sSubject, sEmailHTML)
Else
iErrorNumber = OutlookSendEmailSafe(sDebugEmailAddress, sSubject, sEmailHTML)
End If
If iErrorNumber <> 0 Then
ErrorAdd lCampaignID, sEmail, iErrorNumber, sErrorDescription
End If

miTotalEmailsSent = miTotalEmailsSent + 1

DoEvents
Wend

If gCancelSendEmail Then
ErrorAdd lCampaignID, "", -1000, "User clicked the cancel button"
Forms("frm_Main").txtCancel = "Send Email Cancelled!"
End If

rst.Close
Set rst = Nothing

'force delivery of emails now (note bug in some outlook versions means send/receive needs to be pressed)
Set oUtils = CreateObject("Redemption.MAPIUtils")
oUtils.DeliverNow
Set oUtils = Nothing

iTotalEmailsSent = miTotalEmailsSent

Exit_SendEmail:
Exit Function

Err_SendEmail:
MsgBox Err.Description, vbOKOnly, GC_APPLICATION_TITLE
GoTo Exit_SendEmail
</code ---------------------------------------------------------- >


[FUNCTION SENDMAIL HAS A LINE:]
iErrorNumber = OutlookSendEmailSafe(sDebugEmailAddress, sSubject, sEmailHTML)



[THIS LINE JUMPS TO:]
<code ---------------------------------------------------------- >
'Description: uses Outlook Redemption to prevent the Outlook message box when sending email
'Website: http://www.dimastr.com/redemption/
'
Function OutlookSendEmailSafe(sTo As String, sSubject As String, sHTMLBody As String) As Integer
On Error GoTo Err_OutlookSendEmail

Dim oOutlook As Outlook.Application
Dim oMailItem As Outlook.MailItem
Dim oOutlookRecip As Outlook.Recipient
Dim sMsg
Dim oSafeMail As Object

Set oSafeMail = CreateObject("Redemption.SafeMailItem")
Set oOutlook = New Outlook.Application
Set oMailItem = oOutlook.CreateItem(olMailItem)
oSafeMail.Item = oMailItem

With oSafeMail.Item
.To = sTo
.Subject = sSubject
.HTMLBody = sHTMLBody
.Save
End With

'use safe send to avoid the outlook message box
oSafeMail.Send 'MBELCHER - This results in an error :OVERFLOW
'end safe send

'give operating system time to process (may help with Access stability)
DoEvents

Set oMailItem = Nothing
Set oSafeMail = Nothing
Set oOutlook = Nothing
OutlookSendEmailSafe = 0

Exit_OutlookSendEmail:
Exit Function

Err_OutlookSendEmail:
OutlookSendEmailSafe = Err
Select Case Err
Case 287:
sMsg = "Could not send email because user said 'No' to the security prompt!"
Case Else:
sMsg = Err.Description
End Select
MsgBox sMsg, vbOKOnly, GC_APPLICATION_TITLE
GoTo Exit_OutlookSendEmail

End Function
</code ---------------------------------------------------------- >

[IT ERRORS OUT HERE ON THIS LINE:] oSafeMail.Send

When it goes to send an email via outlook it overflows.

I am not sure how to troubleshoot the error since it is ACCESS interacting with outlook.

Any Ideas why it causes an overflow error on sending mail?

This program used to work and then magically now causes an overflow error.


Thanks.

[June7]

Note that forum CODE tags use [] not <>.

Nothing changed - code, data, form design?

[mbelcher]

It is trying to send 16,291 emails but it overflows on the FIRST one. It hits the send line and bam. It never gets past the very first email.


The first thing I check was integer fields as I have seen that error before.


IT is when it goes to send that it blows up. I am wondering how in the world to troubleshoot this. I guess a visit to the redemption site and a possible update could help. Maybe others are having issues as well.

[June7]

I was trying to test the code but it seems incomplete. For instance, what are the procedures GetRegistry and GetTemplate? And seems to be some global variables not declared in the code you posted.

If you want to provide db, follow instructions at bottom of my post.

[Rawb]

I notice that you're using a program to "get around" some limitations of Outlook. Although it's possible that the program (Redemption) doesn't have anything to do with the issue, you should make sure by removing it from the equation.

Try changing your code to send an e-mail straight through Outlook. If it sends correctly then the issue is likely Redemption and not anything in your code.

If it doesn't solve the issue, then it's most likely a problem in Outlook and not in Access itself. I would try running a Repair Install on your e-mail program and seeing if that helps.

[DepricatedZero]

It is trying to send 16,291 emails but it overflows on the FIRST one. It hits the send line and bam. It never gets past the very first email.


The first thing I check was integer fields as I have seen that error before.


IT is when it goes to send that it blows up. I am wondering how in the world to troubleshoot this. I guess a visit to the redemption site and a possible update could help. Maybe others are having issues as well.

In my experience, things don't magically stop working. I would actually see what the mailer class you're using is doing with the data from the DB - perhaps theres an invalid value in that first email that's causing it to blow out?

[mbelcher]

I did everything over again to see if it would fix it.


1. Registered Redemption.dll as Administrator.
2. Did an office repair.
3. Restarted the computer.
4. Ran the Access application again to send a test email.


I got the error “ActiveX can’t create object” again.






I did some more debugging and it seems to blow up right at "Set oOutlook = New Outlook.Application".


'Description: uses Outlook Redemption to prevent the Outlook message box when sending email
'Website: http://www.dimastr.com/redemption/
'
Function OutlookSendEmailSafe(sTo As String, sSubject As String, sHTMLBody As String) As Integer
On Error GoTo Err_OutlookSendEmail


Dim oOutlook As Outlook.Application
Dim oMailItem As Outlook.MailItem
Dim oOutlookRecip As Outlook.Recipient
Dim sMsg
Dim oSafeMail As Object


Set oSafeMail = CreateObject("Redemption.SafeMailItem")
Set oOutlook = New Outlook.Application








This is happening on two different machines.


Windows 2008 R2 with Access 2010
Windows 7 – 32bit with Access 2007




Sometimes you will get the overflow error as well.




At this point it doesn't seem like code but something at the point of calling outlook.


Any ideas?

[June7]

Did you eliminate Redemption from a test?

Here's how I declare and set Outlook objects:

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = "email address"
''.cc = ""
''.bcc = ""
.Subject = "Central Materials Laboratory Data"
.HTMLBody = "Construction data extract: " & Now
'.Attachments.add ()
.DeleteAfterSubmit = True 'to not save in sent bin
''.Display
.Send
End With