Mail Body Get Text

[blewbot]

I need a macro or sub routine to get text for the body of a email using this code

Sub SendMail()     
Dim OutApp As Object  
Dim OutMail As Object          
Set OutApp = CreateObject("Outlook.Application")    
OutApp.Session.Logon     
Set OutMail = OutApp.CreateItem(0)        
  
  On Error Resume Next   
  
With OutMail        
.To = "joe@whatever.com"         
.cc = ""         
.BCC = ""        
.Subject = "checklist"  
.Body = GetText         
.attachments.Add ActiveWorkbook.FullName     
.Send
     End With     
 On Error Goto 0            
Set OutMail = Nothing      
Set OutApp = Nothing  

End Sub

I need for GetText to be the cell contents of the cell immediately left (0,-1) of every cell that containts exactly "r".

Sometimes it is only one cell sometimes it is 10. but i need each cell be copied and pasted into the body of the email.

Any solutions? I was unable to find anything.

 

[texasalynn]

is this macro one that you currently use with another macro? Does the GetText get defined in that macro? Or did you just put that there?

 

[blewbot]

is this macro one that you currently use with another macro? Does the GetText get defined in that macro? Or did you just put that there?

I just put it there...

that is what i need a macro that "gets the text" and the text being the contents of the cell directly to the left(0,-1) of each cell that contains exactly "r"

Thank you for the help

 

[texasalynn]

here is a sample of putting the data together

Public GetText As String
Sub EMail_GetText()
Dim LR As Long, i As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 2 To LR
    If Cells(i, 2) = "r" Then
        If GetText = "" Then
            GetText = Cells(i, 1)
        Else
            GetText = GetText & Chr(13) & Cells(i, 1)
        End If
    End If
 
Next i
MsgBox "" & GetText
Application.ScreenUpdating = True
End Sub

the public statement goes at the very beginning of the module in vba. You could then change out the msgbox statement to do a Call SendMail

HTH

 

[blewbot]

OK. Thank you, that does part of what i need. i does list all of the cells to the left of the ones containing "r" but it loops. it lists them multiple times, i only need it to list them once.

And im sorry if this is obvious but i cant seem to figure out how to make it take the text which it finds and send it to the send mail macro from above.

Almost there, thanks for all the help

 

[blewbot]

Sorry I spoke too soon. Thank you I have Figured it out!

Exactly what i needed, no looping and it posts to the email.

 

[texasalynn]

glad it worked - thanks for the feedback