how do I create email that contains a table

[smiler44]

I want to create an email with a table and fill it in.
I can create the email, get all the data I need, fill in the subject line, body of email, add a signature and send it.


How do I add a table or something that looks like a table and put the date in the right places?

Also how do I do this? launch excel application in hidden mode and then run the macro after opening the file in hidden mode from a VBS file.

thank you
smiler44

 

[smiler44]

I'm still playing around but Google has come to my aid so far

I wanted to only have a desktop icon to run my macro without excel coming into view.

In no order but this is the way it can be done.

Create an excel file saved as a macro enabled work book. For this example the data is in cells A1 to B7. Purchase being the name of the sheet to get the data from

add this code

  Sub test()
'For Tips see: [URL]http://www.rondebruin.nl/win/winmail/Outlook/tips.htm[/URL]
'Working in Office 2000-2013
    Dim OutApp As Object, OutMail As Object
    Dim rng As Range
    Dim StrBody As String
    StrBody = "Add some custom text" & "" & _
              "This is line 2" & "" & _
              "This is line 3" & ""
 
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    Set rng = Nothing
    On Error Resume Next
 
    Set rng = Sheets("Purchase").Range("A1:b8").SpecialCells(xlCellTypeVisible)
 
    On Error GoTo cleanup
 
 
            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = Sheets("Purchase").Cells(1, 10).Value
                .Subject = "Purchase Order Data"
                .HTMLBody = StrBody & RangetoHTML(rng)
               
                .Display  'Or use .Send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
 
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
 
End Sub

this code is a function

 Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2013
    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

I then found to run the above I should create a notepad text document and save it with a .vbs file extention. you will need perhaps to change the path below to suit your needs

 Set xl = CreateObject("Excel.Application") 
 Set wb = xl.Workbooks.Open("C:\Users\home\documents\planning escalation\table.xlsm")
  
xl.Run "'table.xlsm'!test" 
 
wb.Close 
xl.Quit

I then placed a short cut to the notepad file on my desk top and when it is double clicked it creates an email. In excel you will need to add a border to the cells you want to get the data from, if you want to see it in the email as a table. Also, in the cell below the last cell with data in, I have had to add a space. this is so the border at the bottom of the last cell that has data in in my range is shown otherwise there was no border at the bottom of my table.



this still needs some playing around with but I think I can make this better. this code wont send the email or fill in the to address but I have code that can do that. the big drawback is the path, its different on everyones pc so that is something for me to consider

smiler44