Copy e-mail body to Excel through Access

[mikenelena]

I am looking to bring data from an Outlook message into Access using Excel as a "middleman" for ease of referencing the data. In testing I've been able to copy successfully from Outlook to Excel, and import data successfully from Excel into Access. Now I'm just looking to handle the entire process from Access. The actual copying of the e-mail body is where I'm having difficulty. The line of code that worked in Outlook vba is not working in Access vba. I'm thinking I have a problem with a variable dim, or set, but I can't seem to work it out. I can close the open e-mail from Access, so I know the applications are at least talking to one another. If anyone could help me iron this out, I'd be very grateful. Thanks!

Private Sub cmdNewFromEmail_Click()

Dim strWhere As String

Dim xl As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim filePath As String

Dim objol As Outlook.Application
Dim MyInspector As Outlook.Inspector
Dim objItem As Outlook.MailItem
Dim CurrentMessage As Outlook.MailItem

Set xl = New Excel.Application
Set xlBook = xl.Workbooks.Open(filePath)
Set xlSheet = xlBook.Worksheets(1)

'Set OL = GetObject(, "Outlook.Application")
Set objol = New Outlook.Application
Set MyInspector = Outlook.ActiveInspector
Set objItem = MyInspector.CurrentItem

filePath = "S:\UserName\" & "Excel Test File" & ".xlsx"

xl.Visible = True
'OL.Visible = True


    DoCmd.GoToRecord , , acNewRec
           Me.File_Number = Nz(DMax("File_Number", "ClaimInfo1", strWhere), 0) + 1      'Setting up a new file in Access
           
           Me.Invoice_Number = Me.File_Number & "-01"
           
           DoCmd.RunCommand acCmdSaveRecord
           
 Me.SubformContainer.SourceObject = "Appraisals_Subform2"   'Binds Client Billing tab to Invoicing form when setting up new file 
 Forms!Invoicing_Form.TabCtlEval = 0                        'Sets focus on the appropriate tab.

'-----------------------------------------------------------------------------------------------------------------

    With objItem
    
    'Set CurrentMessage = ActiveInspector.CurrentItem
    Set CurrentMessage = MyInspector.CurrentItem
    
    CurrentMessage.GetInspector().WordEditor.Range.FormattedText.Copy      ' *** This is the line causing the problem. Other things I've tried are commented out.***
    'CurrentMessage.Close olSave
    'CurrentMessage.GetInspector().WordEditor.Range.FormattedText
    'CurrentMessage.Copy
      
    End With
'-----------------------------------------------------------------------------------------------------------------
    With xlSheet
    
        .Range("A1").Select
        .Paste
        
        .Range("F5").Value = "Claim:  "
        .Range("G5").Formula = "=INDEX(B1:B100,MATCH(F5,A1:A100,0))"
     'many similar lines have been removed to keep the code short and readable here.
    End With

Me.Claim_Number = Range("G5")

End Sub