How to copy body of Outlook e-mail and paste into Excel using Access VBA?

Question

I am looking to bring data from an Outlook message into Access using Excel as a "middleman" for ease of referencing the data. I've been able to copy from Outlook to Excel, and import data from Excel into Access.

I'm looking to handle the entire process from Access.

The line of code, in Outlook VBA, to copy the e-mail body is not working in Access VBA. I'm thinking I have a problem with a variable dim, or set.

The error is

Run Time 287 Application-defined or object-defined error.

I can close the open e-mail with VBA from Access, so I know the applications are talking to one another.

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

@june7 the one the OP marked "this is the line causing the problem " — chris neilsen
– chris neilsen, Commented Dec 2, 2020 at 20:29
That line is referencing a lot of objects. I'd suggest breaking it down by Diming and assigning each layer in turn, to see where it breaks — chris neilsen
– chris neilsen, Commented Dec 2, 2020 at 20:32
@chrisneilsen I appreciate the suggestion. It sounds reasonable enough, but I'm not entire sure how to go about it. The line was built with the intellisense. I could maybe dim Myrange as a Range, and FormattedText as String, but what would I do with WordEditor? And how would I then piece them all together? — Michael Rousseau
– Michael Rousseau, Commented Dec 2, 2020 at 20:52
You’re attempting to assign a method (Copy) to an object variable. Just delete Set CurrentMessage = — Timothy Rylatt
– Timothy Rylatt, Commented Dec 2, 2020 at 20:53

mits · Accepted Answer · 2023-05-13 22:46:06Z

0

What about replacing the line
Set CurrentMessage = MyInspector.WordEditor.Range.FormattedText.Copy
with
objItem.Body = CurrentMessage.Body
?

Also there is no need having the With objItem End With block, as you are not using it anywhere. You could completely remove it.

edited May 13, 2023 at 22:46

answered May 13, 2023 at 22:39

mits

9263 gold badges12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to copy body of Outlook e-mail and paste into Excel using Access VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related