I need to send email formed from excel with table or if cells are blank when delete and go to another cell

[missrutele]

Hello,

i need to form body text from excel to send via outlook. One problem, that sometimes in can be 10 names and surnames, or it can be 5 names and surnames. After list goes normal text and signature.

I have this one, from K20 goes fixed text, but from K12 until K20 goes list of people, sometimes it can be shorter than 8 rows so when in email I see a lot of spaces, how can I fix it?:

Sub SendMail()


'SendEmail Macro
'


'
Dim outlApp As Outlook.Application
Set outlApp = CreateObject("Outlook.Application")

Dim outlMail As Outlook.MailItem
Set outlMail = outlApp.CreateItem(outlMailItem)

outlMail.To = Range("K5") ' <-- Put email of the recipient here
outlMail.Subject = "19V69 nuotoliniai mokymai ir prizai"
outlMail.CC = Range("K6") ' <-- Put email of 'copy to' recipient here
outlMail.HTMLBody = Range("K8").Value & "<br><br>" _
& Range("K9").Value & "<br><br>" _
& Range("K10").Value & "<br><br>" _
& Range("K11").Value & "<br><br>" _
& Range("K12").Value & "<br><br>" _
& Range("K13").Value & "<br><br>" _
& Range("K14").Value & "<br><br>" _
& Range("K15").Value & "<br><br>" _
& Range("K16").Value & "<br><br>" _
& Range("K17").Value & "<br><br>" _
& Range("K18").Value & "<br><br>" _
& Range("K19").Value & "<br><br>" _
& Range("K20").Value & "<br><br>" _
& Range("K21").Value & "<br><br>" _
& Range("K22").Value & "<br><br>" _
& Range("K23").Value & vbLf _


outlMail.send

Set outlApp = Nothing
Range("D12").Value = Range("D12").Value + 1

End Sub

Thank you

 

[daverunt]

Hi,

I think this was one I posted?
The code hasn't rendered properly in your post.

You can set your range that the loop will go through in column K.
In the case below it takes K9 as the start of the range and looks up column K to find the last filled row.
It loops through the rows and adds content if found otherwise skips.
The content of MailBody is built up as it loops.

Then you use outlMail.HTMLBody=MailBody

Set rng = Range(Range("K9"), Range("K" & Rows.Count).End(xlUp))
  
   For Each cell In rng
     
    If cell.Value <> "" Then
          
    MailBody = MailBody & cell.Value & "< br >< br >"

 End If
Next