arrays - Opening a new excel worksheet from outlook -

arrays - Opening a new excel worksheet from outlook -

i importing info outlook , works code opening excel opens instance personal.xlsb not loaded, , open multiple instances of excel. if run twice open 2 instances overwrite info in first instance, leaving sec instance blank workbook. if excel closed , outlook not, code run give error since won't set info new "second" instance, though 1 instance running.

can suggest amendment prevent this?

sub extract() on error goto 0 set myolapp = outlook.application set mynamespace = myolapp.getnamespace("mapi") dim thermomail outlook.mailitem set thermomail = application.activeinspector.currentitem set xlobj = createobject("excel.application") xlobj.visible = true xlobj.workbooks.add 'set headings dim msgtext, delimtedmessage, delim1 string delimtedmessage = thermomail.body 'remove before "lead source:" , after "elms" trimmedarray = split(delimtedmessage, "source:") delimtedmessage = trimmedarray(1) trimmedarray = split(delimtedmessage, "elms") delimtedmessage = trimmedarray(0) 'split array @ each homecoming messagearray = split(delimtedmessage, vbcrlf) 'this next line gives error if excel closed , macro rerun. range("a1:a" & ubound(messagearray) + 1) = worksheetfunction.transpose(messagearray) phone call splitatcolons end sub

right now, creating new instance of excel line:

set xlobj = createobject("excel.application")

excel different (most) office applications, because can run multiple instances (powerpoint, outlook, word cannot this...)

so want first check if there open instance of excel, , utilize that. create new instance if there no instance open.

on error resume next set xlobj = getobject(, "excel.application") on error goto 0 if xlobj nil set xlobj = createobject("excel.application")

arrays excel vba outlook