access inactive excel worksheet in outlook

Art Eby

New Member
Joined
Nov 17, 2016
Messages
3
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,094
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.

Code:
' 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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top