Macro to copy email to excel - Runtime Error 91 Object Variable Not Set

[Rick Bennett]

This macro is exactly what I am need, but it produces a run-time error '91': Object variable or With block variable not set

The error occurs on line sText = olItem.body

I am new to VBA for Outlook and do not know how to correct the error. Thanks for any help provided.

 

[Diane Poremsky]

Are you using this one? Use a macro to copy data in Outlook email to Excel workbook

This is line in the Dim section:
Dim sText As String

 

[Rick Bennett]

Thank you for your quick reply. Yes, I am using "Use a macro to copy data in Outlook email to Excel workbook"
and the Dim section has: Dim sText As String.

 

[Diane Poremsky]

remove the spaces from in front of that line & see if that works (I know Edge doesn't always copy spaces in code correctly, not sure about other browsers.)

 

[Rick Bennett]

No change with spaces removed.

 

[Diane Poremsky]

You're using it with a rule? If you made the changes to run it manually, make sure olItem is used to identify the mail object - Set olItem = Application.ActiveExplorer().Selection(1) - this needs to be before the line that errors.

 

[Rick Bennett]

I had already replaced Sub CopyToExcel(olItem As Outlook.MailItem)
with Sub CopyToExcel() and Dim olItem As Outlook.MailItem

I had not added this line as instructed.
Set olItem = Application.ActiveExplorer().Selection(1)

The macro knows runs without error, but it just open excel with no file selected. I will work on getting the path correct and let you know my progress. Thanks for your help so far.

 

[Rick Bennett]

I am not running a script; just running the macro manually from the project explorer The macro completes but nothing happens. How does the CopyToExcel macro know the Outlook inbox to use?

 

[Diane Poremsky]

Add
debug.print strpath
right after this line - then show the Immediate window. you'll see the path outlook is trying to use.
strPath = enviro & "\Documents\test.xlsx"

adding ' in from of on error resume next might help too

 

[Rick Bennett]

Here is the folder in outlook that I want to copy to email. How does the macro know to go to this folder? Sorry to keep asking questions.

 

[Diane Poremsky]

The short version is that this line:
Set olItem = Application.ActiveExplorer().Selection(1)
should work on any folder.
Select a message, run the macro on the selected message.

if you want it to work on all messages in a folder, you need to work it in with one of the messages at Working with All Items in a Folder or Selected Items

If you are using a macro that uses a specific folder and don't want to select it first, you'd use a method on this page:
Working with VBA and non-default Outlook Folders

 

[Diane Poremsky]

if you want to run it on all messages in a folder, I added a new macro to the page - Use a Macro to Copy Data in an Email to Excel