How can I change my code so that it doesn't send out blank emails?

mrindira

New Member
Joined
May 11, 2021
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
Hi,

So I am sending out emails using thunderbird.exe and I am trying to get the the email to copy and paste TPerf.Range("A1:H40") into the body of the email. So far, I have been successful in only sending out a blank email! Please help, my script is as follows:

Rich (BB code):
Sub CreateEmail()

Dim thund As String, email As String, cc As String, subj As String, body As Variant, TPerf As Worksheet, TMV As Workbook

Set TMV = Workbooks("Investments Market Value Email Macro.xlsm")
Set TPerf = TMV.Sheets("Intraday Performance")

TPerf.Range("A1:H40").Copy

# to create and  send out the email
email = "1234@myemail.ca"
subj = "Investments Performance"
body = TPerf.Range("A1:H40")

thund = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe" & _
        " -compose " & """" & _
        "to='" & email & "'," & _
        "subject='" & subj & "'," & _
        "body='" & body


Call Shell(thund, vbNormalFocus)
Application.Wait (Now + TimeValue("0:00:10"))
SendKeys "^{ENTER}", True

DoEvents

  
  ActiveWorkbook.Save
 
End Sub
 
Function RangetoHTML(rngHTML)

#I created this function because I thought it would help me copy and paste the cells into the body of the email, but I couldn't get it to work.

    Dim fso As Object,  ts As Object, TempFile As String, TempWB As Workbook, Rng As Range, TPerf As Worksheet, TMV As Workbook
  
    Set TMV = Workbooks("Investments Market Value Email Macro.xlsm")
    Set TPerf = TMV.Sheets("Intraday Performance")
    Set TempWB = Workbooks.Add(1)
  
 
    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    '' copy the range and create a new workbook to paste the data into
    TPerf.Range("A1:H40").Copy
  
    With TempWB.Sheets(1)

        .Cells(1).PasteSpecial xlPasteFormats, , True, True
        .Cells(1).Select
        Application.CutCopyMode = False
    End With

    '' publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    '' read all data from the htm file into rngHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream
    rngHTML = ts.readall
    ts.Close
    rngHTML = Replace(rngHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    TempWB.Close savechanges:=False
    '' delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
 
Last edited by a moderator:
Hi,

Thanks for your response and thorough look through.

I will try do everything you said and respond here right away, please respond at your convenience.

Thanks,
Great, thanks, and you're very welcome. I'll be interested to see if we can get it to work. I can take a crack at the function if you like, but it would be very helpful if you can try and test each part of the code to make sure that, once we stitch it all together, it will work properly.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Great, thanks, and you're very welcome. I'll be interested to see if we can get it to work. I can take a crack at the function if you like, but it would be very helpful if you can try and test each part of the code to make sure that, once we stitch it all together, it will work properly.

Hi Dan_W

So I changed the code from rngHTML to RangetoHTML
and unfortunately, it produces the same amount. I.e. It creates an email and sends it off without any information in the body.
So I'm still failing to successfully copy and paste the excel data into the body of the email.

i ran it line by line and everything actually ran successfully without any errors. However, it just didn't paste anything. Strange...

You wanted me to change:

Rich (BB code):
rngHTML = ts.readall
ts.Close
rngHTML = Replace(rngHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")


to


Rich (BB code):
rangetoHTML = ts.readall
ts.Close
rangetoHTML = Replace(rngHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

right?
 
Last edited by a moderator:
Upvote 0
HI - there are a few more elements from Logit's code that need to be worked into the final version. Give me 30 mins and I will sort it out.
 
Upvote 0

mrindira :​

You need to use the menu selection / > >_ when posting code.
This places your posted code as seen in Post #2. NOTE: This is a FORUM REQUIREMENT and not optional.


From the lengthy discussion I feel there has been sufficient evidence that "Thunderbird" does not function with the Outlook macro code.
I believe at this point your efforts are for naught.

I have not tested these resources here. They are provided for your review in hopes you will find an answer ...
It is recognized that some or all of these resources may not be helpful at all. One thing that seems to be
common with most online discussions I've reviewed is the need to use CDO for sending emails via HTML.
That is a whole other ball game for you to explore.

Best wishes on your project.

VBA Email using Mozilla Thunderbird [SOLVED]

sending thunderbird mail using access vba | Thunderbird Support Forum | Mozilla Support

VBA push button script for attaching PDF and email using Mozilla Thunderbird - OzGrid Free Excel/VBA Help Forum

Send formatted html email in Thunderbird. | Thunderbird Support Forum | Mozilla Support

This resource discusses HTML Body using CDO to send via Thunderbird: VBA - CDO Mail | DEVelopers HUT
 
Upvote 0
Looking at it properly, @Logit's rewrite of the function is exactly what is needed. It needs to be tested properly though, so I propose that you use Logit's version of the function with the following test code - everything you need is below. Instead of deleting the HTML file that the function produces, I instead adjusted the code to print out into the Immediate Window the filename of the HTML file. The code will then pause. Make sure you make a note of this filename. You can continue by pressing F5 and it wil then print out the HTML code that the function produced. You put the filename into the browser to see how the HTML code renders. Assuming all this is fine, then the problem will come down to whether or not you can compose emails in Thunderbird using HTML code - that part is all up to you.


VBA Code:
Sub TestCode()
Dim HTMLCODE as String, TPerf As Worksheet, TMV As Workbook

Set TMV = Workbooks("Investments Market Value Email Macro.xlsm")
Set TPerf = TMV.Sheets("Intraday Performance")

HTMLCODE = RangetoHTML(TPerf.Range("A1:H40"))
Stop
Debug.Print HTMLCODE

End Sub


Function RangetoHTML(rng As Range)
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.ADD(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
    'Publish the sheet to a htm file
    With TempWB.PublishObjects.ADD( _
         SourceType:=xlSourceRange, _
         FileName:=TempFile, _
         sheet:=TempWB.Sheets(1).Name, _
         source:=TempWB.Sheets(1).UsedRange.address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
    'Close TempWB
    TempWB.Close savechanges:=False
    'Delete the htm file we used in this function
    Debug.Print TempFile
    'Kill TempFile
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
 
Upvote 0
@Logit - I just saw your post now - many thanks.
@mrindira - We can't test the code on your computer for you, so it's up to you now, I think. For what it's worth, I think @Logit's point re: CDO is a very good one. Something to consider if you are still having problems with the code.
 
Upvote 0
@Logit - I just saw your post now - many thanks.
@mrindira - We can't test the code on your computer for you, so it's up to you now, I think. For what it's worth, I think @Logit's point re: CDO is a very good one. Something to consider if you are still having problems with the code.
Hi Dan,

Thanks for your help. I have been able to completely reconstruct the working code as follows. So It copy paste the data into Thunderbird.exe and sends out the data.


VBA Code:
Sub CopyandEmail()

Application.DisplayAlerts = False

Dim MV As Workbook, Perf As Worksheet, Tperf As Worksheet, TMV As Workbook


Set MV = Workbooks.Open("C:\Users\l__ha\Desktop\Python\Investments Market Value\Investments Market Value.xlsx")
Set TMV = Workbooks("Investments Market Value Email Macro.xlsm")
Set Perf = MV.Sheets("IntradayPerformance")
Set Tperf = TMV.Sheets("Intraday Performance")

Tperf.Range("A1:H40").ClearContents
Perf.Range("A1:H40").Copy

Tperf.Range("A1:H40").PasteSpecial Paste:=xlPasteValues
Tperf.Range("A1:H40").Copy

MV.Close savechanges:=True

TMV.Save
DoEvents
 
 

Dim thund As String, email As String, cc As String, subj As String, Rng As String, maxrows As Long, rg As Range, text As String, row As Range

For Each rngCell In Sheets("Intraday Performance").UsedRange
    Select Case rngCell.Column
      Case Sheets("Intraday Performance").UsedRange.Columns.Count
        text = text & rngCell.Value & vbCrLf
      Case Else
        text = text & rngCell.Value & " "
    End Select
  Next rngCell


email = "12233@email.ca"
subj = "Investments Performance"


thund = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe" & _
        " -compose " & """" & _
        "to='" & email & "'," & _
        "subject='" & subj & "'," & _
        "body='" & text & "'"


Call Shell(thund, vbNormalFocus)
Application.Wait (Now + TimeValue("0:00:09"))
SendKeys "^{ENTER}", True

DoEvents

    
  ActiveWorkbook.Save
 
End Sub
 
Upvote 0
Solution
@mrindira: I edited your posts in this thread to wrap your codes in the code tags. I can see that you also used VBA code tags in your final post, so I assume you now see how the code is more readable that way.

Note: I used RICH code tags to edit your posts because you had bold code lines in your sample codes that I believe you'd like to indicate those lines. Therefore, those code snippets are not VBA highlighted. If you need more information about code tags, please see the following instructions.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top