Reading htm file to .HTMLBody VBA
I'm attempting to automate an email using an Excel worksheet and VBA. I'm able to copy the desired range into the email, but I want to use a htm file for the HTML formatting.
How do I read a htm file and add it to the .HTMLBody of my email?
Here's my code, which sends an email with the correct worksheet, but does not include the HTML formatting that is added with the test(path) function:
Sub Send_To_Outlook() Dim AWorksheet As Worksheet Dim Sendrng As range Dim rng As range Dim text As String Dim textline As String Dim sPath As String sPath = "H:\My Documents\email.htm" On Error GoTo StopMacro With Application .ScreenUpdating = False .EnableEvents = False End With 'Fill in the Worksheet/range you want to mail Set Sendrng = Worksheets("Email").range("C6:L244") 'Remember the activesheet Set AWorksheet = ActiveSheet With Sendrng ' Select the worksheet with the range you want to send .Parent.Select 'Remember the ActiveCell on that worksheet Set rng = ActiveCell 'Select the range you want to mail .Select ' Create the mail and send it ActiveWorkbook.EnvelopeVisible = True With .Parent.MailEnvelope With .Item .To = "firstname.lastname@example.org" .CC = "" .BCC = "" .Subject = "My subject" .HTMLBody = test(sPath) .Send End With End With 'select the original ActiveCell rng.Select End With 'Activate the sheet that was active before you run the macro AWorksheet.Select StopMacro: With Application .ScreenUpdating = True .EnableEvents = True End With ActiveWorkbook.EnvelopeVisible = False End Sub Function test(sPath As String) Dim oFSO As Object Dim oFS As Object, sText As String Set oFSO = CreateObject("Scripting.FileSystemObject") Set oFS = oFSO.OpenTextFile(sPath) test= oFS.ReadAll() End Function
Any suggestions or advice on why this isn't working would be awesome!
PS I also need to display the message instead of send, but this isn't as important of an issue.
Your function doesn't return any value. Try this:
Function test(sPath As String) test = CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath).ReadAll() End Function
vba - send email with .htm file embeded as body - MSDN, excel vba and with the following code, but just can not get the htm file to load into the body area of the email. I'd recommend checking out the HTMLBody property on a number of existing emails to see how things look. -- The HTMLBody property should be an HTML syntax string. Setting the HTMLBody property will always update the Body property immediately. Example. The following Visual Basic for Applications (VBA) example creates a new MailItem object and sets the BodyFormat property to olFormatHTML. The body text of the email item will now appear in HTML format.
I solved my problem. There was an issue when using html coupled with sending the worksheet range in the above code. I decided to covert the worksheet into html, export the chart into an image and insert it into the rest of the html for the email.
Sub Mail_Sheet_Outlook_Body() Dim rng1 As range Dim rng2 As range Dim OutApp As Object Dim OutMail As Object Dim newimage As Action Dim aPath As String Dim bPath As String Dim sPath As String 'Name the variables for your the needed paths sPath = "C:\Chart1.png" aPath = "C:\email1.htm" bPath = "C:\email2.htm" 'Export your chart as an image Call ExportChart("Chart1") 'Select the range your desired tables are in Set rng1 = Worksheets("Email").range("C6:L32") Set rng2 = Worksheets("Email").range("C45:L244") Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) 'Create the email On Error Resume Next With OutMail .To = "email@example.com" .CC = "" .BCC = "" .Subject = "This is the Subject line" ' Place your tables in the correct location of your html for the email .HTMLBody = test(aPath) & RangetoHTML(rng1) & "<img src=" & "'" & sPath & "'" & "width=888; height=198>" & RangetoHTML(rng2) & test(bPath) .Display End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Function ExportChart(sChartName As String) ' Export a selected chart as a picture Const sSlash$ = "/" Const sPicType$ = ".png" Dim sPath$ Dim sBook$ Dim objChart As ChartObject On Error Resume Next ' Test if there are even any embedded charts on the activesheet ' If not, let the user know Set objChart = ActiveSheet.ChartObjects(1) If objChart Is Nothing Then MsgBox "No charts have been detected on this sheet", 0 Exit Function End If ' Test if there is a single chart selected If ActiveChart Is Nothing Then MsgBox "You must select a single chart for exporting ", 0 Exit Function End If Start: ' chart is exported as a picture, Chart1.png in the same ' folder location as the workbook sBook = ActiveWorkbook.path sPath = sBook & sSlash & sChartName & sPicType ActiveChart.Export Filename:=sPath, FilterName:="PNG" With Application .EnableEvents = False .ScreenUpdating = False End With End Function 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 Function test(sPath As String) 'Returns a string after reading the contents of a given file test = CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath).ReadAll() End Function
Thanks for all of your help! :)
HTMLBody Property, 07/11/2006; 2 minutes to read. In this article. Remarks; Example. Returns or sets a String representing the HTML body of the specified item. Example. The following Visual Basic for Applications (VBA) example creates a new Scraping a website HTML in VBA. This blog shows how to go through a website, making sense of its HTML within VBA. We'll break the problem down into several chunks - to whit: Defining what we want to achieve. Analysing the target URL (the target website). Referencing the required applications. Getting at the underlying HTML. Parsing the data
When you say that your code doesn't work, does that mean that you get an error or that the code executes but the email body is empty?
I would first check to see if your "test" Function is returning a null string:
Function test(sPath As String) Dim oFSO As Object Dim oFS As Object, sText As String Set oFSO = CreateObject("Scripting.FileSystemObject") Set oFS = oFSO.OpenTextFile(sPath) ' I don't think you need to loop until EOF with .ReadAll sText = oFS.ReadAll ' This will print sText to the Immediate Window; if it is 0, then sText is null Debug.Print ("sText string has a length of: " & Len(sText)) End Function
My guess is that sText is null. If it is reading the .htm successfully, I would next check to make sure that the .htm is valid .html syntax.
Read an HTML file into the body of an Outlook e , Read a pre-formatted HTML document into the body of an Outlook message. Sometimes First insert a new module in the Outlook VBA editor (the principles involved are virtually identical to those covered at HTMLBody & strHTM(i) & vbCr 2 minutes to read; In this article. Returns or sets a String representing the HTML body of the specified item. The HTMLBody property should be an HTML syntax string. Read/write. Setting the HTMLBody property sets the EditorType property of the item's Inspector to olEditorHTML. Setting the HTMLBody property will always update the Body property immediately.
Insert HTML file using VBA for email body in Outlook? : excel, With OutMail .To = "firstname.lastname@example.org" .Subject = "Email Subject" .HTMLBody = "<html><body><table style='font-family: Calibri; font-size: 14px; width: 600px; VBA Read specific number of lines from a text file In cases when you want to read specific lines from a text file you can adapt the line by line read code as below. It allows you to read a certain number of lines (noLines) from a text file from a specific start line number (sLine) .
Outlook Email with Excel VBA body as HTML Table format , Publish (True) End With 'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso. A file with the HTM or HTML file extension is a Hypertext Markup Language file and is the standard web page file type on the internet. Since HTM files are text-only files, they just contain text (like what you're reading now), as well as text references to other external files (like the image in this article).
insert HtML file in to body of email (inside loop email), You need to parse your HTML text file to read it into a string. Code: Copy to I think I need to clear the html.body rather than just the once . 1. Press the Alt + F11 simultaneously to open the Microsoft Visual Basic for Applications window. 2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the Module window. VBA code: Send email with HTML email body in Excel