• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

Outlook version
Outlook 2016 32 bit
Email Account
IMAP
#1
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.
 
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
#3
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.
 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#4
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.)
 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#6
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.
 
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
#7
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.
 
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
#8
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?
 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#9
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
 
Outlook version
Outlook 2016 32 bit
Email Account
IMAP
#10
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.

upload_2016-9-26_16-2-47.png

 
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
#11
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