Format E-mail Body

Hi Rui,

To solve your problem, you have to understand the difference between an Outlook Email .Body and .HTMLBody.

When sending an Email using Outlook, the Email Body can be either .Body or .HTMLBody:
a. Body uses ASCII code for a NEW LINE which can be one of:
(1) Carriage Return LineFeed - the combination of Characters Chr(13) and Chr(10) which can also be coded as vbCrLf.
(2) LineFeed Only - Chr(10) which can also be coded as vbLf.
(3) Carriage Return Only - Chr(13) which can also be coded as vbCr.
NOTE: The above numbers are decimal numbers (e.g. the ASCII code for Linefeed is Decimal 10).

b. HTMLBody uses HTML code to indicate a NEW LINE. The HTML Code for NEW LINE is <BR> .

c. To switch between .Body and .HTMLBody, the appropriate NEW LINE code must be used.

For example if reading code from a Cell and to convert to HTML:

'Get the Email Body from the cell
  sBody = ActiveSheet.Range("B12").Value
  
  'Replace ASCII NEW LINE with HTML NEW LINE
  sBody = Replace(sBody, vbCrLf, "<BR>")
  sBody = Replace(sBody, vbLf, "<BR>")
  sBody = Replace(sBody, vbCr, "<BR>")

To convert from HTML code to Body code:

 'Get the Email Body from the cell
  sBody = ActiveSheet.Range("B14").Value
  
  'Replace HTML NEW LINE with  NEW LINE
  sBody = Replace(sBody, "<BR>", vbCr)

See the attached file which demonstrates the above concepts and contains the following code in ordinary code module ModOutlookBodyVsHTMLBody:

Option Explicit

Sub OutlookEmailB12AsBodyAsIs()
  'This assumes that Outlook is already open to simplify the code
  
  Dim OutApp As Object
  Dim OutMail As Object
  
  Dim sBody As String
  
  'Get the Email Body from the cell
  sBody = ActiveSheet.Range("B12").Value
  
  
 'Attempt to create an Outlook object
  On Error Resume Next
  Set OutApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Err.Clear
    MsgBox "NOTHING DONE.  The Outlook Object could not be created from Excel." & vbCrLf & _
           "Try again when Outlook is open."
    Exit Sub
  End If
  On Error GoTo 0
  
  
  'Create the Outlook Mail Object (using the default Email account)
  Set OutMail = OutApp.CreateItem(0)
 
  'Determine the values to be sent
  With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Cell B12 as Body (as is)"
    .Body = sBody
     
    .Display
    
    '.Send - comment out the 'Display line' if you want to send
  End With
  
  'Clear the Object Pointers
  Set OutMail = Nothing
  Set OutApp = Nothing

End Sub


Sub OutlookEmailB12AsHTMLBodyAsIs()
  'This assumes that Outlook is already open to simplify the code
  
  Dim OutApp As Object
  Dim OutMail As Object
  
  Dim sBody As String
  
  'Get the Email Body from the cell
  sBody = ActiveSheet.Range("B12").Value
  
  
 'Attempt to create an Outlook object
  On Error Resume Next
  Set OutApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Err.Clear
    MsgBox "NOTHING DONE.  The Outlook Object could not be created from Excel." & vbCrLf & _
           "Try again when Outlook is open."
    Exit Sub
  End If
  On Error GoTo 0
  
  
  'Create the Outlook Mail Object (using the default Email account)
  Set OutMail = OutApp.CreateItem(0)
 
  'Determine the values to be sent
  With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Cell B12 as HTMLBody (as is)"
    .HTMLBody = sBody
     
    .Display
    
    '.Send - comment out the 'Display line' if you want to send
  End With
  
  'Clear the Object Pointers
  Set OutMail = Nothing
  Set OutApp = Nothing

End Sub

Sub OutlookEmailB12AsHTMLBodyConverted()
  'This assumes that Outlook is already open to simplify the code
  
  Dim OutApp As Object
  Dim OutMail As Object
  
  Dim sBody As String
  
  'Get the Email Body from the cell
  sBody = ActiveSheet.Range("B12").Value
  
  'Replace ASCII NEW LINE with HTML NEW LINE
  sBody = Replace(sBody, vbCrLf, "<BR>")
  sBody = Replace(sBody, vbLf, "<BR>")
  sBody = Replace(sBody, vbCr, "<BR>")
  
  
 'Attempt to create an Outlook object
  On Error Resume Next
  Set OutApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Err.Clear
    MsgBox "NOTHING DONE.  The Outlook Object could not be created from Excel." & vbCrLf & _
           "Try again when Outlook is open."
    Exit Sub
  End If
  On Error GoTo 0
  
  
  'Create the Outlook Mail Object (using the default Email account)
  Set OutMail = OutApp.CreateItem(0)
 
  'Determine the values to be sent
  With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Cell B12 as HTMLBody (Converted To HTML)"
    .HTMLBody = sBody
     
    .Display
    
    '.Send - comment out the 'Display line' if you want to send
  End With
  
  'Clear the Object Pointers
  Set OutMail = Nothing
  Set OutApp = Nothing

End Sub


Sub OutlookEmailB14AsBodyAsIs()
  'This assumes that Outlook is already open to simplify the code
  
  Dim OutApp As Object
  Dim OutMail As Object
  
  Dim sBody As String
  
  'Get the Email Body from the cell
  sBody = ActiveSheet.Range("B14").Value
  
  
 'Attempt to create an Outlook object
  On Error Resume Next
  Set OutApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Err.Clear
    MsgBox "NOTHING DONE.  The Outlook Object could not be created from Excel." & vbCrLf & _
           "Try again when Outlook is open."
    Exit Sub
  End If
  On Error GoTo 0
  
  
  'Create the Outlook Mail Object (using the default Email account)
  Set OutMail = OutApp.CreateItem(0)
 
  'Determine the values to be sent
  With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Cell B14 as Body (as is)"
    .Body = sBody
     
    .Display
    
    '.Send - comment out the 'Display line' if you want to send
  End With
  
  'Clear the Object Pointers
  Set OutMail = Nothing
  Set OutApp = Nothing

End Sub


Sub OutlookEmailB14AsHTMLBodyAsIs()
  'This assumes that Outlook is already open to simplify the code
  
  Dim OutApp As Object
  Dim OutMail As Object
  
  Dim sBody As String
  
  'Get the Email Body from the cell
  sBody = ActiveSheet.Range("B14").Value
  
  
 'Attempt to create an Outlook object
  On Error Resume Next
  Set OutApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Err.Clear
    MsgBox "NOTHING DONE.  The Outlook Object could not be created from Excel." & vbCrLf & _
           "Try again when Outlook is open."
    Exit Sub
  End If
  On Error GoTo 0
  
  
  'Create the Outlook Mail Object (using the default Email account)
  Set OutMail = OutApp.CreateItem(0)
 
  'Determine the values to be sent
  With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Cell B14 as HTMLBody (as is)"
    .HTMLBody = sBody
     
    .Display
    
    '.Send - comment out the 'Display line' if you want to send
  End With
  
  'Clear the Object Pointers
  Set OutMail = Nothing
  Set OutApp = Nothing

End Sub

Sub OutlookEmailB14AsBodyConverted()
  'This assumes that Outlook is already open to simplify the code
  
  Dim OutApp As Object
  Dim OutMail As Object
  
  Dim sBody As String
  
  'Get the Email Body from the cell
  sBody = ActiveSheet.Range("B14").Value
  
  'Replace HTML NEW LINE with  NEW LINE
  sBody = Replace(sBody, "<BR>", vbCr)
  
  
 'Attempt to create an Outlook object
  On Error Resume Next
  Set OutApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then
    Err.Clear
    MsgBox "NOTHING DONE.  The Outlook Object could not be created from Excel." & vbCrLf & _
           "Try again when Outlook is open."
    Exit Sub
  End If
  On Error GoTo 0
  
  
  'Create the Outlook Mail Object (using the default Email account)
  Set OutMail = OutApp.CreateItem(0)
 
  'Determine the values to be sent
  With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Cell B14 as Body (Converted To Body)"
    .Body = sBody
     
    .Display
    
    '.Send - comment out the 'Display line' if you want to send
  End With
  
  'Clear the Object Pointers
  Set OutMail = Nothing
  Set OutApp = Nothing

End Sub

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Option Explicit

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

Thank you very much for your explanation.

For the line breaks, the problem is solved (although I will need more time to understand it all, because the ASCII part is completely new to me). I have the cell in my worksheet to read as HTML and it works thanks to you.

The thing is, to have my signature, which contains an image, I need to have .htmlbody, right? But that way, how can i keep the format on the body? Because when I convert to .body, I loose the image from the signature. I've tried to convert to .body and then convert back to .htmlbody but that does not seem to do the trick.

Anyway, thank you for the explanation. Not only you tried to help with the problem, but also provided a way to learn the basis, so I am grateful for your answer.

PS: thank you for the Variable Declaration advice. It's a nice control to have.