CODEWITHSUNDEEP
excelvba
DA69
DA69

Reputation: 91

VBA Sending formatted email with CDO

I have this code, that works. 

  Private Sub CommandButton1_Click()
  Dim Mail As New Message
  Dim Config As Configuration
  Set Config = Mail.Configuration
  Dim cell As Range
  Dim strbody As String
  For Each cell In Sheets("Sheet1").Range("C2:D9")
      strbody = strbody & cell.Value & vbNewLine
  Next

  Config(cdoSendUsingMethod) = cdoSendUsingPort
  Config(cdoSMTPServer) = "smtp.gmail.com"
  Config(cdoSMTPServerPort) = 25
  Config(cdoSMTPAuthenticate) = cdoBasic
  Config(cdoSMTPUseSSL) = True
  Config(cdoSendUserName) = "MyEmail"
  Config(cdoSendPassword) = "EmailPassword"
  Config.Fields.Update

  Mail.To = Sheets("Sheet1").Range("j2").Value
  Mail.From = Config(cdoSendUserName)
  Mail.Subject = "Email Subject"
  Mail.TextBody = strbody

  If G9 = "Yes" Then
  Mail.AddAttachment "C:\...file1.xls"
  Mail.AddAttachment "C:\...file2.xls"
  End If

  On Error Resume Next

  Mail.Send

  If Err.Number <> 0 Then
    MsgBox Err.Description, vbCritical, "There was an error"
    Exit Sub
  End If
  MsgBox "Your email has been send!", vbInformation, "Sent"

End Sub

It will take the text I have in C2:D9 but it does not format it as it is in the sheet, so it looks like this when sent: Item1
Result1
Item2
Result2
Item3
Result3
Item4
Result4

If I set the Mail.TextBody to Mail.HTMLBody, it comes out like this:

Item1 Result1 Item2 Result2 Item3 Result3 Item4 Result4

How can I make it send exactly what is in those ranges as it is formatted in the excel sheet? If I were to manually copy those cells and paste them into an email body, it would keep the cell structure, font size, font color, and any background colors as well.

Item1 Result1
Item2 Result2
Item3 Result3
Item4 Result4

I have been looking online a lot to try and find this but have not been able to for CDO. I found some items for Outlook but I am not using Outlook.

Is it possible to send a formatted block of cells using the CDO email method of sending an email?

Thank you.

Upvotes: 0

Views: 8093

Answers (1)

whytheq
whytheq

Reputation: 35557

An example of using HTML formatted mail is as follows. You need to include the correct tags:

Set OutApp = New Outlook.Application
Set OutMail = OutApp.CreateItem(olMailItem)

With OutMail

    fEmailTOSend = RepEmailTO


    .To = "[email protected]"
    .Subject = "report created " & strdate
    .BodyFormat = 2 '<<HTML
    .importance = 2 '<<High
    .HTMLBody = "<P>" & _
        "<BR>" & _
        "<FONT face=""Lucida Sans Unicode"" size=2.5>" & _
               myEmailNarrative & _
               "<BR>" & _
               "Kind Regards," & _
               "<BR>" & _
               "<FONT color=#0000ff>" & _
                           ProfileName & "." & _
               "</FONT>" & _
        "</FONT>" & _
        "</P>" & _
    "</BODY></HTML>"
    .Attachments.Add wb.FullName
    .Save
    .Send
End With

Upvotes: 1

Related Questions