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

[mrindira]

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

 

[johnnyL]

Hey there @Dan_W !!!

@mrindira Can you get any part of the body of the email to send? For example:

email = "[EMAIL]12345@myemail.ca[/EMAIL]"
subj = "Investments Performance"
body = RangetoHTML("A1") & " " & RangetoHTML("A2") & " " & RangetoHTML("A3")

Also, Perhaps you should post an example range of A1:H40 so we can get a glimpse of what you are working with.

 

[Dan_W]

Thunderbird doesn't use VBA. Outlook does, which is why Logit suggested it.
From what I can see online, it isn't possible to do what you're trying to do with Thunderbird - that said, where did you come up with -compose formulation? Have you tried sending an email at the command prompt?

Hey @JohnnyL - sorry, I didn't see that you had also responded until after sending my comment.

 

[Dan_W]

logits response does not work

Though I've not tested, I would just comment that I'm sure Logit's Outlook code/suggestion does work, it's just that, as you note, you don't appear to have Outlook installed. Unless you can confirm that it is possible to send emails by Thunderbird through the command line, I think revisiting the installation of Outlook might be your best bet, no?

 

[johnnyL]

Also, at the top of your code you have ...

Set rng = Nothing
Set rng = Tperf.Range("A1:H40")

That first line will be negated by the second line.

 

[Dan_W]

Ok - I've partly worked it out. From what I can see, it isn't possible to send emails automatically through the commandline with Thunderbird - though it appears to be possible to compose emails via the commandline. You still need to click on the send button or, as you have done in your code, with sendkeys (press ctrl-enter). Understood.

We then come back to the problems in the code. In addition to the points made by @johnnyL, you need to correct the problems in the RangeToHTML function - in this respect, I can see that @Logit has very kindly rewritten it for you such that it should now work (save for the Outlook v Thunderbird issue). I suspect the answer is some amalgum of @Logit code and the points made by @johnnyL , but then we come back to the question of whether you can compose emails from the commandline containing HTML code. I've tried to do it on my computer, but I can't compose emails this way (it appears) - I type the instructions as set out in your code and nothing happens. Perhaps I need to change some security setting or something, but can you confirm this point?

 

[mrindira]

Hey there @Dan_W !!!

@mrindira Can you get any part of the body of the email to send? For example:

email = "[EMAIL]12345@myemail.ca[/EMAIL]"
subj = "Investments Performance"
body = RangetoHTML("A1") & " " & RangetoHTML("A2") & " " & RangetoHTML("A3")

Also, Perhaps you should post an example range of A1:H40 so we can get a glimpse of what you are working with.

Hi Johnny,

I get a type mistmatch error when I try to run your suggested code.
However I can add words (example: "These are the words I can add") as follows and successfully get it to show in this way:

thund = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe" & _
        " -compose " & """" & _
        "to='" & email & "'," & _
        "subject='" & subj & "'," & _
        "body=These are the words I can add" & body

and you wanted an example of the range A1:H40 please see attached image:

 

[mrindira]

Ok - I've partly worked it out. From what I can see, it isn't possible to send emails automatically through the commandline with Thunderbird - though it appears to be possible to compose emails via the commandline. You still need to click on the send button or, as you have done in your code, with sendkeys (press ctrl-enter). Understood.

We then come back to the problems in the code. In addition to the points made by @johnnyL, you need to correct the problems in the RangeToHTML function - in this respect, I can see that @Logit has very kindly rewritten it for you such that it should now work (save for the Outlook v Thunderbird issue). I suspect the answer is some amalgum of @Logit code and the points made by @johnnyL , but then we come back to the question of whether you can compose emails from the commandline containing HTML code. I've tried to do it on my computer, but I can't compose emails this way (it appears) - I type the instructions as set out in your code and nothing happens. Perhaps I need to change some security setting or something, but can you confirm this point?

Hi Dan,

If you look at look at @Logit code you will see the Function RangetoHTML(rng As Range) piece is the exact same. We got it from the same source. This function is meant to copy excel data into an email form.
However, the rest of the code is pertaining to Outlook. I am using thunderbird, so I have written a code that successfully automates the creation of the email and sends it but I cannot seem to successfully paste the copied excel data into its body before it gets sent.

I am not familiar with using the command line, forgive me, I am new to VBA. I simply type my code in ALT+F11 and the code I have written can successfully create and prepare and send off emails via thunderbird (however the excel data does not paste into it).

Thunderbird is meant to be a suitable substitute for outlook when using vba.

 

[Dan_W]

Hi
I had looked at the Function, and they're weren't the same - one of the reasons that your code wasn't working was because this function was not returning the converted HTML code. In order to return a value from a function, you need to assign some value to the what is the function name. In your code:

rngHTML = ts.readall
...
rngHTML = Replace(rngHTML, "align=center x:publishsource=", "align=left x:publishsource=")

This needs to be RangeToHTML = ... in order to work. The final working function ought to be a hybrid between your function and Logits.

I have written a code that successfully automates the creation of the email

This answers JohnnyL's and my question - so, this code aside, you're able to go to the command prompt and compose emails?

My quetions about the command line and HTML code do not relate to VBA. Those aren't programming questions - essentially, I'm asking whether or not it is even possible to compose emails from the command line with HTML code. This is relevant because it becomes a question of whether you will be sending a formatted, legible email (which is what you're trying to do) or you're sending a stream of HTML code. I tried with my Thunderbird, but I cannot get it to compose emails from the command line, so just wanted to make sure by checking with you that it is in fact possible.

When trying to work out why something has gone wrong with the code, it is best to 'step through' the code on a line-by-line basis. You can do this by pressing the F8 function key. I would also suggest testing whether or not the RangeToHTML function works - is it coming back as valid HTML Code? Does it look the way you want it to?

This way you can make sure that variable are not empty, for example, and you can pinpoint exactly where problem originates. Are you able to try rewriting the code above and test it, or would you like me to take a crack at it - I will have time either after work tonight or (more realistically) tomorrow morning.

 

[mrindira]

Hi
I had looked at the Function, and they're weren't the same - one of the reasons that your code wasn't working was because this function was not returning the converted HTML code. In order to return a value from a function, you need to assign some value to the what is the function name. In your code:

rngHTML = ts.readall
...
rngHTML = Replace(rngHTML, "align=center x:publishsource=", "align=left x:publishsource=")

This needs to be RangeToHTML = ... in order to work. The final working function ought to be a hybrid between your function and Logits.


This answers JohnnyL's and my question - so, this code aside, you you're able to go to the command prompt and compose emails?

My quetions about the command line and HTML code do not relate to VBA. Those aren't programming questions - essentially, I'm asking whether or not it is even possible to compose emails from the command line with HTML code. This is relevant because it becomes a question of whether you're send a formatted, legible email or you're sending a stream HTML code. I tried to recreate the process, but I cannot get Thunderbird to compose emails from the command line.

When trying to work out why something has gone wrong with the code, it is best to 'step through' the code on a line-by-line basis. You can do this by pressing the F8 function key. This way you can make sure that variable are not empty, for example, and you can pinpoint exactly where problem originates. Are you able to try rewriting the code above and test it, or would you like me to take a crack at it - I will have time either after work tonight or (more realistically) tomorrow morning.

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,

 

[Dan_W]

Thunderbird is meant to be a suitable substitute for outlook when using vba.

You've said this before. I explained that Outlook uses VBA, Thunderbird doesn't. Who says it's a 'suitable substitute' and why do they say that?