Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2007
    Posts
    18

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

    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]
    Code:
    --------------------------------------------------------------------------
    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****nCommand (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]
    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
    
    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,

    Mike
    Last edited by gvee; 04-16-13 at 11:24. Reason: [code] tags added

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so step through the code
    put a watch/breakpoint on the functions and step through the code using F8 till you find what line trips up
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As an outsider, trying to follow this isn't very easy, for me, especially since you didn't use the Code Tags to make it easier to read, but how many emails are you trying to send? In your function header you have

    iTotalEmailsSent As Integer

    and an Integer can only hold the aforementioned 32,768. Passing a number larger than that would pop this kind of error. Replacing the Integer with Long would expand that to 2,147,483,647, I believe.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2007
    Posts
    18
    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.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by belcherman View Post

    ...Any Ideas why it causes an overflow error on sending mail...
    Once again, how many emails are you attempting to send? If it's over 32,768, the limit for an Integer, I believe you're going to pop this kind of error.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if it used to work, and you have made no changes to the code, and it now doesnt' work then its highly likely its a system update (ie a library has been replaced)

    as the fault is in 'their' code I'd suggest you contact http://www.dimastr.com/redemption/
    it is there code that breaking. unless someone else here has used that library and already hit the problem its unlikely that anyone here will be able to magically guess what is happening

    just wondering if you could check the health or sanity of the Redemption.SafeMailItem object. ie make certain its initialised properly, make certain that its valid beforte attemtpong to send the email
    is it possible that there are some inbuilt error conditions. it could be that their internal error handler isn't cope with a error number of type long
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by healdem View Post
    if it used to work, and you have made no changes to the code, and it now doesnt' work then its highly likely its a system update (ie a library has been replaced)
    Quite possibly, but this scenario (used to work...no changes made...now doesn't work) in conjunction with an overflow error is not at all uncommon. It's often seen in sales/financial type apps where they're tracking YTD numbers. Everything runs smoothly, for months, until the YTD figure exceeds the size of the Field that holds the YTD, then kaboom! Hence my repeated questions about how many emails they're attempting to send! The developer apparently was expecting 32k, more or less, and more would pop this error.

    Either way, though, you're exactly right; when commercial apps suddenly go bad contacting the developer really should be the first line of defense!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Sep 2007
    Posts
    18
    It is trying to send 16,291 emails but it overflows on the FIRST one. It hits the send like 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.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The error is in the redemption object not your access code.
    Providing you are supplying correct information its not your code. Id argue that any object should be abke to gracefully handle data errors.


    Id want to check the redemption documentation and see if that can help
    Check its not a data error. Its easy to leap to conclusions looking for detail technical faults whrn it can be a data error.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2007
    Posts
    18
    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?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by belcherman View Post
    Any ideas?
    I'd start by contacting the folks that wrote/support Redemption.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •