Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    45
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Send Email & Body contents from html file

    Hello,

    The below script is sending the email from excel workbook successfully with an external text file as the contents of Email Body.

    I need help & want to send the contents of .html file as Email Body. ("c:\disclaimer.htm")

    Anyways to do that.

    Code:
        Sub Send_Files()
        Dim OutApp As Outlook.Application
        Dim OutMail As Outlook.MailItem
        Dim sh As Worksheet
        Dim cell As Range
        Dim FileCell As Range
        Dim rng As Range
        Dim strReportBody As String
    	 With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set sh = Sheets("Reference")
    
        Set OutApp = CreateObject("Outlook.Application")
            For Each cell In sh.Columns("G").Cells.SpecialCells(xlCellTypeConstants)
    
            'Enter the path/file names in the C:Z column in each row
            
            Set rng = sh.Cells(cell.Row, 1).Range("K1:Z1")
                  
            If cell.Value Like "?*@?*.?*" And _
            LCase(Cells(cell.Row, "H").Value) = "yes" _
            And LCase(Cells(cell.Row, "I").Value) <> "send" And _
            Application.WorksheetFunction.CountA(rng) > 0 Then
               
               Set OutMail = OutApp.CreateItem(olMailItem)
                    
                    strReportBody = "Dear Sir / Madam," & vbNewLine & vbNewLine & _
                    "Reference : " & cell.Offset(0, 3).Value _
                    & vbNewLine & vbNewLine & _
                    "Ref No.: " & cell.Offset(0, -5).Value _
                    & vbNewLine & vbNewLine
            
            On Error Resume Next
            With OutMail
            .To = cell.Value
            .Subject = Worksheets("Reference").Range("A2").Value
            .Body = strReportBody & GetBoiler("c:\disclaimer.txt")
                    
                    For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                        If Trim(FileCell) <> "" Then
                            If Dir(FileCell.Value) <> "" Then
                                .Attachments.Add FileCell.Value
                            End If
                        End If
                    Next FileCell
    
                    .Send
                    '.Display  'Or use Send
                    Application.Wait (Now + TimeValue("0:00:02"))
                End With
    
    On Error GoTo 0
    Cells(cell.Row, "I").Value = "send"
                Set OutMail = Nothing
            End If
        Next cell
    cleanup:
        Set OutApp = Nothing
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    
    Function GetBoiler(ByVal sFile As String) As String
        Dim fso As Object
        Dim ts As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
        GetBoiler = ts.ReadAll
        ts.Close
    End Function

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You should be able to just use replace the file extension
    .Body = strReportBody & GetBoiler("c:\disclaimer.htm")

    The issue could be if the HTML file has information that is not desired. The GetBoiler code gets all the text. If you want the GetBoiler function to only get some of the text, you will have to provide some logic of what it should keep or what it should be eliminating so it can make those choices at runtime. But if it is a standard file, I think it would make more sense to make that edit once, save the cleaned up file and import that.

    Steve

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    It would make even more sense to copy and paste the disclaimer into a file called disclaimer.txt and not change the code at all.

    Maud

  4. #4
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Alternatively, send the file as an attachment?

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •