Sending dynamic Excel table in Outlook email

Question

I'm trying to, in Excel 2016, send an email in Outlook 2016 with a table in the email body.
I've researched Stack Overflow as well as other websites. I find the HTML part confusing.

Below is the code I've written, to send an email. The code works, but I need to replace the .HTMLBody = "Hello World" with a table.

Sub Send_mail()

Dim outlookApp As Outlook.Application
Dim outlookMail As Outlook.MailItem

Set outlookApp = New Outlook.Application
Set outlookMail = outlookApp.CreateItem(olMailItem)

With outlookMail
    .To = "recipient@mail.com"
    .CC = ""
    .BCC = ""
    .Subject = "TEST123"
    .BodyFormat = olFormatHTML
    .HTMLBody = "Hello World"
    .Send
End With

Set outlookMail = Nothing
Set outlookApp = Nothing

End Sub

The table to insert in the body of the email consists of columns A, B, C and D.
The data always begin on row 2 but the row containing the last data needs to be dynamic.
The table will always be fairly small - max 20 rows ish.

I imagine the following might do the trick?
Locating the last populated row then looping to go through each row between 2 and the last populated row and convert it into HTML format and storing that as a string. Then concatenate those strings into one final string to put in the body of the email.

I've looked at Ron de Bruin's guides, but I would like to achieve this without having to create a temporary file or use non-Microsoft tools. The macro will have several users from a professional company, and needs to be bulletproof. I fear that saving temporary files and deleting them could result in horrible mistakes if the company's shared-folder structure is changed.

@cyboashu Thanks for your input! However, I have already read that webpage. As i mentioned in my post, I would really like to achieve this without having to create or delete files. Is this simply just impossible? :) — DirtyDeffy, Apr 11 '18 at 10:20
not impossible but too cumbersome. You will need to loop through your range's rows and then inside each row loop through cells and then create your own HTML for table. — cyboashu, Apr 11 '18 at 10:22
`ThisWorkbook.Path` returns the name of the folder containing the workbook containing the macro being executed. Using that folder will always work no matter how often they move the workbook or change the file structure. — Tony Dallimore, Apr 11 '18 at 10:44
`"Hello World"` is not Html although most browsers would display it correctly. `Hello World` is a minimal Html string. Do you need to learn Html and Css as well? — Tony Dallimore, Apr 11 '18 at 10:53
Have a look at this question: VBA Excel to Outlook font formatting. The OP for that question was happy with Ron de Bruin's solution. My answer introduces an approach closer to what you are requesting. — Tony Dallimore, Apr 11 '18 at 10:53
IF you want to send your table by e-mail, why don't you use the object [ActiveSheet.MailEnvelope](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-mailenvelope-property-excel) and properties such as `ActiveSheet.MailEnvelope.Introduction, ActiveSheet.MailEnvelope.Item.To, ActiveSheet.MailEnvelope.Item.Subject...` and so on? It's much easier indeed. — Foxfire And Burns And Burns, Apr 11 '18 at 12:17
@TonyDallimore Thank you for the help. Using `ThisWorkbook.Path` is clever. For this task i don't need to learn HTML, but at some point i should learn it. I saw your answer on the thread you referred to and it could indeed work. However @FoxfireAndBurnsAndBurns you're completely right. The following code solved my problem satisfyingly: `Sub Envelope() Range("table").Select ActiveWorkbook.EnvelopeVisible = True With ActiveSheet.MailEnvelope .Introduction = "" .Item.To = "" .Item.Subject = "" .Item.Send End With End Sub` — DirtyDeffy, Apr 12 '18 at 06:48

score 1 · Accepted Answer · answered Apr 12 '18 at 01:43

1

To Copy From Column A2: to Column D/or Column 4 last used range, Example would be

With ThisWorkbook.Worksheets("Sheet1")    
    Dim rng As Range
    Set rng = .Range("$A$2:" & .Cells( _
                       .Rows.Count, 4) _
                       .End(xlUp).Address)
    Debug.Print rng.Address ' Print on immediate Window
    rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
End With

See Copy Excel range as Picture to Outlook

answered Apr 12 '18 at 01:43

0m3r

12,286
15
35
71

Interesting approach. I couldn't find an easy way to paste the picture into the email body without creating at temporary file. Is it impossible? – DirtyDeffy Apr 12 '18 at 06:29
@JoeBerg look at this link https://stackoverflow.com/a/48897439/4539709 – 0m3r Apr 12 '18 at 07:39
1

This worked perfectly - thank you! To recap for others: This creates a picture of the desired range in Excel and then pastes it into the body of the email in Outlook. – DirtyDeffy Apr 12 '18 at 12:54

Sending dynamic Excel table in Outlook email

1 Answers1