Sending email with attachment using Sendobject & Thunderbird client

[ACB64]

Hi. I'm running win 10 (updated to most recent).
I've recently rebuilt my computer and have reinstalled Access 2007 and a split database. In the front-end there is a script to email a message to multiple recipients. I select the recipients from a list and click a "send" button, intending to send it as an email in Thunderbird. I'm using the standard Sendobject code.
It used to work and now doesn't. The same database is used on another computer (both front-end and back-end are on a cloud drive) without problems. I get the message:
"Runtime error 2046:
The command or action SendObject isn't available now."
I have spent days looking for a solution.
Can anyone help?
Thanks in advance.


Full code of subroutine follows. Note that in the following there is a function called ConcatRelated. It works to create a string of email recipients. It definitely works:

Private Sub Command76_Click()
'Check for empty Subject line
If IsNull(Text89) Then
    MsgBox "Please enter some text for the SUBJECT.", vbOKOnly, "Error"
    Text89.SetFocus
    Exit Sub
End If
'Check for empty text
If IsNull(Text73) Then
    MsgBox "Please enter some text for the MESSAGE.", vbOKOnly, "Error"
    Text73.SetFocus
    Exit Sub
End If
 
'Determine the recipients to be sent an email
Dim varItem As Variant
Dim recipients As String
'Determine if at least one course has been selected
If List77.ItemsSelected.Count = 0 Then
    MsgBox "Please select at least one course", vbOKOnly, "Error"
    Exit Sub
End If
'Collect email addresses of the recipients
recipients = ""
 
Lc = List77.ListCount
 
'If "ALL" is selected, then select all courses
If List77.Selected(1) Then
    Dim i As Integer
    For i = 0 To List77.ListCount - 1
    List77.Selected(i) = True
    Next i
End If
 
        'loop through all selected courses
            Dim z As Long
            With List77
 
                For z = 2 To Lc
                  If List77.Selected(z) Then
                    If Check81 Then recipients = ConcatRelated("Invoicemail", "BulkEmail", "[coursekey]= """ & .ItemData(z) & """", , ";") & recipients 'Email invoicees
                    If Check79 Then recipients = ConcatRelated("StudentEmail", "BulkEmail", "[coursekey]= """ & .ItemData(z) & """", , ";") & recipients  'Email Students
                 End If
                Next z
            End With
 
    'remove duplicates
    recipients = EliminateDupesInString(recipients, ";")
    ' Calculate number of email addresses
    If recipients <> "" Then x = Len(recipients) - Len(Replace(recipients, ";", ""))
    MsgBox "There are " & Str(x) & " addresses."
 
    If recipients <> "" Then DoCmd.SendObject acSendNoObject, , acFormatRTF, "[e-mail]", , recipients, Text89, Text73, True
    DoCmd.Close
End Sub

[NeoPa]

Have you tried to Dubug (Debugging in VBA) this code and seen what each variable holds at the time you get the error (Line #54) in your code?

[ACB64]

Hi NeoPa,
Thanks for answering. Yes, I have and everything checks out. The error appears only when the SendObject is attempted. All variables are valid. Also, it works on another machine, and also worked seamlessly prior to the rebuild.
I remember having this issue about 1 year ago and I found a solution, but have no idea what it was. How silly that I didn’t record the solution.
ACB

[GazMathias]

Hi ACB64,

Doesn't SendObject negotiate with the default mail client? That would suggest that the problem seems to be outside of your Access solution. I do not use Thunderbird, but is there a setting for enabling this kind of automation?

Gaz

[ACB64]

Hi Gaz,
Yes, that’s my understanding, and exactly the reason for my problem. Previously, I had no problems with SendObject negotiating with Thunderbird. Now, however, after the rebuild, something is presumably different and I don’t know what it is.
Andrew.

[ACB64]

Hi Mycinegisters,
Any hints??? I checked out your blogs and didn’t find anything that looked related to my issue.
Andrew.

[GazMathias]

Hi ACB64,

In Windows itself, when you right click a file and select Send To... Mail recipient does Thunderbird respond properly?

Is Thunderbird defined as the default mail handler in PC Settings -> Default apps?

Is the build of Thunderbird the same on your machine and the working machine, and are you able to directly compare settings?

Gaz

[GazMathias]

Hi ACB64,

In addition to my prior questions I also meant to ask if both Office and Thunderbird are targetting the same architecture (x86 / x64)?

Gaz

[ACB64]

Hi Gaz,
Thanks for responding so quickly.
Yes, TB is the default email client in Windows.
Yes, the machine computer aus running TB 32 Bit and Access 2007 only had the 32 Bit architecture. I tried running the 64 Bit version of TB, but got the same problem. Then I went back to the 32 Bit Version of TB. The two machines are similar except the „working“machine is running office 2013 and the corresponding version of Outlook. I even tried installing the Access 2013 runtime and ran the DB with that. No help. The problem was still there. I uninstalled the runtime. Apart from that, I can no differences. However, as stated previously, the “non working” machine used to work and the build is, as far as I can tell, the same a as before (Win 10, access 2007, TB, etc.)
Andrew.

[GazMathias]

Hi Andrew,

Did the right click file -> Send To -> Mail recipient test work?

I assume the machine has been restarted since the reinstallation of Thunderbird? Shutdown / cold boot does not count (Win 10 fast boot shenanigans).

Gaz

[ACB64]

Hi Gaz,
Sorry, I completely missed that one. I just tried right-click/send to (mail) and … it didnt work. I checked to see what the default email client is, and it lists TB. Strange!!
And, yes, the machine has been restarted many times. I rebuilt it about 3 weeks ago and have been trying to solve this ever since🤔.
What do you think?
Andrew.

[ACB64]

@ACB64
When I try to right click and send as email, the message I get is “these files can’t be emailed because there is no email program on this computer install an email program and try again”. But, as I said, Thunderbird is installed and TB is listed as the default email client.

[GazMathias]

Hi Andrew,

I suspected as much.

Thunderbird FAQs say to reinstall the client and it should put all the MAPI settings back but you say you've allready done that.

One very simple thing you can try is to change the Default app Email setting back to Windows Mail, reboot and then change it back to Thunderbird. It might convince Windows to recreate the correct MAPI settings.

If that does not work then the settings that tell Windows how this should work are located inside HKLM -> SOFTWARE -> Mail but correcting those entries is beyond the scope of what we can do here and you must realise that incorrect registry modifications can lead to serious corruption and we can take no responsibility for any damage done to your Windows installation.

The best course of action I would then suggest is to contact Thunderbird support as it is very much a Thunderbird issue.

If you are no stranger to the registry and you have a good and valid backup of your old Windows partition that you can browse (or better, virtualise and boot) then I would suggest that you extract its registry hive and inspect your old settings to compare them, but be aware of any potential valid differences, such as different versions of referenced items / paths.

I feel that the solution is in reach but as mentioned, it has gone beyond the scope of what we can do for you now.

Gaz

P.S. Sorry for the delay, I have slept since we last corresponded / other IRL distractions.

[ACB64]

Hi Gaz,
Thanks. We all have to sleep! After my last response, I followed directions to modify the registry and succeeded in getting the right click action to work. However, still no joy in finding a solution to the original problem.
While dwelling on the issue whilst trying to sleep, I remembered that when I had this problem years ago, I added some object library to access (via the VBA-tools menu) and I think that was the solution. I have no idea which library/ libraries I added though. Have you any ideas? I spent some time this morning searching, but found nothing helpful.
Cheers.

[GazMathias]

Hi Andrew,

I don't know what to tell you.

A quick n dirty SendObject test worked for me using the 365 version of Outlook O2K16 on Windows 10 21H1 and I had to set no additional references to do so.

Admittedly, I do not use SendObject (or MAPI in general) as our projects usually dictate Outlook automation or SMTP connections to Microsoft Exchange servers over SSL in .NET land.

If I were confronted with this problem at this stage I would first see if the problem can be replicated on another machine and use forensic techniques to discover what file / reg queries the call to SendObject generates versus a working instance.

I still think something is awry with the registry and that Thunderbird support may be your best course of action, as it may well be a known issue to them.

There are other MAPI-based mail sending options but they would obviously involve refactoring your solution.

Gaz

[ACB64]

Hi Gaz,
Yes, I’m with you- not sure where to go. But I will contact the tech help for thunderbird and see what, if anything, they may be able to help me with.
A very big thank you to you!
Regards,
Andrew.

[NeoPa]

At this stage I'd consider complete removal of Thunderbird first, followed by a valid Restart of the system and then installing Thunderbird again from scratch.

After the first reboot following the removal of Thunderbird you may want to check the Registry for any references to it as you really want as clean a registry as possible.

As always with the Registry, be aware that missteps are very serious and can permanently damage your system. Also bear in mind that technical support providers may decide that any such meddling can invalidate their responsibility to help. IE. Only meddle in the Registry if you decide you want to. Generally speaking it shouldn't be necessary. I do myself, from time to time, but then I'm a natural meddler ;-)

[ACB64]

Hi NeoPa,
Great minds think alike😊. I did that this morning, removing TB and every mention of TB from the registry that I could find. I then reinstalled TB 32 Bit.
Same problem 😔. Needless to say, I’m feeling a little hopeless.
Any other ideas?
Andrew.

[isladogs]

If you want to try something different, consider sending email direct from Access using CDO (Collaborative Data Objects)
For more info and an example app, see my CDO EMail Tester
Using that approach, no external email application is required. In fact, none need to be installed!

[ACB64]

Hi Isladogs,
If I can’t find a solution, I’ll try this work around. There are advantages to using the Send object method such as saving a copy of all sent emails, business letterhead capability etc.
Thanks for the idea.

[isladogs]

I've used CDO as my main email approach for over 15 years. It works seamlessly. I certainly wouldn't think of it as a 'work-round'.
I first used it when I couldn't get Novell Groupwise to work with Access.

CDO can do anything that the other approaches can do and , in many ways, is a lot simpler.
You can use any valid email address, use encryption, add attachments, plain test or HTML etc.
As for the two specific points you mentioned
1. If you send an HTML message, you can include a business related image as a header, footer or both...or as an attachment. See the example HTML message supplied with the app. Of course, many of the email programs used by your recipients will routinely block inline images whichever method you use to send emails
2. You can set it up to send a copy of the email to yourself.

The only thing CDO cannot do is receive email direct in Access. You need automation for that

[ACB64]

Dear Isladogs,
Thanks for this. I’ll certainly look at it when I return in a couple of days. Oh, and I’ll have a detailed look at the link that you sent. I really appreciate the help and different approach.

[GazMathias]

We used CDO a while back but TLS over 587 became a deal breaker. We ended up COM wrapping System.Net.Mail.MailMessage instead since we used it in .Net anyway.

Now everything seems to be going the o365 route we either have to talk to Outlook or send out though the o365 Exchange direct on 587 smtp.office365.com, but the Mail Flow settings have to have the IP definded as a send connector.

Email continuity is a big factor and seems to go hand in hand with GDPR shenanigans, and potential client tender questionnaires.

You'd be surprised at how many times the need to have users see the message in their Outlook drafts and send it themselves is a requirement for new projects, too.

To Andrew - could you temporarily install another mail client and test? I'm going to install Thunderbird on one of my dev VMs to see if I can replicate the problem - I'm itching to run a trace. EDIT - no luck, it worked.

I should've asked earlier, have you done the usual sfc:scannow and DISM.exe /Online /Cleanup-image /Restorehealth (CMD as admin).

Gaz

[isladogs]

Don't see why TLS was a 'deal-breaker' for you.

There needn't an issue using CDO with TLS over port 587.
I handle that very easily with the following code in my example app:

 
    'code for STARTTLS
    If txtPort = 587 Then
        .Item("http://schemas.microsoft.com/cdo/configuration/sendtls").Value = True
    End If

It can also handle the additional security required to send messages using a GMail account. See the help file supplied with the app.

[GazMathias]

From memory the issue was with TLS 1.2 via On-Premesis Exchange 2010. We no longer maintain any systems that need to send email via COM objects now.

[isladogs]

All I can tell you is that CDO does work with TLS with that code line added.
As stated I've used it for many years with no issues. Its used with all my commercial apps and several thousand users across a number of clients.

At one time, it appeared GMail was going to be a problem, but that was also solved relatively easily

[ACB64]

Wow, Gaz! A lot of that went straight over my head🤔.
Anyway, actually, no, I haven’t run those commands, but I will when I return tomorrow (?).
I’ll let you know. Thanks.
Andrew.

[GazMathias]

Hi,

I ran a trace on my VM anyway to work out what's going in the call stack. Here's the interesting parts:

MAPI32.DLL (C:\WINDOWS\SYSTEM32\MAPI32.DLL) v1.0.2536.0 @ MAPISendMail
MAPI32.DLL (C:\WINDOWS\SYSTEM32\MAPI32.DLL) v1.0.2536.0 @ FGetComponentPath
RegQueryValue HKCU\SOFTWARE\Clients\Mail\(Default) -> REG_SZ Mozilla Thunderbird

bizarrely:

RegQueryValue HKLM\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY \MACHINE\Software\Clients\Mail(Default) -> REG_SZ Microsoft Outlook
RegQueryValue HKCU\SOFTWARE\Clients\Mail\(Default) -> REG_SZ 40 Mozilla Thunderbird

Interesting class resolution parts:

RegQueryValue HKCR\CLSID\{29F458BE-8866-11D5-A3DD-00B0D0F3BAA7}\(Default) -> REG_SZ "C:\Program Files\Mozilla Thunderbird\thunderbird.exe" /MAPIStartup
RegQueryValue HKCR\CLSID\{29F458BE-8866-11D5-A3DD-00B0D0F3BAA7}\ProdID -> REG_SZ MozillaMapi.1

Theres a lot of cross referencing to copies of the same Keys in HKLM\SOFTWARE\Microsoft\Office\ClickToRun\Registry \, which makes me think that the version of MAPI32.dll that I have was installed with Office.

Instantiation:

MAPI32.DLL C:\WINDOWS\SYSTEM32\MAPI32.DLL v1.0.2536.0 @ fn MAPILogoff
Process Start C:\Program Files\Mozilla Thunderbird\thunderbird.exe" /MAPIStartup -Embedding
Load Image C:\Program Files\Mozilla Thunderbird\thunderbird.exe
mozMapi32_InUse.dll C:\Program Files\Mozilla Thunderbird\mozMapi32_InUse.dll v0.8 @ fn MAPISendMail
mozMapi32_InUse.dll C:\Program Files\Mozilla Thunderbird\mozMapi32_InUse.dll v0.8 @ fn Ordinal0

Andrew - I appreciate that this may not be something you can replicate to compare with but maybe you know someone personally familiar with these techniques?

Process Monitor is free from the sysinternals website along with a lot of other very useful tools and can accomplish the same thing.

Isla - I do not doubt it. If I ever need to use CDO again I will bear it in mind. All I can say is that it did not work at the time for the very specific server it was supposed to (that replaced a Windows Server 2003 machine) so the decision was made to not spend more time trying to get it to work and to write a DLL seemed to be the simplest work around. That solution was retired in 2016 and so with it went our need for CDO entirely.

I'm most definitely not bashing CDO or its use within Access.

Gaz

[ACB64]

Gaz,
True, it’s beyond me. But, if I’m hearing you correctly, it might be because you have Outlook installed?? Do you think that I should reinstahl the entire Office 2007 program, including Outlook? I don’t want to use it, though.
Andrew.

[GazMathias]

Hi Andrew,

No. My setup is different to yours - pretty sure 2007 didn't have ClickToRun.

What I showed here is how calling the black box that is SendObject gets to Thunderbird in my config, but there should be common steps in yours.

If I had to resolve such a problem personally I could verify that the resolution is happening by comparing a known bad stack trace either to a known good one on another computer or on another user profile on the same computer.

MAPI32.dll lives in C:\Windows\System32 so the system file integrity checks I wrote about earlier should examine that dll.

Run command prompt as administrator (important). - Click Start, Type CMD, right click the Command Prompt entry and click Run as Administrator.

Type sfc:scannow and hit enter.

Allow it to run and correct anything it finds.

Regardless of what it does, type DISM.exe /Online /Cleanup-image /Restorehealth and hit enter.

I feel it would be best to uninstall Thunderbird and Office then restart and do the above. Then install Office, restart, install Thunderbird and restart.

That said, I have seen instances of reinstalling Office not correcting bad registry entries though but it is worth a try.

Did you pose the question to Thunderbird support - they may have tools at their disposal that will help them.

Gaz

[ACB64]

Hi Gaz,
I tried to run sfc:scannow and it didm't run. I got this messdage: "The filename, directory name, or volume label syntax is incorrect."
Am trying the uninstall / reinstall / DISM procedure shortly. Again, I'll let you know.
I have written to TB support and have some ideas from the community (is this what they offer as technical help?), none of which have proven helpful (yet).
Andrew.

[GazMathias]

Hi Andrew,

Try 'sfc /scannow' as admin (again hit Start, type CMD, right click "Command Prompt" and click Run as Administrator).

Gaz

[ACB64]

Hi Gaz,
thanks for not giving up on me. I tried the run as admin thing yesterday: Still no luck.

BUT the good news is that I got Access working. I removed Office 2007 sand Thunderbird before removing (almost) all references to both programmes in the registry. Then I rebooted, installed the complete Office 2007 package, rebooted, installed TB, rebooted again and tried the database. It worked!!!🥳.
I’ll keep trying to do the scan, because that should work, but my immediate problem is fixed - thank you SO much. The scan issue is a lower priority, but I will continue to work on it.
Andrew.

[GazMathias]

Hi Andrew,

Great!

You can probably forget about the system file check now, since its solved.

Gaz