Sending Emails containing cell info - Formatting Help

Hi All,

I am currently using a piece of coding that i found and modified so that when i highlight a row of data that i have which contacts name, email address, sales order number etc etc, and press the macro shortcut key, it sends an email to that email address containing the other info along with some text to make it sound more human.

The issue im having is with formatting and custom stationary etc, background, colours and all that.

Ive read in a few places that changing the format to HTML will solve all of my issues. The problem i have is that, as i am really new to all this, i dont know how to convert what ive got to html and i dont want to lose the coding i have, just incase.

The code im using is:

Sub SendMail()
Dim OutlookApp As Object: Set OutlookApp = CreateObject("Outlook.Application")
Dim var As Variant: var = Selection.Value
Set MyMail = OutlookApp.CreateItem(0)
          With MyMail
                    .To = var(1, 6)
                    .Subject = "Dispatch"
                    .body = "Dear" & " " & var(1, 9) & " " & var(1, 7) & vbNewLine & vbNewLine & "We wanted to let you know that your artwork is ready to be dispatched. " & vbNewLine & vbNewLine & "Please email us or call " & var(1, 25) & " on phone number ext. " & var(1, 26) & " and quote " & var(1, 3) & " at your earliest convenience to arrange a suitable week day (Monday-Friday) for your item to be delivered." & vbNewLine & vbNewLine & "Kind Regards" & var(1, 11)
                End With
                MyMail.send
End Sub

Thanks in advance!

Welcome to the forum! You will need to learn html tags to get close to what you want using htmlBody rather than Body. e.g. vbNewLine would be (BR) with <> rather than ().

Had you wanted a range converted to html, you could have used this method. https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

The other method would be to use WordEditor to make your Body.

Hi Ken,

I've thought of another way around it, thats a little bit of a pain, but is only an extra few clicks.

Ive added .Display after the subject line and the email opens with all of the data, rather than automatically sending it.

Then i can do whatever formatting is needed, signature etc, which only takes a few seconds.

Thanks though mate!

Hi marcosis,

The following code may help you. It contains working example code that demonstrates how to convert your text to HTML and also how to add a Signature.

Sub OutlookEmailHTMLBodyConvertedAddSignature()
  'This assumes that Outlook is already open to simplify the code
  '
  'The 'Font Name' and 'Font Size' attributes are variables obtained from the Spreadsheet
  
  Dim OutApp As Object
  Dim OutMail As Object
  
  Dim sBody As String
  Dim sFontName As String
  Dim sFontSize As String
  
  'Set the Font Name and Font Size
  sFontName = "Arial"
  sFontSize = "13"
  
  
  'Get the Email Body from the cell
  sBody = "Body line one." & vbCrLf & "Body line two."      'Creates text body
  
  'Replace ASCII NEW LINE with HTML NEW LINE
  sBody = Replace(sBody, vbCrLf, "<BR>")                    'Converts text body to HTML
  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)
  
  'Grab the Signature
  OutMail.display                 'Creates .HTMLbody containing the signature
 
  'Determine the values to be sent
  With OutMail
    .To = ""
    .CC = ""
    .BCC = ""
    .Subject = "Text Body (Converted To HTML) with Signature - Font Attributes"
     
    'Put New .HTMLbody (containing font information)  around sBody (HTML body) and in front of the signature .HTMLBody
    .htmlbody = "<p style='font-family:" & sFontName & ";font-size:" & sFontSize & "pt'>" & sBody & "</p>" & .htmlbody
     
    .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

Lewis

Hey LJMetzger,

Thanks for that response. As I said, im really new to this. I think ive followed the instructions, but i'm getting a type mismatch error now. When I step in to it, it seems to be in my email body. Basically the part I did lol

Sub OutlookEmailHTMLBodyConvertedAddSignature()
  'This assumes that Outlook is already open to simplify the code
  '
  'The 'Font Name' and 'Font Size' attributes are variables obtained from the Spreadsheet
  
  Dim OutApp As Object
  Dim OutMail As Object
  Dim var As Variant: var = Selection.Value
  Dim sBody As String
  Dim sFontName As String
  Dim sFontSize As String
  
  'Set the Font Name and Font Size
  sFontName = "Arial"
  sFontSize = "13"
  
  
  'Get the Email Body from the cell
  sBody = "Dear" & " " & var(1, 9) & " " & var(1, 7) & vbCrLf & "We wanted to let you know that your artwork titled " & var(1, 5) & " is ready to be dispatched. " & vbCrLf & "Please email us or call " & var(1, 25) & " on ******* ext. " & var(1, 26) & " and quote " & var(1, 3) & " at your earliest convenience to arrange a suitable week day (Monday-Friday), giving at least 48 hours notice, for your item to be delivered." & vbCrLf & "Kind Regards" & var(1, 12)      'Creates text body
  
  'Replace ASCII NEW LINE with HTML NEW LINE
  sBody = Replace(sBody, vbCrLf, "<BR>")                    'Converts text body to HTML
  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)
  
  'Grab the Signature
  OutMail.Display                 'Creates .HTMLbody containing the signature
 
  'Determine the values to be sent
  With OutMail
    .To = var(1, 6)
    .CC = ""
    .BCC = ""
    .Subject = "Dispatch"
     
    'Put New .HTMLbody (containing font information)  around sBody (HTML body) and in front of the signature .HTMLBody
    .HTMLBody = "<p style='font-family:" & Arial & ";13:" & sFontSize & "pt'>" & sBody & "</p>" & .HTMLBody
     
    .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

By the way, the stars are the phone number for my place. It is an actual number.

Ignore me!

Im a moron! I didnt highlight the row i wanted to send!

WORKS PERFECTLY THOUGH! CHEERS!

Tell a lie.

I dont want each line to be right under the current one, i want to leave 1 space. For example:

Text 1

Text 2

Text 3

Ive tried writing & vbCrLf & vbCrLf & or <br><br> and it still puts it directly under one another.

Cheers in advance

Hi,

The following is a slightly modified copy of your code from post #6:
a. One line would not compile, so I commented it out and replaced it with my original line.
b. I added some vbCrLf characters, and it seems to double space in the appropriate places.
NOTE: An underscore at the end of the line denotes a 'continuation line'.

Option Explicit

Sub OutlookEmailHTMLBodyConvertedAddSignature()
  'This assumes that Outlook is already open to simplify the code
  '
  'The 'Font Name' and 'Font Size' attributes are variables obtained from the Spreadsheet
  
  Dim OutApp As Object
  Dim OutMail As Object
  Dim var As Variant: var = Selection.Value
  Dim sBody As String
  Dim sFontName As String
  Dim sFontSize As String
  
  'Set the Font Name and Font Size
  sFontName = "Arial"
  sFontSize = "13"
  
  
  'Get the Email Body from the cell
  sBody = "Dear" & " " & var(1, 9) & " " & var(1, 7) & vbCrLf & vbCrLf & _
          "We wanted to let you know that your artwork titled " & var(1, 5) & " is ready to be dispatched. " & vbCrLf & vbCrLf & _
          "Please email us or call " & var(1, 25) & " on ******* ext. " & var(1, 26) & " and quote " & var(1, 3) & _
          " at your earliest convenience to arrange a suitable week day (Monday-Friday), giving at least 48 hours notice, for your item to be delivered." & vbCrLf & vbCrLf & _
          "Kind Regards" & vbCrLf _
          & var(1, 12)      'Creates text body
  
  'Replace ASCII NEW LINE with HTML NEW LINE
  sBody = Replace(sBody, vbCrLf, "<BR>")                    'Converts text body to HTML
  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)
  
  'Grab the Signature
  OutMail.Display                 'Creates .HTMLbody containing the signature
 
  'Determine the values to be sent
  With OutMail
    .To = var(1, 6)
    .CC = ""
    .BCC = ""
    .Subject = "Dispatch"
     
    'Put New .HTMLbody (containing font information)  around sBody (HTML body) and in front of the signature .HTMLBody
    ' .HTMLBody = "<p style='font-family:" & Arial & ";13:" & sFontSize & "pt'>" & sBody & "</p>" & .HTMLBody    'Original - did not compile
      sFontName = "Arial"
      sFontSize = "13"
     .htmlbody = "<p style='font-family:" & sFontName & ";font-size:" & sFontSize & "pt'>" & sBody & "</p>" & .htmlbody
     
    .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

Hey Lewis!

Thanks alot! Works like a dream and also the fact that you are teaching me the stuff and not just pasting some code for me to try and understand.

Much appreciated!