Object Required Outlook VBA Copy Paste to Excel -

i trying copy body of email , paste new excel workbook. below code generating "object required" error on "set wb" line. new outlook vba, , having hard time trying find information on error anywhere. majority of code copied somewhere else, it's been while , forget where. appreciated.

sub pastetoexcel() dim activemailmessage mailitem dim xlapp excel.application dim wb excel.workbook dim ws excel.worksheet   if typename(activeexplorer.selection.item(1)) = "mailitem"      'get handle on email     set activemailmessage = activeexplorer.selection.item(1)      'copy formatted text:     activemailmessage.getinspector().wordeditor.range.formattedtext.copy      'ensure excel application open     set xlapp = createobject("excel.application")      'make excel application visible     xlapp.visible = true      'name excel file     set wb = xlobject.object.workbooks("test.xlsx")      'paste email     set ws = xlobject.object.sheets(1)     ws.range("a1").paste      end if end sub 

xlobject isn't declared or assigned anywhere. specify option explicit @ top of module - vbe complaining undeclared identifier.

because xlobject isn't declared, it's implicitly variant, , because it's never assigned, type variant/empty. , because empty isn't object type, using set keyword assign object reference causes "object required" error, because set can used assign object reference.

this code reproduces issue - try in immediate pane:

set foo = xyz.abc 

in other words, xlobject doesn't exist, vba doens't know it. you've been told in comments, need use declared , assigned xlapp reference instead.

set wb = xlapp.workbooks("test.xlsx") 

this gives me out of range error

of course does. there's no workbook named test.xlsx in instance of excel.

the solution open it:

set wb = xlapp.workbooks.open("test.xlsx") 

now, should expecting error if file doesn't exist: handle it.

test.xlsx not exist. need macro create new workbook , name that.

the add method create brand new workbook:

set wb = xlapp.workbooks.add 

you can name workbook when save it, before close it:

wb.saveas "test.xlsx" wb.close false 

the close method has parameters save changes, in 1 line:

wb.close true, "test.xlsx"