1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Outlook Email with Excel VBA body as HTML Table format

Discussion in 'VBA Macros' started by Monty, Apr 18, 2017.

  1. Monty

    Monty Well-Known Member

    Messages:
    833
    Hello Everybody.

    Seeking help on Sending email from Outlook using excel vba attached the requirement.
    Am little poor in Email excel vba code..trying at my end, due to time constraint posting in our forum.

    Basically challenge part is showing table structure in body based on condition.

    I have provided necessary screenshot as an example to show output.

    Any Help much appreciated.

    Thanks as always.

    Attached Files:

  2. Logit

    Logit Active Member

    Messages:
    100
    Code (vb):

    Option Explicit
    Sub CopyRows()
    Dim i As Integer
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Brewery Dashboard")
        ws1.Range("U47:J49").Copy
        Mail_Selection_Range_Outlook_Body
    End Sub

    Sub Mail_Selection_Range_Outlook_Body()
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim lEndRow
    Dim Value As String
    Set rng = Nothing
    ' Only send the visible cells in the selection.
    Set rng = Sheets("Brewery Dashboard").Range("U47:J49")
    If rng Is Nothing Then
        MsgBox "An unknown error has occurred. "
        Exit Sub
    End If
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = "Your email address here in quotes"
        .CC = ""
        .BCC = ""
        .Subject = "Trigger Point for Cars On Hand"

        .HTMLBody = "<p>Text above Excel cells" & "<br><br>" & _
                    RangetoHTML(rng) & "<br><br>" & _
                    "Text below Excel cells.</p>"
     
        ' In place of the following statement, you can use ".Display" to
       ' display the e-mail message.
       .Display
    End With
    On Error GoTo 0
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub
    Function RangetoHTML(rng As Range)
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook
        TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
        'Copy the range and create a new workbook to past the data in
       rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
        'Publish the sheet to a htm file
       With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
        'Read all data from the htm file into RangetoHTML
       Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        RangetoHTML = ts.ReadAll
        ts.Close
        RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
        'Close TempWB
       TempWB.Close savechanges:=False
        'Delete the htm file we used in this function
       Kill TempFile
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function
     
    You will need one more macro to add to this. Something that will identify the table structure based on condition. Ex: If condition A, copy paste one row to
    Sheets("Brewery Dashboard").Range("U47:J49") ...

    If condition B copy paste two rows to
    Sheets("Brewery Dashboard").Range("U47:J49")

    or however large the range needs to be.

    Attached Files:

  3. Monty

    Monty Well-Known Member

    Messages:
    833
    Thank you Logit for the code.
    i have below sample code which will print data in outlook body in table format.

    Question is when you try to break into several lines below line getting error not allowing to break into lines as below line is only a sample , But my actual code of line has already touched the end of vba editor in a single line and need to break it now.

    Any thoughts.


    Code (vb):
    .HTMLBody = "<Table><TH>Heading1</h1><TH>Heading2</h1><TH>Heading3</h1><TR><TD>" & one & "</td><TD>" & Two & "</td><TD>" & Two & "</td></html>"
  4. Logit

    Logit Active Member

    Messages:
    100
    Place the Word Numbers in quotation marks :

    Code (vb):

        .HTMLBody = "<Table><TH>Heading1</h1><TH>Heading2</h1><TH>Heading3</h1><TR><TD>" & "one" & "</td><TD>" & "Two" & "</td><TD>" & "Two" & "</td></html>"
     
  5. Monty

    Monty Well-Known Member

    Messages:
    833
    Hello Logit.

    <TD>" & "Two" & "</td>

    1) We cannot use in quotation marks as it is a variable name.
    2) As my requirement is to break that single line into several lines..Tried using &_ but no luck.

    Thanks!
  6. Logit

    Logit Active Member

    Messages:
    100
    Hmmm ... post your workbook for review please.
  7. Monty

    Monty Well-Known Member

    Messages:
    833
    Logit

    Really sorry as it was designed and developed at office...so provided you a sample line of code.

    Just need to break into links as below.

    .HTMLBody = "<Table><TH>Heading1</h1><TH>Heading2</h1>
    <TH>Heading3</h1><TR><TD>" & one & "</td><TD>" & Two & "</td><TD>" & Two & "</td></html>"

    Thanks any way it really helped!
  8. Logit

    Logit Active Member

    Messages:
    100
    You are welcome. Thanks
    Monty likes this.
  9. Monty

    Monty Well-Known Member

    Messages:
    833
    Hello everyone!

    This below line killing me...tried all the possible ways.

    it's a sample code of line otherwise actual line is very big where i have to break into next line which looks impossible for me.


    Code (vb):
    .HTMLBody = "<Table><TH>Heading1</h1><TH>Heading2</h1><TH>Heading3</h1><TR><TD>" & one & "</td><TD>" & Two & "</td><TD>" & Two & "</td></html>"

    What am looking for is...if the above line is in single line it works but when we try to break into several lines shows an error....How to break into next line

    Thanks
    Monty!
  10. Logit

    Logit Active Member

    Messages:
    100
  11. Monty

    Monty Well-Known Member

    Messages:
    833
    Hello Logitech.

    I developed that entire line with that site...As I said it work s as long as in single line ...But my line of code is coming two lines which not acceptable by VBA.
  12. Logit

    Logit Active Member

    Messages:
    100
    Sorry ...I didn't realize you had been there before. I'm not well versed in HTML in VBA code.

    :rolleyes:
  13. Monty

    Monty Well-Known Member

    Messages:
    833
    Hmmm..That's the trick here...First created HTML table tags to see the out put in a web browser desired table format...Then need embed into VBA .
  14. Monty

    Monty Well-Known Member

    Messages:
    833
    Hello everyone.

    Finally i understood to break .htmlbody line in vba to get table format and to break into several lines by the below magic letter.

    &_

    Thanks

    Monty!
  15. SirJB7

    SirJB7 Excel R┼Źnin

    Messages:
    8,894
    Monty likes this.
  16. Monty

    Monty Well-Known Member

    Messages:
    833
    Hello SirJB7

    This is more of not sending just a mail from excel vba to outlook but building a table based on the range in excel...So finally got it.

    I was facing the issue in breaking this below line into second line.

    Now problem is solved it was so simple but killed by time.


    .HTMLBody = "<Table><TH>Heading1</h1><TH>Heading2</h1>
    <TH>Heading3</h1><TR><TD>" & one & "</td><TD>" & Two & "</td><TD>" & Two & "</td></html>"

Share This Page