Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Send mail body from cell value and text file

    Hello,

    I send mail with this script and the contents of body are cell values, now i want to add the contents of text which is in "c:\Email Contents\covering.txt". Please some guide me doing this its very important to have the contents of this text file in the mail body.
    Code:
        Option Explicit
        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
        
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set sh = Sheets("sendemail")
    
        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)
        
        On Error Resume Next
            With OutMail
            .To = cell.Value
            '.CC = ""
            .Subject = "Reports & Statements "
            .Body = "Dear Sir / Madam," & vbNewLine & vbNewLine & _
                    "Status : " & cell.Offset(0, 3).Value _
                    & vbNewLine & vbNewLine & _
                    "Report No.: " & cell.Offset(0, -5).Value _
                    & vbNewLine & vbNewLine
            
      GetBoiler("c:\Email Contents\covering.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
    Are you looking for perhaps by changing the line to:

    .Body = .Body & GetBoiler("c:\Email Contents\covering.txt")

    This will append the contents obtained by the function onto the end of the Body you had already created.

    Steve

Posting Permissions

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