Send Email Error 438

[weirddemon]

I've inherited an Access application at work and I'm trying to change the email function from the old guy's email to a dummy one on the server.

I copied over the Access file and changed all references from his email to the dummy one. I tried sending an email, but received an error 438. Below is the code I'm using:

To be honest, I hate the fact that we're using VBA for a business application. It's a poor excuse for a real solution, but I think the old guy's programming knowledge was pretty awful.

Sub Test()
        blnSuccessful = FnSafeSendEmail("email", _
                                        "test", _
                                        strHTML, "...", _
                                        "...")
End Sub

From the above method, I've stripped out all the other code because it's irrelevant. The application is failing on that line. The code to the method it's calling is:

'This is the procedure that calls the exposed Outlook VBA function...
Public Function FnSafeSendEmail(strTo As String, _
                    strSubject As String, _
                    strMessageBody As String, _
                    Optional strAttachmentPaths As String, _
                    Optional strCC As String, _
                    Optional strBCC As String) As Boolean

    Dim objOutlook As Object ' Note: Must be late-binding.
    Dim objNameSpace As Object
    Dim objExplorer As Object
    Dim blnSuccessful As Boolean
    Dim blnNewInstance As Boolean
    
    'Is an instance of Outlook already open that we can bind to?
    On Error Resume Next
    Set objOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    
    If objOutlook Is Nothing Then
    
        'Outlook isn't already running - create a new instance...
        Set objOutlook = CreateObject("Outlook.Application")
        blnNewInstance = True
        'We need to instantiate the Visual Basic environment... (messy)
        Set objNameSpace = objOutlook.GetNamespace("MAPI")
        Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)
        objExplorer.CommandBars.FindControl(, 1695).Execute
                
        objExplorer.Close
                
        Set objNameSpace = Nothing
        Set objExplorer = Nothing
        
    End If

    blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _
                                                strSubject, strMessageBody, _
                                                strAttachmentPaths)
                                
    If blnNewInstance = True Then objOutlook.Quit
    Set objOutlook = Nothing
    
    FnSafeSendEmail = blnSuccessful
    
End Function

I've seen this piece of code all around the web, so I think it's fair to say he just copied and pasted it. In my searches, I found one guy who said he was having the same issue as me. He was able to turn off the macro security in Outlook and it worked.

I've done this in Outlook and Access, to no success. I don't actually think it's an error with the code since it works just fine on his old PC. I think it's an issue with the configuration of the computer, as in a security setting of some sort. I tried it on the server and my notebook, but neither worked,

Does anyone have any ideas on what's going on here?

 

[boblarson]

To be honest, I hate the fact that we're using VBA for a business application. It's a poor excuse for a real solution,

Well, I think that is a bit of an insult to those of us who use VBA daily for business applications. It is as valid as anything else you may have. It has its place and is one more tool in a toolbox.

Sub Test()
        blnSuccessful = FnSafeSendEmail("email", _
                                        "test", _
                                        strHTML, "...", _
                                        "...")
End Sub

Is that the EXACT code being used in that procedure call (including the "..." parts)? I ask because it is definitely incorrect as shown.

 

[weirddemon]

Well, I think that is a bit of an insult to those of us who use VBA daily for business applications. It is as valid as anything else you may have. It has its place and is one more tool in a toolbox.


Is that the EXACT code being used in that procedure call (including the "..." parts)? I ask because it is definitely incorrect as shown.

I certainly didn't mean to offend you. However, for the type of work that this application is used for, there are better solutions.

I'm used to creating full fledged applications and web applications, where I have fine tune control over everything. That and VBA is too unorganized. It's hard to read the code because of how everything is... kind of like spaghetti-ed together. The use of GoTo, On Error, etc (especially GoTo) is not anywhere near modern day programming and doesn't help alleviate that confusion.

But you're right, it's one more tool for people to use. I just don't see the purpose if you have the abilities to make real applications.

The guy who made this made a real application, but it was clear that his prior experience was only VBA. So now I have to go back and completely re-do the application.

Sorry for the ranting. I certainly didn't come here for that. I've just got a lot of extra work on my hands now that this has left. Which wouldn't be this way if all these things were programmed correctly. *sigh*

As for the issue at hand, the parameters shown here are just place holders, nothing more. I can show you the actually string, with the business information remove of course, later if you really want to see it.

However, I'm 99.99% sure that the code isn't the problem. At least not in the since that something drastic needs to be changed.

If I ran this Access sheet on his old PC, it works as intended. However, if I move it to another PC and change the references to his email address to the new one, it no longer works.

I did a find all throughout the code, so all instances of his email had to have been replaced. Which is why this makes me think that the issue is a security/configuration issue. Office was removed from his PC earlier today, so I can no longer look at the settings.

 

[boblarson]

Well as far as working - if this is exact:

blnSuccessful = FnSafeSendEmail("email", _
"test", _
strHTML, "...", _
"...")

Then it implements the function incorrectly anyway. Not sure why it would work on the other machine, because it shouldn't as written.

The function states clearly:

Public Function FnSafeSendEmail(strTo As String, _
                    strSubject As String, _
                    strMessageBody As String, _
                    Optional strAttachmentPaths As String, _
                    Optional strCC As String, _
                    Optional strBCC As String) As Boolean

Which means that strTo is set to "email" which is not a valid email address, nor is it a valid reference to a variable or a field. It is a literal string.

Second, the subject is fine "test" fits there.

Third, if strHTML is a valid string of text it would be fine in the position it is in.

Fourth, and this is where it goes further south. The next optional parameter is ATTACHEMENT PATH. And yet, it has "..." which is an invalid path. If there is no value to be passed you just bypass it. And in fact, there appears to be no further need of parameters, so the call should JUST be:

Sub Test()
 
blnSuccessful = FnSafeSendEmail(x@Somedomain.com, _
"test", _
strHTML)
End Sub

Now, should that not work, the other thing to mention is that unless there is a valid Outlook profile set up on the machine for the logged in user, then you would have a problem as well.

 

[weirddemon]

The following is exactly how it appears, minus the specific details.

FnSafeSendEmail("email@domain.com", _
                                        "Subject", _
                                        strHTML, "C:\excel.xls", _
                                        "email@domain.com;email@domain.com;")

Which all looks good.

As for the valid Outlook profile, to what are you referring specifically? Do you just mean a normal account? If so, then yes, that works just fine. I sent test emails to my PC from it and there's no problems.

I tried this setup on both the server and my notebook with no success.

 

[weirddemon]

So I was looking into this error a little and found the following from Microsoft:

Run Time Error 438 - Object Doesn't Support this Property or Method
The most common cause of error 438 is not maintaining binary compatibility between successive versions of your components. Each COM interface has an associated GUID that is called an interface ID (IID). Each coclass has an associated GUID that is called class ID (CLSID). When you compile an ActiveX component in Visual Basic, the CLSIDs and IIDs are compiled into the component's type library.

That sounds like it could be the issue. But, I don't know how I would go about checking the compatibility and/or updating the binaries. Any ideas?

 

[boblarson]

Never seen that problem with Outlook before when using Late Binding which you are. Check to see that there isn't a reference to Outlook set. Maybe the original guy set a reference to Outlook even though we have Late Binding and then when moving it to the Server, the wrong version of Outlook is being referenced.

Open TOOLS > REFERENCES and see if any are marked MISSING or that Outlook is checked. I would uncheck Outlook if it is.

 

[Wet_Blanket]

Never seen that problem with Outlook before when using Late Binding which you are. Check to see that there isn't a reference to Outlook set. Maybe the original guy set a reference to Outlook even though we have Late Binding and then when moving it to the Server, the wrong version of Outlook is being referenced.

Open TOOLS > REFERENCES and see if any are marked MISSING or that Outlook is checked. I would uncheck Outlook if it is.

Hey Bob,

I designed a database in Access 2003, but recently the company upgraded to Access 2007 AND Outlook 2010. I have one associate using the database get the above error when the database tries to access Outlook to send an email.

I can't find where to locate REFERENCES in the 2007 version. Can you offer me your sage wisdom?

 

[boblarson]

I can't find where to locate REFERENCES in the 2007 version. Can you offer me your sage wisdom?

Go to the Ribbon and on the DATABASE TOOLS tab, click the VISUAL BASIC button.
See the screenshot here
http://www.btabdevelopment.com/ts/default.aspx?PageId=91
and you'll see the Visual Basic button all the way to the left on the Ribbon.

 

[Wet_Blanket]

Great thanks.

I see that I have "Microsoft Outlook 14.0 Object Library" checked. Could this be the issue?