0

I want to send mail to each recipient (separate mail) in Col A, subject in Col B, and data from Col D to Col I in Excel as mail body.

I found the below code and the code is working fine for email address & subject.

In the body it is picking data from only one column (Col D). How to update the range D2:I2 in the body with formatting?

Option Explicit

Sub Sample()
    Dim OutApp As Object, OutMail As Object
    Dim ws As Worksheet
    Dim i As Long, lRow As Long

    Set OutApp = CreateObject("Outlook.Application")

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            Set OutMail = OutApp.CreateItem(0)

            With OutMail
                .To = ws.Range("B" & i).Value
                .Cc = ""
                .Subject = ws.Range("C" & i).Value
                .Body = ws.Range("D" & i).Value
                .Display

            End With
        Next i
    End With
End Sub

Excel Sample Data

Excel Sample Data

Community
  • 1
  • 1
Mallur
  • 211
  • 1
  • 4
  • 11
  • With formatting? What formatting do you want to preserve? The cell borders? Because that's the only formatting in range D2:I2, and if you need this, you will need to convert the range to HTML. There are functions available to do this, but seems overkill for a single, short line of data. – David Zemens Jul 09 '18 at 12:57

1 Answers1

2

Use RangeToString to get the string for the .Body from - How can I convert a range to a string (VBA)? 

Function RangeToString(ByVal myRange as Range) as String
    RangeToString = ""
    If Not myRange Is Nothing Then
        Dim myCell as Range
        For Each myCell in myRange
            RangeToString = RangeToString & "," & myCell.Value
        Next myCell
        'Remove extra comma
        RangeToString = Right(RangeToString, Len(RangeToString) - 1)
    End If
End Function

And use it like this:

.Body = RangeToString(ws.Range(ws.Cells(2,"D"),ws.Cells(i, "I")))

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 2
    Nice one Vityata!! – ASH Jul 09 '18 at 14:40
  • @Vityata, Thank you. I updated the code and tested as per suggestion and it is working fine, but I need the data with the borders not comma separated. What I need to change in the code to get with formatting. – Mallur Jul 10 '18 at 02:25
  • @Mallur - replace the comma in the `RangeToString` function (there is only one comma) to something else. E.g., an empty space would do. – Vityata Jul 10 '18 at 07:16