Vba excel html body

Outlook Email with Excel VBA body as HTML Table format

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.

Attachments

Logit

Active Member

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 = "
Text above Excel cells" & "

" & _ RangetoHTML(rng) & "

" & _ "Text below Excel cells.
" ' 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.

Источник

Send HTML Email from Excel using VBA¶

In previous section, I introduced how to send email in a Excel VBA project. In this section, I will introduce how to send HTML email to specified recipients based on Excel sheet data.

Build HTML Template¶

Press Alt+F11 to open VBA IDE, click ThisWorkbook add a function like this:

Function BuildHtmlBody() Dim oSheet Set oSheet = ThisWorkbook.Sheets(1) Dim i, rows rows = oSheet.UsedRange.rows.Count Dim html, name, address, age, department html = "" html = html & "
" html = html & "Dear , 

This is a test email from Excel using VBA. 
" html = html & "Here is sheet1 data:

" html = html & ""' Build a html table based on rows dataFori=1Torowsname=Trim(oSheet.Cells(i,1))address=Trim(oSheet.Cells(i,2))age=Trim(oSheet.Cells(i,3))department=Trim(oSheet.Cells(i,4))html=html&""html=html&""html=html&""html=html&""html=html&""html=html&""Nexthtml=html&"
                            
     
" & name & "
     
" & address & "
     
" & age & "
     
" & department & "
     
      
" BuildHtmlBody = html End Function 

Above codes can generate a HTML table based on current Sheet1 data like this:

Now we can add another function like this:

Public Sub SendHtmlMailFromWorkBook() Dim oSheet Set oSheet = ThisWorkbook.Sheets(1) Dim i, rows rows = oSheet.UsedRange.rows.Count Dim sender, name, address, subject, bodyTemplate, body, bodyFormat bodyFormat = 1 'HTML body format ' Please change sender address to yours sender = "test@emailarchitect.net" subject = "Test email from Excel and VBA" ' Use a body template to build body text based on current workbook bodyTemplate = BuildHtmlBody() Dim emailSent emailSent = 0 For i = 2 To rows name = Trim(oSheet.Cells(i, 1)) address = Trim(oSheet.Cells(i, 2)) body = Replace(bodyTemplate, "", name) If Not SendMailTo(sender, name, address, subject, body, bodyFormat) Then Exit Sub End If emailSent = emailSent + 1 Next Application.StatusBar = "Total " & emailSent & " email(s) sent." End Sub 

Remarks: All of samples in this section are based on first section: Send email from Excel using VBA and VBScript. To compile and run the following example codes successfully, please click here to learn how to create the test project and add reference of EASendMail to your project.

Run VBA Macro¶

Close VBA IDE and back to Excel, press Alt+F8 to open Macro dialog box, select ThisWorkbook.SendHtmlMailFromWorkBook and click Run . You will see the status and result at Excel status bar.

Send Email to Specified Recipients based on Sheet Data¶

If you only want to send email to specified recipients, you can add some conditions in your codes. For example, you can only send email to the person in IT department.

Press Alt+F11 to open VBA IDE, click ThisWorkbook add a function like this:

Public Sub SendMailFromWorkBookToIT() Dim oSheet Set oSheet = ThisWorkbook.Sheets(1) Dim i, rows rows = oSheet.UsedRange.rows.Count Dim sender, name, address, subject, bodyTemplate, body, bodyFormat, department bodyFormat = 1 'HTML body format ' Please change sender address to yours sender = "test@emailarchitect.net" subject = "Test email from Excel and VBA" ' Use a body template to build body text based on current workbook bodyTemplate = BuildHtmlBody() Dim emailSent emailSent = 0 For i = 2 To rows department = Trim(oSheet.Cells(i, 4)) 'only send email to the person in IT department If UCase(department) = "IT" Then name = Trim(oSheet.Cells(i, 1)) address = Trim(oSheet.Cells(i, 2)) body = Replace(bodyTemplate, "", name) If Not SendMailTo(sender, name, address, subject, body, bodyFormat) Then Exit Sub End If emailSent = emailSent + 1 End If Next Application.StatusBar = "Total " & emailSent & " email(s) sent." End Sub 

Close VBA IDE and back to Excel, press Alt+F8 to open Macro dialog box, select ThisWorkbook.SendMailFromWorkBookToIT and click Run . You will see the status and result at Excel status bar.

Download Sample Excel VBA Project¶

You can download Sample.xlsm here, All example codes in this tutorial are included. Please enable Macro after you opened this file, otherwise the codes cannot be executed.

You need to access the Trust Center in the Excel Options dialog box. Click the Microsoft Office Button, and then click Excel Options. In the Trust Center category, click Trust Center Settings, and then click the Macro Settings category.

Next Section

In this section, I introduced how to send HTML email from Excel VBA project. By default, Macro is disabled by Excel. At next section I will introduce how to send email from VBScript directly without Excel Macro.

Источник