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

[mrindira]

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 xublishsource=", _
"align=left xublishsource=")

#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