VBA Copy table from email to Excel - Cannot paste the data - 1004

%3CLINGO-SUB%20id%3D%22lingo-sub-2833475%22%20slang%3D%22en-US%22%3EVBA%20Copy%20table%20from%20email%20to%20Excel%20-%20Cannot%20paste%20the%20data%20-%201004%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2833475%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20copy%20a%20table%20from%20email%20and%20paste%20it%20into%20Excel%20with%20VBA%20for%20further%20manipulation.%3C%2FP%3E%3CP%3EI%20found%20code%20on%20the%20internet%20and%20all%20seems%20to%20work%20100%25%2C%20except%20the%20final%20part%20where%20I%20need%20to%20paste%20the%20table%20into%20the%20new%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20the%20following%20error%3A%20%22Runtime%20error%201004%20-%20Microsoft%20Excel%20cannot%20paste%20the%20data%22.%3C%2FP%3E%3CP%3EIf%20I%20switch%20to%20the%20open%20workbook%20I%20can%20manually%20paste%20using%20Ctrl%20%2B%20V%20and%20the%20it%20works%20perfectly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20-%20I%20have%20spend%20so%20much%20time%20on%20this%20and%20is%20unable%20to%20find%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20PDF_To_Excel()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20setting_sh%20As%20Worksheet%3CBR%20%2F%3ESet%20setting_sh%20%3D%20ThisWorkbook.Sheets(%22Setting%22)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20pdf_path%20As%20String%3CBR%20%2F%3EDim%20excel_path%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3Epdf_path%20%3D%20setting_sh.Range(%22E11%22).Value%3CBR%20%2F%3Eexcel_path%20%3D%20setting_sh.Range(%22E12%22).Value%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDim%20fso%20As%20New%20FileSystemObject%3CBR%20%2F%3EDim%20fo%20As%20Folder%3CBR%20%2F%3EDim%20f%20As%20File%3C%2FP%3E%3CP%3ESet%20fo%20%3D%20fso.GetFolder(pdf_path)%3C%2FP%3E%3CP%3EDim%20wa%20As%20Object%3CBR%20%2F%3EDim%20doc%20As%20Object%3CBR%20%2F%3EDim%20wr%20As%20Object%3C%2FP%3E%3CP%3ESet%20wa%20%3D%20CreateObject(%22word.application%22)%3CBR%20%2F%3E%3CBR%20%2F%3Ewa.Visible%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20nwb%20As%20Workbook%3CBR%20%2F%3EDim%20nsh%20As%20Worksheet%3C%2FP%3E%3CP%3EFor%20Each%20f%20In%20fo.Files%3CBR%20%2F%3ESet%20doc%20%3D%20wa.documents.Open(f.Path%2C%20False%2C%20Format%3A%3D%22PDF%20Files%22)%3CBR%20%2F%3ESet%20wr%20%3D%20doc.Paragraphs(1).Range%3CBR%20%2F%3Ewr.WholeStory%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20nwb%20%3D%20Workbooks.Add%3CBR%20%2F%3ESet%20nsh%20%3D%20nwb.Sheets(1)%3CBR%20%2F%3Ewr.Copy%3CBR%20%2F%3E%3CBR%20%2F%3Ensh.Paste%3CBR%20%2F%3Enwb.SaveAs%20(excel_path%20%26amp%3B%20%22%5C%22%20%26amp%3B%20Replace(f.Name%2C%20%22.pdf%22%2C%20%22.xlsx%22))%3CBR%20%2F%3E%3CBR%20%2F%3E'Clipboard%20123%3CBR%20%2F%3Edoc.Close%20False%3CBR%20%2F%3Enwb.Close%20False%3CBR%20%2F%3E%3CBR%20%2F%3ENext%3CBR%20%2F%3E%3CBR%20%2F%3Ewa.Quit%3C%2FP%3E%3CP%3EMsgBox%20%22Done%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2833475%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2836659%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Copy%20table%20from%20email%20to%20Excel%20-%20Cannot%20paste%20the%20data%20-%201004%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836659%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564938%22%20target%3D%22_blank%22%3E%40Gertdj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fallonline365.com%2Fpower-query%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EPower%20Query%20allows%20you%20to%20import%20email%20attachments%20-%20allonline365%3C%2FA%3E%3C%2FP%3E%3CP%3Ewhy%20suffer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2836773%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Copy%20table%20from%20email%20to%20Excel%20-%20Cannot%20paste%20the%20data%20-%201004%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836773%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%3CBR%20%2F%3EThank%20you%20for%20the%20suggestion%20-%20I%20will%20definitely%20investigate%20further.%3CBR%20%2F%3EI%20am%20grabbing%20a%20table%20from%20the%20body%20of%20an%20email%20though%20and%20then%20sending%20it%20to%20Excel%20for%20further%20manipulation%20-%20not%20sure%20if%20Power%20Query%20is%20the%20best%20tool%20for%20this...%20disclaimer%20-%20I%20haven't%20worked%20with%20Power%20Query%20much%20before...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2836801%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Copy%20table%20from%20email%20to%20Excel%20-%20Cannot%20paste%20the%20data%20-%201004%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836801%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F564938%22%20target%3D%22_blank%22%3E%40Gertdj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20suggested%20it%20because%20your%20code%20mentioned%20pdf%2C%20as%20pdf%20can%20only%20be%20an%20attachment%20unless%20it%20is%20an%20embedded%20object.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20PDF_To_Excel()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20setting_sh%20As%20Worksheet%3CBR%20%2F%3ESet%20setting_sh%20%3D%20ThisWorkbook.Sheets(%22Setting%22)%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20pdf_path%20As%20String%3CBR%20%2F%3EDim%20excel_path%20As%20String%3CBR%20%2F%3E%3CBR%20%2F%3Epdf_path%20%3D%20setting_sh.Range(%22E11%22).Value%3CBR%20%2F%3Eexcel_path%20%3D%20setting_sh.Range(%22E12%22).Value%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDim%20fso%20As%20New%20FileSystemObject%3CBR%20%2F%3EDim%20fo%20As%20Folder%3CBR%20%2F%3EDim%20f%20As%20File%3C%2FP%3E%3CP%3ESet%20fo%20%3D%20fso.GetFolder(pdf_path)%3C%2FP%3E%3CP%3EDim%20wa%20As%20Object%3CBR%20%2F%3EDim%20doc%20As%20Object%3CBR%20%2F%3EDim%20wr%20As%20Object%3C%2FP%3E%3CP%3ESet%20wa%20%3D%20CreateObject(%22word.application%22)%3CBR%20%2F%3E%3CBR%20%2F%3Ewa.Visible%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20nwb%20As%20Workbook%3CBR%20%2F%3EDim%20nsh%20As%20Worksheet%3C%2FP%3E%3CP%3EFor%20Each%20f%20In%20fo.Files%3CBR%20%2F%3ESet%20doc%20%3D%20wa.documents.Open(f.Path%2C%20False%2C%20Format%3A%3D%22PDF%20Files%22)%3CBR%20%2F%3ESet%20wr%20%3D%20doc.Paragraphs(1).Range%3CBR%20%2F%3Ewr.WholeStory%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20nwb%20%3D%20Workbooks.Add%3CBR%20%2F%3ESet%20nsh%20%3D%20nwb.Sheets(1)%3CBR%20%2F%3Ewr.Copy%3CBR%20%2F%3E%3CBR%20%2F%3Ensh.Paste%3CBR%20%2F%3Enwb.SaveAs%20(excel_path%20%26amp%3B%20%22%5C%22%20%26amp%3B%20Replace(f.Name%2C%20%22.pdf%22%2C%20%22.xlsx%22))%3CBR%20%2F%3E%3CBR%20%2F%3E'Clipboard%20123%3CBR%20%2F%3Edoc.Close%20False%3CBR%20%2F%3Enwb.Close%20False%3CBR%20%2F%3E%3CBR%20%2F%3ENext%3CBR%20%2F%3E%3CBR%20%2F%3Ewa.Quit%3C%2FP%3E%3CP%3EMsgBox%20%22Done%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2836969%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20Copy%20table%20from%20email%20to%20Excel%20-%20Cannot%20paste%20the%20data%20-%201004%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836969%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%3CBR%20%2F%3EMy%20humble%20apologies...%3CBR%20%2F%3EI%20managed%20to%20insert%20incorrect%20code%20from%20another%20project%20I%20am%20busy%20with.%3CBR%20%2F%3EHere%20is%20the%20correct%20Sub.%20It%20gives%20the%20error%20on%20the%20last%20line.%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20GetTable()%3CBR%20%2F%3E%3CBR%20%2F%3EDim%20oLookInspector%20As%20Inspector%3CBR%20%2F%3EDim%20oLookMailitem%20As%20MailItem%3CBR%20%2F%3E%3CBR%20%2F%3E'Declare%20Word%20Variables.%3CBR%20%2F%3EDim%20oLookWordDoc%20As%20Word.Document%3CBR%20%2F%3EDim%20oLookWordTbl%20As%20Word.Table%3CBR%20%2F%3E%3CBR%20%2F%3E'Declare%20Excel%20Variables.%3CBR%20%2F%3EDim%20xlApp%20As%20Excel.Application%3CBR%20%2F%3EDim%20xlBook%20As%20Excel.Workbook%3CBR%20%2F%3EDim%20xlWrkSheet%20As%20Excel.Worksheet%3CBR%20%2F%3E%3CBR%20%2F%3E'Get%20mail%20item.%3CBR%20%2F%3ESet%20oLookMailitem%20%3D%20Application.ActiveExplorer.CurrentFolder.Items(%22Test%20Table%22)%3CBR%20%2F%3E%3CBR%20%2F%3E'Get%20Active%20Inspector.%3CBR%20%2F%3ESet%20oLookInspector%20%3D%20oLookMailitem.GetInspector%3CBR%20%2F%3E%3CBR%20%2F%3E'Get%20Word%20Editor%20object%3CBR%20%2F%3ESet%20oLookWordDoc%20%3D%20oLookInspector.WordEditor%3CBR%20%2F%3E%3CBR%20%2F%3E'Create%20new%20Excel%20App.%3CBR%20%2F%3ESet%20xlApp%20%3D%20New%20Excel.Application%3CBR%20%2F%3E%3CBR%20%2F%3E'Make%20visible.%3CBR%20%2F%3ExlApp.Visible%20%3D%20True%3CBR%20%2F%3E%3CBR%20%2F%3E'Add%20new%20workbook.%3CBR%20%2F%3ESet%20xlBook%20%3D%20xlApp.Workbooks.Add%3CBR%20%2F%3E'%3CBR%20%2F%3E''Add%20a%20new%20worksheet.%3CBR%20%2F%3E'Set%20xlWrkSheet%20%3D%20xlBook.Worksheets.Add%3CBR%20%2F%3ESet%20xlWrkSheet%20%3D%20xlBook.Sheets(1)%3CBR%20%2F%3E%3CBR%20%2F%3E'Grab%20the%20Word%20Table.%3CBR%20%2F%3ESet%20oLookWordTbl%20%3D%20oLookWordDoc.Tables(1)%3CBR%20%2F%3E%3CBR%20%2F%3E'Copy%20the%20table.%3CBR%20%2F%3EoLookWordTbl.Range.Copy%3CBR%20%2F%3E%3CBR%20%2F%3E'Paste%20it%20to%20the%20sheet.%3CBR%20%2F%3ExlWrkSheet.Paste%20Destination%3A%3DxlWrkSheet.Range(%22A1%22)%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

 

Please help - I have spend so much time on this and is unable to find a solution.

 

 

9 Replies
@Yea_So
Thank you for the suggestion - I will definitely investigate further.
I am grabbing a table from the body of an email though and then sending it to Excel for further manipulation - not sure if Power Query is the best tool for this... disclaimer - I haven't worked with Power Query much before...

@Gertdj 

 

I only suggested it because your code mentioned pdf, as pdf can only be an attachment unless it is an embedded object.

 

Sub PDF_To_Excel()

 

Dim setting_sh As Worksheet
Set setting_sh = ThisWorkbook.Sheets("Setting")

Dim pdf_path As String
Dim excel_path As String

pdf_path = setting_sh.Range("E11").Value
excel_path = setting_sh.Range("E12").Value


Dim fso As New FileSystemObject
Dim fo As Folder
Dim f As File

Set fo = fso.GetFolder(pdf_path)

Dim wa As Object
Dim doc As Object
Dim wr As Object

Set wa = CreateObject("word.application")

wa.Visible = True

Dim nwb As Workbook
Dim nsh As Worksheet

For Each f In fo.Files
Set doc = wa.documents.Open(f.Path, False, Format:="PDF Files")
Set wr = doc.Paragraphs(1).Range
wr.WholeStory

Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
wr.Copy

nsh.Paste
nwb.SaveAs (excel_path & "\" & Replace(f.Name, ".pdf", ".xlsx"))

'Clipboard 123
doc.Close False
nwb.Close False

Next

wa.Quit

MsgBox "Done"

 

End Sub

@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

@Gertdj 

 

its a good thing I pointed it out, now the real vba affecionados can go at it. vba is not my forte

@Yea_So
Thank you for your input! Much appreciated.
Anyone 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?
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?

@Gertdj 

 

I found this link, maybe it will be a stepping stone towards your solution go check it out:

import data from outlook email body into access (microsoft.com)

 

actual vba code:

https://stackoverflow.com/a/40958425/17186635