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:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,064
I don't know anything about Thunderbird.exe . However, you are running Windows so there is not reason you could not use Outlook
as your email client.

Here is a macro/s that will send any range in the email body :

VBA Code:
Option Explicit

Sub CopyRows()
Dim i As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
    ws1.Range("B2:C12").Copy
    Mail_Selection_Range_Outlook_Body
End Sub

Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim lEndRow
Dim Value As String
Set rng = Nothing
' Only send the visible cells in the selection.
Set rng = Sheets("Sheet1").Range("B2:C12")
If rng Is Nothing Then
    MsgBox "An unknown error has occurred. "
    Exit Sub
End If
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
    .to = "Your email address here in quotes"
    .CC = ""
    .BCC = ""
    .Subject = "Your Subject Here"

    .HTMLBody = "<p>Text above Excel cells" & "<br><br>" & _
                RangetoHTML(rng) & "<br><br>" & _
                "Text below Excel cells.</p>"
    
    ' In place of the following statement, you can use ".Display" to
    ' display the e-mail message.
    '.Send
    .Display
End With
On Error GoTo 0
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
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
    Kill TempFile
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
 

mrindira

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

ADVERTISEMENT

I don't know anything about Thunderbird.exe . However, you are running Windows so there is not reason you could not use Outlook
as your email client.

Here is a macro/s that will send any range in the email body :

VBA Code:
Option Explicit

Sub CopyRows()
Dim i As Integer
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Sheet1")
    ws1.Range("B2:C12").Copy
    Mail_Selection_Range_Outlook_Body
End Sub

Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim lEndRow
Dim Value As String
Set rng = Nothing
' Only send the visible cells in the selection.
Set rng = Sheets("Sheet1").Range("B2:C12")
If rng Is Nothing Then
    MsgBox "An unknown error has occurred. "
    Exit Sub
End If
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
    .to = "Your email address here in quotes"
    .CC = ""
    .BCC = ""
    .Subject = "Your Subject Here"

    .HTMLBody = "<p>Text above Excel cells" & "<br><br>" & _
                RangetoHTML(rng) & "<br><br>" & _
                "Text below Excel cells.</p>"
   
    ' In place of the following statement, you can use ".Display" to
    ' display the e-mail message.
    '.Send
    .Display
End With
On Error GoTo 0
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
Set OutMail = Nothing
Set OutApp = Nothing
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
    Kill TempFile
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
Hi,

I failed to set up outlook on my personal computer. I have updated my code please refer to my main post.
 

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:

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
Hi So I have updated my code as follows, but still it sends out blank emails.

Rich (BB code):
Dim thund As String, email As String, cc As String, subj As String, body As String, 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 rng = Nothing
 Set rng = Tperf.Range("A1:H40")

email = "12345@myemail.ca"
subj = "Investments Performance"
body = RangetoHTML(rng)

#creating the email
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:05"))
SendKeys "^{ENTER}", True

DoEvents

   
  ActiveWorkbook.Save
 
End Sub


# this function was created to copy the excel range and paste the range from excel to the body of the email.
 
Function RangetoHTML(rng As Range)

    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
    Dim 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
    rng.Copy
   
       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 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:

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
483
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I note that Logit has responded since I first started to look at it, so I'll hold off on replying - please let me know if I can help at all.
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
483
Office Version
  1. 365
Platform
  1. Windows
Sorry - just checked up on one question I was going to ask - is it even possible to use Thunderbird to send emails via the command line? From what I can see, it isn't, but maybe there have been some changes to the software.
 

mrindira

New Member
Joined
May 11, 2021
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
Hm
Sorry - just checked up on one question I was going to ask - is it even possible to use Thunderbird to send emails via the command line? From what I can see, it isn't, but maybe there have been some changes to the software.
Um I do not know the answer to that. But I know that thunderbird is supposed to be a suitable substitute for outlook, when trying to automate emails using vba.
 

mrindira

New Member
Joined
May 11, 2021
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
I note that Logit has responded since I first started to look at it, so I'll hold off on replying - please let me know if I can help at all.
Hi logits response does not work unfortunately as it requires outlook.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,132,895
Messages
5,655,830
Members
418,242
Latest member
k3itall

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
Top