How can I send an email from excel VBA using Thunderbird?

Lots of sample code for various methods here:
maybe cd CDO will work
http://www.rondebruin.nl/sendmail.htm
cheers
cslarsen

Thank you, I tried them and I even wrote him asking for help.
His only reply was:  "Sorry, I never use this mail program"

Most of the examples on the net are from when you could just email through any SMTP server but today they all require login.
There are so many asking for the same on the net and they are all unanswered.
As I wrote, I have been reading page up and page down before posting.
However I am not an expert, just started on VBNA 4 weeks ago and made a huge league score system in excel with lots of cool stuff and all automated.
However my club uses Thunderbird and I cannot find a way to have it sent automatically to me through it.
I guess Microsoft wins on some sides ¿
But thank you anyway.

Ooops!

Sorry... replace line:

strParams = "mailto:fanpages@experts-exchange.com?subject=(Q_27496792) How can I send an email from excel VBA using Thunderbird?body=Does this work?"

With:

strParams = "mailto:fanpages@experts-exchange.com?subject=(Q_27496792) How can I send an email from excel VBA using Thunderbird?&body=Does this work?"

That is, an ampersand  between "?" and "body".

BFN,

fp.

THANK YOU! That was perfect!
But as you can imagine, now I am in trouble having to find out what is what to implement it with my code to attach the 2 sheets and in another macro the whole workbook....

But I will work on it.

Thank you again a 1000 times I am SO HAPPY now.
Tom

First time after searching for weeks I got some working help!
What can I say, EXCELLENT!

THANK YOU

Hi Tom,

If you would like me to add the necessary code to your workbook, please just post it in this thread & I'll update it & re-post (back to you).

Thanks for your closing comments (so far) in any respect.

BFN,

fp.

Well the truth is that I have no idea where to start in your code!
What I do is, on close workbook, copy 2 of the sheets to a temp file, attach them to the email and send it to me. (from my local tennis club, so I can update scores daily)
End of month, when they run end of month update where I have programmed excel to do lots of things to handle 10 singles and 10 double groups of 6 players in each group, to make a backup, then place players in the new groups depending of their scores, sort them out, reset old scores, print new sheets and so on.
It all comes down to one thing:

"Do something and mail it."

So I need to know where and how to implement the below code in your code.
If you really want to do this I will highly appreciate it as I am really new to VBA.

Thank you
Kind regards
Tom

Private Sub Workbook_BeforeClose(Cancel As Boolean)


    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim sh As Worksheet
    Dim TheActiveWindow As Window
    Dim TempWindow As Window

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    'Copy the sheets to a new workbook
    'We add a temporary Window to avoid the Copy problem
    'if there is a List or Table in one of the sheets and
    'if the sheets are grouped
    With Sourcewb
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
        .Sheets(Array("Tabla de puntuacion Individual", "Tabla de puntuacion Dobles")).Copy
    End With

    'Close temporary Window
    TempWindow.Close

    Set Destwb = ActiveWorkbook

    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2010
            'We exit the sub when your answer is NO in the security dialog that you only
            'see  when you copy a sheet from a xlsm file with macro's disabled.
            If Sourcewb.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
               ' MsgBox "Your answer is NO in the security dialog"
                Exit Sub
            Else
                FileExtStr = ".xlsm": FileFormatNum = 52
                    
            End If
        End If
    End With

  
    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

       With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        

        On Error GoTo 0
        .Close savechanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Select allOpen in new window

Sorry, I thought with your opening comment that you were skilled in Visual Basic for Applications.

I am not sure if it would be possible to add an attachment to an e-mail message to be sent by Thunderbird or, in reality, the "mailto:" syntax (that uses your local MS-Windows file associations to determine which application to use to create/send the message).

You can, however, call the Thunderbird(.exe) application using the MS-Windows "Command Line" syntax & include a reference to an existing file (as an attachment).

Reference: [ http://kb.mozillazine.org/Command_line_arguments_%28Thunderbird%29 ]

I will look at trying to automate the process (based on your code), but you may find creating a new question at Experts-Exchange may yield additional help from others that have experience of your requirements already.

BFN,

fp.

As a starting point, if I use the MS-DOS Prompt window I can create a sample e-mail & attached the file "c:\config.sys" as follows:

From the folder where I have installed Mozilla Thunderbird (C:\Program Files (x86)\Mozilla Thunderbird\)...

thunderbird -compose "to=fanpages@experts-exchange.com,subject=Test E-mail,body=Attached file,attachment=c:\config.sys"

Thank you.
I believe that I learned a lot in short time :-)
And I will keep learning.....
I do not give up, I am sure it can be done :-)

I will work on it over the weekend and if I do not hace success maybe ill post another question.
But you are actually the only one so far, that came up with a working solution.
I did find another that, kind of generated the e-mail but did not send it, deactivated the numlook and thats it.
Tom

Hi,

I have not tried this, but here goes...

Before the lines:
---
    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr
---

Add this:

Shell("C:\Program Files (x86)\Mozilla Thunderbird\thunderbird -compose " & Chr$(34) & "to=fanpages@experts-exchange.com,subject=Test E-mail,body=Attached file,attachment=" & TempFilePath & TempFileName & FileExtStr & Chr$(34))

Remember to change the path to your local installation of Mozilla Thunderbird (as well as the "to" recipient, "subject" & "body" text, of course).

(Next we can look at combining the Pause & SendKeys statements I posted above to automatically send the e-mail after it has been created).

However, a word of warning:

You may not wish to delete the file (the "Kill" command above) until the e-mail has actually been sent!

BFN,

fp.

I changed it from Before Close to a Sub and....

IT WORKS!
Now just if it can send it.
God you are good!

Sub sendmailTH()

'Working in 2000-2010
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
'    Dim OutApp As Object
'    Dim OutMail As Object
    Dim sh As Worksheet
    Dim TheActiveWindow As Window
    Dim TempWindow As Window

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    'Copy the sheets to a new workbook
    'We add a temporary Window to avoid the Copy problem
    'if there is a List or Table in one of the sheets and
    'if the sheets are grouped
    With Sourcewb
        Set TheActiveWindow = ActiveWindow
        Set TempWindow = .NewWindow
        .Sheets(Array("Tabla de puntuacion Individual", "Tabla de puntuacion Dobles")).Copy
    End With

    'Close temporary Window
    TempWindow.Close

    Set Destwb = ActiveWorkbook

    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2010
            'We exit the sub when your answer is NO in the security dialog that you only
            'see  when you copy a sheet from a xlsm file with macro's disabled.
            If Sourcewb.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
               ' MsgBox "Your answer is NO in the security dialog"
                Exit Sub
            Else
                FileExtStr = ".xlsm": FileFormatNum = 52
                    
            End If
        End If
    End With

    '    'Change all cells in the worksheets to values if you want
    '    For Each sh In Destwb.Worksheets
    '        sh.Select
    '        With sh.UsedRange
    '            .Cells.Copy
    '            .Cells.PasteSpecial xlPasteValues
    '            .Cells(1).Select
    '        End With
    '        Application.CutCopyMode = False
    '        Destwb.Worksheets(1).Select
    '    Next sh

    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

 '   Set OutApp = CreateObject("Outlook.Application")
 '   Set OutMail = OutApp.CreateItem(0)

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
 '       With OutMail
 '           .To = "me@me.com"
 '           .CC = ""
 '           .BCC = ""
 '           .Subject = "Liga Daily Update Fernando Gil"
 '           .Body = "Resultados de la liga Fernando Gil"
 '           .Attachments.Add Destwb.FullName
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
 '           .Send   'or use .Display
 '       End With
        On Error GoTo 0
 '       .Close savechanges:=False
    End With
 
 Shell ("C:\Program Files (x86)\Mozilla Thunderbird\thunderbird -compose " & Chr$(34) & "to=me@me.com,subject=Liga Update,body=Attached file,attachment=" & TempFilePath & TempFileName & FileExtStr & Chr$(34))


    'Delete the file you have send
  '  Kill TempFilePath & TempFileName & FileExtStr

'    Set OutMail = Nothing
'    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Select allOpen in new window

You were right, the kill command was not possible.

I found a page about "Shell And Wait" anything you know about?

Can it solve the problem?

"Shell & Wait" will pause the execution of the code until the call (Shell) to the Thunderbird (compose) line has finished.
This is in contrast to the code execution continuing whilst Thunderbird is still loading & the creation of the e-mail message is occurring.

Waiting until the Thunderbird application has loaded & the message is created will help but it I think the deletion of the file will still cause a problem if, for example, the file is significantly large enough that the message has not been sent (or is still being sent to your mail server) when the request to delete (kill) the file occurs.

Is it imperative that the file is removed after each execution?  Do you do this to be "tidy"?  Or do you do this for data security reasons?

I would suggest this:

Create the file in a dedicated "temporary" folder & at the *start* of your code, check this folder for any previously created "temporary" files.
If any are found, remove them.  Do not remove (kill) them at the end of your process; do this at the beginning of the next execution (when a new file is being created).

BFN,

fp.

Sounds like e great idea.

But when using Outlook and after executing the commands and sending the email, the two newly generated sheets are NOT in excel anymore but with thunderbird they are and you then have to close without saving them manually.

Tom

Now using Outlook so this is not relevant anymore.

Thank you so much for all the help.