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

mrindira

New Member
Joined
May 11, 2021
Messages
15
Office Version
  1. 2010
Platform
  1. Windows
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:
Rich (BB code):
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
 
Last edited by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
486
Office Version
  1. 2013
Platform
  1. Windows
Hi mrindira,

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

Insstead of
VBA Code:
 text = text & b & vbCrLf
try to use
VBA Code:
 text = text & b & "%0D%0A"
Ciao,
Holger
 

mrindira

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

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

Insstead of
VBA Code:
 text = text & b & vbCrLf
try to use
VBA Code:
 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

Active Member
Joined
Jan 24, 2003
Messages
486
Office Version
  1. 2013
Platform
  1. Windows
Hi mrindira,

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

VBA Code:
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
 
Solution

mrindira

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

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

VBA Code:
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...
 

Forum statistics

Threads
1,141,063
Messages
5,704,064
Members
421,326
Latest member
pfaustino

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