Outlook VBA - Range setting

Question

Hello Community,

I have an Outlook macro, which creates a new email when a button is clicked. The macro works well if I specify the subject as only one cell. Here it is :

Private Sub CommandButton1_Click()

    Dim a As Integer

    Dim objOutlook As Object

    Dim objMail As Object

    Dim rngTo As Range

    Dim rngSubject As Range

    Dim rngBody As Range

    Dim rngAttach As Range

    Set objOutlook = CreateObject("Outlook.Application")

    Set objMail = objOutlook.CreateItem(0)

    a = ActiveCell.Row

    With ActiveSheet

        'Set rngTo = .Cells(a, "C")

        Set rngSubject = .Cells(1, "A")

Set rngBody = .Range("C17:M24")

        'Set rngAttach = .Range("B4")

    End With

    With objMail

        '.To = rngTo.Value

        .Subject = rngSubject.Value

.Body = rngBody.Value

       '.Attachments.Add rngAttach.Value

        .Display 'Instead of .Display, you can use .Send to send the email _

                    or .Save to save a copy in the drafts folder

    End With

    Set objOutlook = Nothing

    Set objMail = Nothing

    Set rngTo = Nothing

    Set rngSubject = Nothing

    Set rngBody = Nothing

    Set rngAttach = Nothing

End Sub

The problem happens when I set the body range to multiple cells. You see, I have text in C17 and a table in C18:M22. I want to include both the text and the table in the email that pops up. 

Any help or up is truly greatly appreciated!

Cheers,

Daniel O.

Answer 1

Thank you very much, it did work at taking multiple cells. However, it takes tables as if they were text instead of keeping them in the table format.

Your code will not work at all because you need to generate HTML instead, which is much more complicated:

https://msdn.microsoft.com/de-de/library/office/aa171418%28v=office.11%29.aspx

https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

Andreas.

Answer 2

Thank you! I found what had to be done with your references.

Cheers

Answer 3

Hello Andreas,

Thank you very much, it did work at taking multiple cells. However, it takes tables as if they were text instead of keeping them in the table format.

In my case, I have an introduction text in C17, A table from C19 to M22, and a conclusion text in C24.

When I select the range in our macro as C17:M24, it does effectively copy all the text in those cells. However, it takes my table as a text format without the borders and cell order.

Daniel Ortega

Answer 4

Add the function below and change the line

.Body = rngBody.Value

to

.Body = MultiLine( rngBody)

Andreas.

Function MultiLine(ByVal From As Range) As String

  Dim R As Range

  If From.Count = 1 Then

    MultiLine = From.Value

  Else

    For Each R In From

      MultiLine = MultiLine & R.Value & vbCrLf

    Next

    MultiLine = Left(MultiLine, Len(MultiLine) - 2)

  End If

End Function