How to transfer a specific column range (with value/text) using Excel VBA to body of Outlook in plain text.

I got the code for TO and Subject line, the problem is the body text.

Most of the solutions via internet is to transfer a table, pasted as image.

How to transfer a specific column range (with value/text) using Excel VBA to body of Outlook in plain text.

I got the code for TO and Subject line, the problem is the body text.

Most of the solutions via internet is to transfer a table, pasted as image.

• excel
• vba

Share Improve this question Follow edited Apr 8 at 13:59 CommunityBot 111 silver badge asked Jan 19 at 7:28 g00reg00re 2566 bronze badges 4

• Are you looking for a code to do that? Why not right click > Paste > Keep text only ? – Ali Sheikhpour Commented Jan 19 at 7:44
• i know what you mean.... yes im looking for a code.... we will use it for our work related. – g00re Commented Jan 19 at 8:03
• 1 @g00re This article will show you the method. jkp-ads/rdb/win/s1/outlook/bmail2.htm – Harun24hr Commented Jan 19 at 8:09
• 2 Try .Body = VBA.Join(Application.Transpose(rng), vbLf) where rng is a reference to the single-column range (e.g. Dim rng As Range: Set rng = Sheet1.Range("B6:B17")). – VBasic2008 Commented Jan 19 at 8:27

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

This is a code for adding into the email body the defined range of the ActiveSheet as text.

Sub tomail()
Dim srcrng As Range
Set srcrng = Range("A2:B10")   'the range to insert as text
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
RowDelim = Chr(10)  'to separate rows
ColDelim = ","      'to separate columns

For i = 1 To srcrng.Rows.count
    For j = 1 To srcrng.Columns.count
        If Len(stringto) = 0 Then
            stringto = srcrng(i, j)
        ElseIf j = 1 Then
            stringto = stringto & srcrng(i, j)
        Else
            stringto = stringto & ColDelim & srcrng(i, j)
        End If
    Next j
    stringto = stringto & RowDelim
Next i
olMail.BodyFormat = 1
olMail.body = olMail.body & stringto
olMail.display

olMail.Close olDiscard

End Sub