Really Stupid Question

[AndyAdmin]

OK two hours of zero progress and I'm now ready to kill.
All I'm attempting to do is create a single command macro to run a piece of VB code.
Creating a new macro, selecting runcode, entering the module name with () at the end and saving.
The module is visible in Access and has a unique name (newsemailcode).
The module runs as it should in VB editor, but as soon as I run the macro in Access I get "the expression you entered has a function name that microsoft office access can't find".
The DB is just new, created today, the name is unique and not the same as a VB command etc. the db is in a trusted zone and I'm the network admin.
According to every help I've read this is the easiest thing in the world and I'm about to lose my mind trying to do this.
Any help very greatfully recieved. thanks

 

[JamesMcS]

I think if you're running it from a macro is has to be a function, is it or is it a private sub?

 

[AndyAdmin]

the code in question is

Option Explicit

Sub SendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
'Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
' objOutlookRecip.Type = olTo

' Add the BCC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Test User")
objOutlookRecip.Type = olBCC

' Set the Subject, Body, and Importance of the message.
.Subject = "Test"
.Body = "Last test - I promise." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
' .Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub


Got it from the MS tech site and am attempting to use for the purpose they've said it can be used for.
thanks

 

[JamesMcS]

try changing sub... to Private Function (Optional Attachmentpath as string) - I'm not 100% on the bit in brackets, but you can't run a sub from a macro, only a function - as far as I know

 

[JamesMcS]

oh, and end function instead of end sub at the bottom.

 

[AndyAdmin]

Same result, still ran fine from within the VB editor, but the same error message when I try to run the macro.
thanks for the suggestion though.

 

[JamesMcS]

Hmmm..... stumped! Er, is the code in a class module? I would try putting it in a regular one (if it's already in there try creating a new one with a different name to the function) - I'm pretty sure Access doesn't look in class modules when looking for a function that runcode calls.

That's about it from me I'm afraid...

 

[AndyAdmin]

tried the recreating it bit, no joy, the module itself is sitting at the same level as my forms in the project window of VB editor.
Basically I pasted it in from the MS website, made a couple of code chages, commented a few things out and made sure all the references were ticked.
Damn..Microsoft DAO 3.6 object library wasn't selected but if I try and select it I get a name conflict message. Today isn't going well, not sure if this is linked to my main problem.

 

[Mr. B]

Create a Module and move the Function code to it. Then change the Private to Public for the function declaration.

 

[JamesMcS]

Well, good luck! Time to kill I think.....

 

[AndyAdmin]

Mr B.
the top of the code now reads;

Option Explicit

Public Function SendMessage(Optional AttachmentPath)

is that what you meant?
Tried running the macro with the new module name, same result.

 

[SOS]

Also, make sure you have NOT named the module the same name as the function.

 

[Mr. B]

Should be:

Option Compare Database
Option Explicit

Public Function SendMessage(Optional AttachmentPath)
'Code Here ...
End function

 

[SOS]

the module itself is sitting at the same level as my forms in the project window of VB editor.

That would indicate it is on a form module and NOT a standard module where it needs to be.

 

[AndyAdmin]

Mr B. - changed the code as per your suggestion - again code ran in VB editor, but got can't find module in Access.

SOS - I probably didn't explain it very well, the module, in the VB editor, is connected directly to the project name, it isn't a stub from a form.

 

[AndyAdmin]

Fixed it - please feel free to slap me on the head. I was using the module name, not the function name contained within the module.
Suppose the clue was in the error message "can't find the FUNCTION name".
Thanks for all your suggestions.
In my defence I did say it was really stupid!

 

[JamesMcS]

Excellent, glad it's worked - I'll remember to keep an eye out for that next time I'm writing a function....!

 

[Brianwarnock]

Fixed it - please feel free to slap me on the head. I was using the module name, not the function name contained within the module.
Suppose the clue was in the error message "can't find the FUNCTION name".
Thanks for all your suggestions.
In my defence I did say it was really stupid!

I hate to beat a man when he is down but both James and SOS did mention that.
I find that careful reading is the most useful skill to learn.

Brian

 

[Mr. B]

How many times has that come back to bite me. LOL

Glad you got thing working for you. I asure you that this is one you will not soon forget.

 

[AndyAdmin]

Thanks guys and glad to know it isn't just me that can trip up on these things!