Automation Error

[AlliCarr]

Hi,

One user of my database is getting the error below when clicking a save button.

The code behind the button sends an email and I wondered whether this was because the button had been clicked twice which would have caused the code to try running when it already was. However, I have tried to replicate the error and when I click the button twice it still worked for me. The code also works for other users and research so far online suggests this is an outlook issue but I have been unable to find a fix. The code behind the button is below and I have redacted file paths and email addresses.

Any help would be appreciated.

Private Sub SaveFinalAuth_Click()
    On Error GoTo ErrorHandler

' Declare variables
    Dim olApp As Object
    Dim olNameSpace As Object
    Dim olMail As Object
    Dim SafeItem As Object
    Dim Redemption As Object
    Const olFormatHTML As Long = 2 ' HTML format constant

    Set Redemption = CreateObject("Redemption.RDOSession")
    Dim OrgURN As String
    Dim GrtURN As String
    Dim strFolder As String
    Dim strGrantFolder As String
    Dim strFile As String
    Dim strSaveAs As String

' Declare file path and attachment
    Const strParent = 
    OrgURN = "Org URN " & Me.OrganisationURN
    GrtURN = "Grant URN " & Me.GrantURN
    strFolder = strParent & OrgURN
    strGrantFolder = strFolder & "\" & GrtURN
    strFile = strGrantFolder & "\" & "Bank Statement.pdf"
    strSaveAs = strGrantFolder & "\" & "Payment ready to pay.msg"

' Ensure the save directory exists; create if necessary
    If Dir(strGrantFolder, vbDirectory) = "" Then
        MkDir strGrantFolder
    End If

' Check if the bank statement file exists
    If Dir(strFile) = "" Then
        MsgBox "Bank statement does not exist, please check the file", vbOKOnly + vbCritical
        Exit Sub
    End If

' If final authorisation date is not completed, show a message box
    If IsNull(Me.FinalAuthorisationDate) Then
        MsgBox "Please add the date", vbOKOnly
    Else
    
    ' Email message text
        Dim msg As String
        msg = "Organisation Name: " & Me.OrganisationName & "<p>" _
            & "Grant URN " & Me.GrantURN & "<p>" _
            & "Payment of " & Format(CCur(Me.PaymentAmount), "Currency") & " was authorised on " _
            & Me.FinalAuthorisationDate & " by " & Me.FinalAuthorisation & " and is ready to pay." & "<p>" _
            & "Sort Code: " & Me.SortCode & "<p>" _
            & "Account No: " & Me.AccountNumber

    ' Create Outlook session
        Set olApp = CreateObject("Outlook.Application")
        Set olNameSpace = olApp.GetNamespace("MAPI")

    ' Create a new mail item
        Set olMail = olApp.CreateItem(0) ' 0 = olMailItem
        Set SafeItem = CreateObject("Redemption.SafeMailItem")
        SafeItem.Item = olMail

    ' Set sender address, body format, recipient, subject, attach file, and save to file
 
        SafeItem.BodyFormat = olFormatHTML
        SafeItem.HTMLBody = msg
        SafeItem.To =
        SafeItem.Subject = "Payment authorised and ready to pay"
        SafeItem.Attachments.Add strFile
        SafeItem.Send
        SafeItem.SaveAs strSaveAs, 3 ' Save as .msg file
        
    ' Disable final authorisation date and set payment status
        Me.FinalAuthorisationDate.Enabled = False
        Me.PaymentStatus = "Awaiting payment"

    ' Disable final authorisation save
        Me.SaveFinalAuth.Enabled = False
        Me.SaveGrant.Enabled = True
        
    ' Confirmation message and close
        MsgBox "Payment authorised"
        Me.Dirty = False
        DoCmd.Close

    ' Clean up
        Set SafeItem = Nothing
        Set olMail = Nothing
        Set olNameSpace = Nothing
        Set olApp = Nothing
    End If

Exit Sub

ErrorHandler:
    Dim ErrMsg As String
    ErrMsg = Err.Number & ":" & Err.Description
    MsgBox ErrMsg
End Sub

 

[dalski]

Are they using New Outlook; if so I believe that is the issue; does not allow manipulation through VBA. Probably not but just incase it was an easy fix; with a rapid response & got lucky.

 

[CJ_London]

SafeItem.To =

Suggest step through the code to determine which line triggers the issue - perhaps this one?

 

[Gasman]

Try setting a filter in outlook, then run the code.

 

[The_Doc_Man]

The error -2147418107 in hexadecimal is 0x80010005, which suggests an error with a remote procedure call ("remote" in this case more spcifically meaning attempt to call a procedure in a module being activated by Access rather than a procedure that is intrinsically part of Access. I.e. tried to run something from an application object that launched a child task of MSACCESS.EXE's main task. This is somewhat of a "catch-all" error in that it is returned because the thing you called COULDN'T respond with an error. So in essence it is an interface error, not a failure of something inside the remote code being called.

@dalski has made a good suggestion. See whether the person is using Classic Outlook or New Outlook. You cannot run automation with New Outlook. Usually, when you launch a Windows app that involves a ribbon, you can find the File tab and get a Help or an About option that would lead you somewhere that will tell you if it is new or classic Outlook.

IF this person is using New Outlook, your choices are to somehow load Classic Outlook or - if you are always and only SENDING mail, never trying to receive it - you could adapt your code to use CDO - Collaboration Data Object - code, which CAN send mail. It does not, however, use MAPI.

 

[Isaac]

He's using Redemption. That IS the workaround to the outlook issue, isn't it?

 

[The_Doc_Man]

He's using Redemption. That IS the workaround to the outlook issue, isn't it?

Before you pointed that out, I had never heard of Redemption. It hasn't been commonly discussed on the forum and I AM retired now, so don't keep up with as much as I used to.

However, the comments on what that error code means still apply. If Redemption can't handle that particular function, I might expect that class of error message.

Also note in the code that though he creates a Redemption object, he ALSO creates an Outlook object later in the sequence. If THAT is the source of the error, then using Redemption might not matter. If the created Outlook object can't handle automation, that is where it will die.

 

[Isaac]

It's a tough one with no Debug option and no way to know which line of code errs, almost looks a bit like a runtime error but not quite.

 

[The_Doc_Man]

It's a tough one with no Debug option and no way to know which line of code errs, almost looks a bit like a runtime error but not quite.

Code 0x80010005 is always a run-time error because compilers can never know when an external object will barf at a given request.

 

[Isaac]

I meant runtime as in he was using Access runtime, and they never come with debug option.
I know the compiler can't catch it, that's not what i meant

 

[The_Doc_Man]

The format of the error (-2147etc.) may be a sign of Access Runtime, but the actual error code suggests that the VBA ATTEMPTED the operation but got the catch-all error response. I would expect a different error message if it it never tried the operation.

When I was experimenting with Excel's SmartArt features when trying to draw a family tree, I used to get that catch-all with a different app number (the 001 is the app number). When the interface doesn't like you, it gives a different error than that the interface failed.