excel - 如何使用 Access VBA 复制 Outlook 电子邮件正文并粘贴到 Excel 中？

Question

我希望使用 Excel 作为“中间人”将 Outlook 邮件中的数据带入 Access，以便于引用数据。我已经能够从 Outlook 复制到 Excel，并将数据从 Excel 导入 Access。

我希望从 Access 处理整个过程。

Outlook VBA 中用于复制电子邮件正文的代码行在 Access VBA 中不起作用。我在想我有一个变量暗淡或设置的问题。

错误是

运行时 287 应用程序定义或对象定义的错误。

我可以使用 Access 中的 VBA 关闭打开的电子邮件，因此我知道应用程序正在相互通信。

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
    
    filePath = "S:\UserNanme\" & "Excel Test File" & ".xlsx"
    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
    
    xl.Visible = True
    'OL.Visible = True
    
    DoCmd.GoToRecord , , acNewRec

    'Setting up a new file in Access
    Me.File_Number = Nz(DMax("File_Number", "ClaimInfo1", strWhere), 0) + 1
               
    Me.Invoice_Number = Me.File_Number & "-01"
               
    DoCmd.RunCommand acCmdSaveRecord
               
    'Binds Client Billing tab to Invoicing form when setting up new file
    Me.SubformContainer.SourceObject = "Appraisals_Subform2"

    'Sets focus on the appropriate tab.
     Forms!Invoicing_Form.TabCtlEval = 0
    
    '---------------------------------------------------------------------
    
    With objItem
        
        'Set CurrentMessage = ActiveInspector.CurrentItem
        
        ' *** This is the line causing the problem.
        ' Other things I've tried are commented out.***
        Set CurrentMessage = MyInspector.WordEditor.Range.FormattedText.Copy    

        'Set CurrentMessage = MyInspector
        'CurrentMessage.GetInspector().WordEditor.Range.FormattedText.Copy
        '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))"
                    
    End With
    
    Me.Claim_Number = Range("G5")
        
End Sub 

标签： excelvbams-accessoutlookms-word