CODEWITHSUNDEEP
excelvbaemailoutlook
DirtyDeffy
DirtyDeffy

Reputation: 507

Sending dynamic Excel table in Outlook email

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 = "[email protected]"
    .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.

Upvotes: 1

Views: 3399

Answers (1)

0m3r
0m3r

Reputation: 12497

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

Upvotes: 1

Related Questions