VBA Copy table from Outlook email body to Excel

[Gertdj]

Hi,

I am trying to copy a table from email and paste it into Excel with VBA for further manipulation.
I found code on the internet and all seems to work 100%, except the final part where I need to paste the table into the new worksheet.

I get the following error: "Runtime error 1004 - Microsoft Excel cannot paste the data".

If I switch to the open workbook I can manually paste using Ctrl + V and the it works perfectly.


Sub ExportTableToExcel()

Dim xMailItem As MailItem
Dim xTable As Word.Table
Dim xDoc As Word.Document
Dim xExcel As Excel.Application
Dim xWb As Workbook
Dim xWs As Worksheet
Dim I As Integer
Dim xRow As Integer

'On Error Resume Next

Set xExcel = New Excel.Application
Set xWb = xExcel.Workbooks.Add
xExcel.Visible = True
Set xWs = xWb.Sheets(1)
xRow = 1

'Get mail item.
Set xMailItem = Application.ActiveExplorer.CurrentFolder.Items("Test Table")

Set xDoc = xMailItem.GetInspector.WordEditor
For I = 1 To xDoc.Tables.Count
Set xTable = xDoc.Tables(I)

xTable.Range.Copy

xWs.Paste
xRow = xRow + xTable.Rows.Count + 1
xWs.Range("A" & CStr(xRow)).Select
Next

End Sub

 

[Gertdj]

Hi,

I am running this code on my corporate PC at work and is getting this error.
I have now moved it over to my personal PC and the code is working perfectly.
Could this be a corporate restriction or maybe some setting in Office?