Hello,

I am trying to automate emails from excel based on cells populated with data, my issue is there could be one line of data or twenty lines of data on any given day. Any ideas on how I can have the email only populate cells that have data? I have only gone to B11/C11 for testing purposes, ideally I would have this up to one hundred to allow for days where the numbers spike without anything being missed.

Thanks

Damien

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()

Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 2 To 2 'data in rows 2 to 2
' Get the email address
Email = Range("E2")

' Message subject
Subj = "Client Balances"

' Compose the message
Msg = ""
Msg = Msg & "Dear " & Range("D2") & "," & vbCrLf & vbCrLf
Msg = Msg & "Below is a listing of client balances." & vbCrLf & vbCrLf
Msg = Msg & Range("B2") & " - $" & Range("C2") & vbCrLf & vbCrLf
Msg = Msg & Range("B3") & " - $" & Range("C3") & vbCrLf & vbCrLf
Msg = Msg & Range("B4") & " - $" & Range("C4") & vbCrLf & vbCrLf
Msg = Msg & Range("B5") & " - $" & Range("C5") & vbCrLf & vbCrLf
Msg = Msg & Range("B6") & " - $" & Range("C6") & vbCrLf & vbCrLf
Msg = Msg & Range("B7") & " - $" & Range("C7") & vbCrLf & vbCrLf
Msg = Msg & Range("B8") & " - $" & Range("C8") & vbCrLf & vbCrLf
Msg = Msg & Range("B9") & " - $" & Range("C9") & vbCrLf & vbCrLf
Msg = Msg & Range("B10") & " - $" & Range("C10") & vbCrLf & vbCrLf
Msg = Msg & Range("B11") & " - $" & Range("C11") & vbCrLf & vbCrLf
Msg = Msg & "Regards," & vbCrLf
Msg = Msg & "Cash Settlements" & vbCrLf

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") ' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

Next r
End Sub