Sending Email Formatting Problem Via VBA

[RLPeloquin]

My problem is with this line of code: strBody = "Here is a copy of your power usage: " & RangeConc(ActiveSheet.Range("A1:H41")). The code works fine but when the email is sent it doesn't retain the same format when sent: This is what I need it to look like: Entire Code below

Public Function RangeConc(ByRef argRange As Range) As String If Not argRange Is Nothing Then For Each c In argRange sTmp = sTmp & c.Text & vbCrLf Next c RangeConc = sTmp End If End Function Sub Send_Email() Dim CDO_Mail As Object Dim CDO_Config As Object Dim SMTP_Config As Variant Dim strSubject As String Dim strFrom As String Dim strTo As String Dim strCc As String Dim strBcc As String Dim strBody As String Dim c As Range Dim sTmp As String strSubject = "Results of your power useage" strFrom = "johndoe@gmail.com" strTo = "janedoe@gmail.com" strCc = "" strBcc = "" strBody = "Here is a copy of your power usage: " & RangeConc(ActiveSheet.Range("A1:H41")) Set CDO_Mail = CreateObject("CDO.Message") On Error GoTo Error_Handling Set CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1 Set SMTP_Config = CDO_Config.Fields With SMTP_Config .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "johndoe@gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "123456" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True .Update End With With CDO_Mail Set .Configuration = CDO_Config End With CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Error_Handling: If Err.Description <> "" Then MsgBox Err.Description End Sub

 

[daverunt]

Having not used CDO for many years I haven't tried any of the following so take a look.

According to Rons site you can use .HTMLBody instead of .TextBody to send mails with CDO.

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

1. It might be worth trying a simple HTML message with a URL link for example to see if it works as per Rons example.
This suggests the need create the HTML content using tags and your cell contents, which could be done if the .HTMLBody works and if the function mentioned below doesn't.

2. Then see if you mail works using the RangetoHTML function that can be found on Rons site here for the range you want to send.

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl