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

M

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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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
 
Upvote 0
Logit said:
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
Click to expand...
Hi,

I failed to set up outlook on my personal computer. I have updated my code please refer to my main post.
 
Upvote 0
mrindira said:
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
Click to expand...
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Hm
Dan_W said:
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.
Click to expand...
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.
 
Upvote 0
Dan_W said:
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.
Click to expand...
Hi logits response does not work unfortunately as it requires outlook.

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Email from Excel query
Replies
0
Views
160
gmazza76
G
C
  • Question
VBA - Email specific range and attach Sheet1
Replies
4
Views
484
Clermont
C
A
  • Question
Ron de Bruin VBA
Replies
2
Views
937
Alexfoots
A
E
  • Question
This code is to put a excel range onto a email
Replies
3
Views
407
I_know_nuffin
I
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
416
josros60
J

Forum statistics

Threads
1,214,641
Messages
6,120,684
Members
448,977
Latest member
dbonilla0331

Share this page

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