Macros not appearing

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Hi All -

I'm trying to implement the below macro to open up outlook and reply to specific emails but Excel isn't able to locate/recognize the macro. I've tried to save it in the relevant sheet and in a module but still doesn't show up for either. When I press F5 inside the macro it pulls up the screen prompting me to type the macro in (because it can't find it). I have the Outlook library enabled as well. Thanks!

VBA Code:
Sub Test()

Dim olApp As Outlook.Application
Dim olNs As Namespace
Dim Fldr As MAPIFolder
Dim olMail As Variant
Dim i As Integer

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
i = 1

For Each olMail In Fldr.Items
If InStr(olMail.Subject, "Application for Privilege Leave - Leave ID - Dev-PL-45252-4") <> 0 Then
olMail.Display

i = i + 1
End If
Next olMail
End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
1. Put the code in a "Standard" Module (i.e. in the VB Editor Explorer, right-click on your workbook, select Insert --> Module and save it there.
2. Then from the worksheet, go to the Macros menu and select "View Macros". Make sure that the "Macros in:" setting is set to This Workbook.

You should see the "Test" macro listed there.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It needs to be in a normal module, not a worksheet/ThisWorkbook module, but you should be able to run it from the VBE with f5 if the cursor is actually somewhere in that routine.
 

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Hi all - thanks for the replies - I had put it in a separate module already. The problem was I modified to sub name to
VBA Code:
Sub Test (i As Long)
- if I change it back to Sub test() and Dim i as Long later down it appears as an option and I'm able to run the code
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

It's hard for us to answer when you post code that is different to what you're actually using... ;)
 

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
It's hard for us to answer when you post code that is different to what you're actually using... ;)
Yeah of course the one part I forgot was what was actually causing the error.. lol sorry folks! If anyone can tell my why it wouldn't show up I'll mark that as the solution :)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yeah of course the one part I forgot was what was actually causing the error.. lol sorry folks! If anyone can tell my why it wouldn't show up I'll mark that as the solution
It is because you original procedure declaration had an input parameter/variable in it:
Rich (BB code):
Sub Test (i As Long)

You cannot pass parameters to VBA procedures that you call from the the Macros menu. So those sort of procedures are hidden from that menu.
Those VBA procedures (ones with parameters) can only be called by other procedures, where you are passing a parameter.
 
Solution

nuckfuts

New Member
Joined
Mar 10, 2020
Messages
43
Office Version
  1. 365
Platform
  1. Windows
It is because you original procedure declaration had an input parameter/variable in it:
Rich (BB code):
Sub Test (i As Long)

You cannot pass parameters to VBA procedures that you call from the the Macros menu. So those sort of procedures are hidden from that menu.
Those VBA procedures (ones with parameters) can only be called by other procedures, where you are passing a parameter.
That makes sense now, thank you!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You cannot pass parameters to VBA procedures that you call from the the Macros menu

You can, subject to certain restrictions, but you have to type it all in to the dialog. ;)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
That makes sense now, thank you!
You are welcome!

You can, subject to certain restrictions, but you have to type it all in to the dialog.
Rory, I am curious. Can you give an example?
And then how would you coerce it to show up in the "View Macros" menu?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,201
Messages
5,640,818
Members
417,168
Latest member
StumpoC

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