1

I would like Excel to write in the active folder of Outlook using VBA. But I am struggling to even define the path to the currently active folder.

Here is the code I'm trying to use:

 Sub Test_folder_path()
     Dim myFolder As Object
     Set myFolder = Application.ActiveExplorer.CurrentFolder
 End Sub

The error that I get is Error 438: ("Object Doesn’t Support this Property or Method")

If I try to define other types for myfolder, Set myfolder asks for an object.

I would be really happy about any advice, since I'm looking for a solution to this since hours!

I'm working with Office 2021.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
Roy R
  • 31
  • 3

1 Answers1

1

To work with MS Outlook from VBA-Excel you need to either late bind or early bind with it.

In your example, Application will refer to MS Excel and not to MS Outlook.

Here is an example

Option Explicit

Sub Test_folder_path()
    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")

    Dim myFolder As Object
    Set myFolder = OutApp.ActiveExplorer.CurrentFolder
    
    MsgBox myFolder.Name
End Sub

I recommend reading Using early binding and late binding in Automation

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks a lot! That worked like a charm! Now everybody using the Macro just has to pay attention that the right folder is active in Outlook. ;-) Is there also an option to address folders in public (company internally) section of Outlook by name/path? (In my case I have an "Info" folder with a subfolder "tasks" in it, which I would like to address.) – Roy R Oct 12 '22 at 11:23
  • Why do you not let the use pick the folder? – Siddharth Rout Oct 12 '22 at 12:21
  • Well I can of course! However if some users make the mistake of activating the macro when the wrong folder is selected, then the macro writes in the wrong folder. So I just thought it would be more fool-proof, if the macro already knows where to write without any user input. Because it will always be the same folder anyhow... More a nice to have then a must though. – Roy R Oct 12 '22 at 12:54
  • You can always post validate the name of the folder and the parent folder (`Info | Tasks`) and ask them to select again. I am not sure about `public (company internally)` folders. I do not have an option to test it and hence I am not suggesting anything. – Siddharth Rout Oct 12 '22 at 14:56
  • To designate the Outlook folder from Excel. https://stackoverflow.com/questions/11151811/reference-a-folder-by-name – niton Oct 12 '22 at 16:39