Forum Discussion
Gertdj
Oct 11, 2021Copper Contributor
VBA Copy table from email to Excel - Cannot paste the data - 1004
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 ...
Gertdj
Oct 12, 2021Copper Contributor
Yea_So
My humble apologies...
I managed to insert incorrect code from another project I am busy with.
Here is the correct Sub. It gives the error on the last line.
Sub GetTable()
Dim oLookInspector As Inspector
Dim oLookMailitem As MailItem
'Declare Word Variables.
Dim oLookWordDoc As Word.Document
Dim oLookWordTbl As Word.Table
'Declare Excel Variables.
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlWrkSheet As Excel.Worksheet
'Get mail item.
Set oLookMailitem = Application.ActiveExplorer.CurrentFolder.Items("Test Table")
'Get Active Inspector.
Set oLookInspector = oLookMailitem.GetInspector
'Get Word Editor object
Set oLookWordDoc = oLookInspector.WordEditor
'Create new Excel App.
Set xlApp = New Excel.Application
'Make visible.
xlApp.Visible = True
'Add new workbook.
Set xlBook = xlApp.Workbooks.Add
'
''Add a new worksheet.
'Set xlWrkSheet = xlBook.Worksheets.Add
Set xlWrkSheet = xlBook.Sheets(1)
'Grab the Word Table.
Set oLookWordTbl = oLookWordDoc.Tables(1)
'Copy the table.
oLookWordTbl.Range.Copy
'Paste it to the sheet.
xlWrkSheet.Paste Destination:=xlWrkSheet.Range("A1")
End Sub
My humble apologies...
I managed to insert incorrect code from another project I am busy with.
Here is the correct Sub. It gives the error on the last line.
Sub GetTable()
Dim oLookInspector As Inspector
Dim oLookMailitem As MailItem
'Declare Word Variables.
Dim oLookWordDoc As Word.Document
Dim oLookWordTbl As Word.Table
'Declare Excel Variables.
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlWrkSheet As Excel.Worksheet
'Get mail item.
Set oLookMailitem = Application.ActiveExplorer.CurrentFolder.Items("Test Table")
'Get Active Inspector.
Set oLookInspector = oLookMailitem.GetInspector
'Get Word Editor object
Set oLookWordDoc = oLookInspector.WordEditor
'Create new Excel App.
Set xlApp = New Excel.Application
'Make visible.
xlApp.Visible = True
'Add new workbook.
Set xlBook = xlApp.Workbooks.Add
'
''Add a new worksheet.
'Set xlWrkSheet = xlBook.Worksheets.Add
Set xlWrkSheet = xlBook.Sheets(1)
'Grab the Word Table.
Set oLookWordTbl = oLookWordDoc.Tables(1)
'Copy the table.
oLookWordTbl.Range.Copy
'Paste it to the sheet.
xlWrkSheet.Paste Destination:=xlWrkSheet.Range("A1")
End Sub
Yea_So
Oct 12, 2021Bronze Contributor
its a good thing I pointed it out, now the real vba affecionados can go at it. vba is not my forte
- GertdjOct 18, 2021Copper ContributorAnyone with a solution or suggestion?
I have now even tried another external forum and still nothing...
Surely someone from Microsoft must be able to assist here?- GertdjOct 18, 2021Copper ContributorHi,
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?