CODEWITHSUNDEEP
excelexcel-2010outlook-2010vba
Mallur
Mallur

Reputation: 211

Copy multiple columns, keeping formatting, from Excel to Outlook mail

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

Upvotes: 0

Views: 242

Answers (1)

Vityata
Vityata

Reputation: 43575

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")))

Upvotes: 2

Related Questions