How can I fix my script so that it sends out the copy and pasted data from this worksheet to an email?

mrindira

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

I am trying to have my code copy ( TPerf.Range("A1:R100")) and paste excel information into an thunderbird email and send it out to myemail@yahoo.ca. But it isn't working, please help? Note I am trying to send it out by thunderbird app.

This is my script so far. I have only been successful in making it create the email. However the email does not get sent out successfully nor does it paste the data ( TPerf.Range("A1:R100")) into the body of the email. Can someone please tell me what I am doing wrong?


Sub CopyandEmail()

Application.DisplayAlerts = False

Dim MV As Workbook, Perf As Worksheet, TPerf As Worksheet, TMV As Workbook, rng As Range


Set MV = Workbooks.Open("Investments Market Value.xlsx")
Set TMV = Workbooks("Investments Market Value Email Macro.xlsm")
Set Perf = MV.Sheets("IntradayPerformance")
Set TPerf = TMV.Sheets("Intraday Performance")


#to clear TPerf worksheet and then copy and paste data from worksheet Perf to TPerf
TPerf.Range("A1:AH2000").ClearContents
Perf.Range("A1:AH2000").Copy

TPerf.Range("A1:AH2000").PasteSpecial Paste:=xlPasteValues

MV.Close SaveChanges:=True

TMV.Save
DoEvents

Dim thund As String, email As String, cc As String, subj As String, body As String

email = "myemail@yahoo.ca"
subj = "Investments Performance"
body = RangetoHTML(mail)

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:03"))
SendKeys "^{ENTER}", True

DoEvents


ActiveWorkbook.Save

End Sub

Function RangetoHTML(mail)

Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
Dim rng As Range
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 past the data in
TPerf.Range("A1:R100").Copy

With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , True, True
.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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Forum statistics

Threads
1,136,445
Messages
5,675,899
Members
419,591
Latest member
mersanko

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