How do I get my script to successfully insert a line break at the end of each row in this specified range?

[mrindira]

Hi everyone,

So I am working on a massive project, but I can't seem to get my script to insert a line break in the specified range after every row. I can do it for every cell in the specified range but now at the end of every row. Please help.

My script is as follows:

Dim thund As String, email As String, cc As String, subj As String, Rng As String, maxrows As Long, rg As Range, maxcolumns As Long, row As Range

With TMV.Sheets("Intraday Performance")
  maxrows = Sheets("Intraday Performance").UsedRange.Rows.Count
  maxcolumns = Sheets("Intraday Performance").UsedRange.Columns.Count
 
  Set rg = Range(Cells(1, 1), Cells(maxrows, maxcolumns))
  text = ""
   For Each row In rg.Rows
        text = text & b & vbCrLf ' this is the part I can't seem to manipulate properly.
        Next
End With

email = "1234@yahoo.ca"
subj = "Investments Performance"

thund = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe" & _
        " -compose " & """" & _
        "to='" & email & "'," & _
        "subject='" & subj & "'," & _
        "body='" & text & "'"

Call Shell(thund, vbNormalFocus)
Application.Wait (Now + TimeValue("0:00:09"))
SendKeys "^{ENTER}", True

DoEvents
 
  ActiveWorkbook.Save

 

[HaHoBe]

Hi mrindira,

are you sure Thunderbird can work with a VBA Constant and know how to apply it?

Insstead of

 text = text & b & vbCrLf

try to use

 text = text & b & "%0D%0A"

Ciao,
Holger

 

[mrindira]

Hi mrindira,

are you sure Thunderbird can work with a VBA Constant and know how to apply it?

Insstead of

 text = text & b & vbCrLf

try to use

 text = text & b & "%0D%0A"

Ciao,
Holger

Yes I am sure it can. In fact, I have been able to get it to send an email but It was inserting the break after every cell.

Your suggestion actually didn't work when I made the change, I saw "%0D%0A" pasted all throughout the body of the email.

Thanks,

 

[HaHoBe]

Hi mrindira,

as I don´t know where you instantiate TMV I omitted the pointer for the workbook.

Sub MrE1171216_02()
'https://www.mrexcel.com/board/threads/how-do-i-get-my-script-to-successfully- _
'insert-a-line-break-at-the-end-of-each-row-in-this-specified-range.1171216/
  
  Dim strText As String, strTB As String, strEmail As String, strSubject As String
  Dim lngMaxRows As Long, lngMaxCols As Long
  Dim rngRow As Range, rngCell As Range
  
  For Each rngCell In Sheets("Intraday Performance").UsedRange
    Select Case rngCell.Column
      Case Sheets("Intraday Performance").UsedRange.Columns.Count
        strText = strText & rngCell.Value & vbCrLf
      Case Else
        strText = strText & rngCell.Value & " "
    End Select
  Next rngCell
  
  strEmail = "1234@yahoo.ca"
  strSubject = "Investments Performance"
  
  strTB = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe" & _
          " -compose " & """" & _
          "to='" & strEmail & "'," & _
          "subject='" & strSubject & "'," & _
          "body='" & strText & "'"
  
  Call Shell(strTB, vbNormalFocus)
  Application.Wait (Now + TimeValue("0:00:09"))
  SendKeys "^{ENTER}", True
  
  DoEvents
  
  ActiveWorkbook.Save

End Sub

On my system and with my data the contents of the cells in each row were concatenated with a blank between having a line break at the end when Thunderbird displayed them.

Ciao,
Holger

 

[mrindira]

Hi mrindira,

as I don´t know where you instantiate TMV I omitted the pointer for the workbook.

Sub MrE1171216_02()
'https://www.mrexcel.com/board/threads/how-do-i-get-my-script-to-successfully- _
'insert-a-line-break-at-the-end-of-each-row-in-this-specified-range.1171216/
 
  Dim strText As String, strTB As String, strEmail As String, strSubject As String
  Dim lngMaxRows As Long, lngMaxCols As Long
  Dim rngRow As Range, rngCell As Range
 
  For Each rngCell In Sheets("Intraday Performance").UsedRange
    Select Case rngCell.Column
      Case Sheets("Intraday Performance").UsedRange.Columns.Count
        strText = strText & rngCell.Value & vbCrLf
      Case Else
        strText = strText & rngCell.Value & " "
    End Select
  Next rngCell
 
  strEmail = "1234@yahoo.ca"
  strSubject = "Investments Performance"
 
  strTB = "C:\Program Files\Mozilla Thunderbird\thunderbird.exe" & _
          " -compose " & """" & _
          "to='" & strEmail & "'," & _
          "subject='" & strSubject & "'," & _
          "body='" & strText & "'"
 
  Call Shell(strTB, vbNormalFocus)
  Application.Wait (Now + TimeValue("0:00:09"))
  SendKeys "^{ENTER}", True
 
  DoEvents
 
  ActiveWorkbook.Save

End Sub

On my system and with my data the contents of the cells in each row were concatenated with a blank between having a line break at the end when Thunderbird displayed them.

Ciao,
Holger

Thank you so much that worked.

Now I have to figure out a way to make the final data aligned in columns. Might need to paste to textpad first...