creating mail in outlook using VBA

[keeper85]

I want to create transfer some information from excel to a mail in outlook. I already have the following code:

Sub MailSend()
ActiveWorkbook.Save
Dim olApp As Object, olMail As Object
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
olMail.to = "CAF Amsterdam CCRM; CAM Amsterdam"
olMail.Subject = "Holdings Swapclear " & Date - 1
olMail.sentonbehalfofname = "CAF Amsterdam CCRM"
olMail.body = "Dear CAM," & vbCr & vbCr & "Below you will find the Holdings concerning LCH Swapclear. " & vbCr & vbCr & Range("a18:b22") _
& vbCr & vbCr & "Kind regards," & vbCr & vbCr & "CAF Amsterdam"
 
olMail.Display
 
End Sub

</PRE>
I get an error using the code. Can someone help me fix this? I can copy using single cells using this method, however, it does not seem to work with bigger ranges. Also I want to keep the formatting intact. The code does not do that. I would appreciate if someone could help me fix this problem.

​

 

[Ralajer]

The problem was that the body has to be a string. A single cell might be interpreted based on its value but multiple cells won't, or at least that how I understand it.

The below converts the range into a list using a for loop and adds it the string

Sub MailSend()
    ActiveWorkbook.Save
    Dim olApp As Object, olMail As Object
    Dim msgBody As String
    Dim temp As String
    Dim cell As range
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0)
    
    temp = ""
    For Each cell In range("A18:B22")
        temp = temp & vbCr & vbTab & "•" & vbTab & cell.Value
    Next cell

    msgBody = "Dear CAM," & vbCr & vbCr & "Below you will find the Holdings concerning LCH Swapclear. " & vbCr
    msgBody = msgBody & temp
    msgBody = msgBody & vbCr & vbCr & "Kind regards," & vbCr & vbCr & "CAF Amsterdam"
    With olMail
        .To = "CAF Amsterdam CCRM; CAM Amsterdam"
        .Subject = "Holdings Swapclear " & Date - 1
        .sentonbehalfofname = "CAF Amsterdam CCRM"
        .body = msgBody
        .Display
    End With  '// olMail
End Sub