Table to Text Format & Send To Outlook

dudematters21

New Member
Joined
Oct 12, 2024
Messages
15
Office Version
  1. 365
Hello Masters,

Trying to convert column A1:A22 to plain text then paste it as an outgoing email in Outlook..
I tried this vba below, and Yes it converted everything to text
however it doesnt have proper spacing and paragraph anymore.. basically just the output is a straight text line.

VBA Code:
Sub Email_test()

    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Set rng = Nothing
    Set rng = Sheets("Shirts").Range("A1:A22").SpecialCells(xlCellTypeVisible)
    If rng Is Nothing Then
        MsgBox "The selection is not a range or the sheet is protected. " & _
               vbNewLine & "Please correct and try again.", vbOKOnly
        Exit Sub
    End If
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Dim v As Variant: v = rng.Value
    Dim tempStr As String: tempStr = ""
    For i = LBound(v, 1) To UBound(v, 1)
            For j = LBound(v, 2) To UBound(v, 2)
                If j = 2 Then
                    tempStr = tempStr & v(i, j) & vbCrLf
                Else
                    tempStr = tempStr & v(i, j) & " "
                End If
            Next j
        Next i
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "[EMAIL]User@company.com[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "Cells as text "
        .body = tempStr

   .Display

    End With

End Sub
 

Attachments

  • Capture.PNG
    Capture.PNG
    10.8 KB · Views: 4
Last edited by a moderator:
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time. 😊
 
Upvote 0
You have shown us the original data (XL2BB would be better for that as we could then easily copy it for testing) but not shown us what outcome you expect.

Therefore, this does not mean much ..
it doesnt have proper spacing and paragraph anymore.
For example, there is nothing that I would call a paragraph in the original data (the most any cell has in it is 3 words) so I can't see what paragraphs you would expect in the email.

If it may help you, in this section of code, j will never be 2 so the red line of code will never be executed.
Rich (BB code):
If j = 2 Then
    tempStr = tempStr & v(i, j) & vbCrLf
Else
    tempStr = tempStr & v(i, j) & " "
End If
So the string will just be every cell value joined together with a space between.


Also, do you (or might you ever) actually have any hidden rows? If you do, then I suspect the following lines will not do what I think you are expecting. Comes back to representative sample data and the expected results,

VBA Code:
Set rng = Sheets("Shirts").Range("A1:A22").SpecialCells(xlCellTypeVisible)


Dim v As Variant: v = rng.Value
 
Last edited:
Upvote 0

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
Back
Top