access inactive excel worksheet in outlook

[Art Eby]

hello,

I am trying to run an outlook vba script when i receive a new email. It will take certain values in one excel worksheet and copy them to another worksheet in the same workbook.
to do this i activate "Sheet 1", but when i try to copy certain Sheet 1 values to Sheet 2, i get (runtime error '1004': application-defined or object-defined error). I think the problem is that Sheet 2 isn't activated. Is there any way to insert values in Sheet 2 without deactivating Sheet 1?Any help would be appreciated! Thanks in advance.

 

[Worf]

Hello

- Example code below; you should use the new mail event. I am home and do not have Outlook configured here, so I cannot actually fire the event.
- Try copying the data manually to see if errors arise.

' Outlook module
Sub Art()
Dim ExApp As Excel.Application, exwbk As Workbook, ws1 As Worksheet, ws2 As Worksheet
Set ExApp = New Excel.Application
Set exwbk = ExApp.Workbooks.Open("C:\pub\ERP2.xlsm")            ' your path here
ExApp.Visible = True
Set ws1 = exwbk.Sheets("sheet1")
Set ws2 = exwbk.Sheets("output")
ws2.[d4] = ws1.[a10]                                            ' copy value
ws1.[a15:a20].Copy ws2.[b15:b20]                                ' copy range
End Sub

 

[Art Eby]

Thanks for your help. Its working now.