Macros not appearing

[nuckfuts]

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!

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

 

[Joe4]

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]

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]

Hi all - thanks for the replies - I had put it in a separate module already. The problem was I modified to sub name to

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]

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

 

[nuckfuts]

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]

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:

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.

 

[nuckfuts]

It is because you original procedure declaration had an input parameter/variable in it:

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]

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]

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?