emailing cell range

[alldreams]

I have this module that allows me to send a cell range in
the body of an email. my question is that the cell range
is a whole block of cells in a sheet and what I want to
do is only email the last line filled row of that block
of cells. The module code is below.

Any help is greatly appreciated


Sub Mail_Text_in_Body()
Dim msg As String, cell As Range
Dim Recipient As String, Subj As String, HLink As
String
Dim Recipientcc As String, Recipientbcc As String

Recipient = ""
Recipientcc = ""
Recipientbcc = ""
'Subj = "test"

msg = Sheets("PIVNUMS").Range("A18").Value & " - Piv
#s "
For Each cell In Sheets("PIVNUMS").Range("B18:AG18")
msg = msg & cell
Next cell
msg = WorksheetFunction.Substitute(msg, vbNewLine, "%
0D%0A")

HLink = "mailto:" & Recipient & "?" & "subject="
& msg & "&"
'HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & msg

ActiveWorkbook.FollowHyperlink (HLink)
'Application.Wait (Now + TimeValue("0:00:03"))
'Application.SendKeys "%s"
End Sub

 

[Ron de Bruin]

Hi

Do you always have text in the first column of that range??

 

[Paul]

I have this module that allows me to send a cell range in
the body of an email. my question is that the cell range
is a whole block of cells in a sheet and what I want to
do is only email the last line filled row of that block
of cells. The module code is below.

Any help is greatly appreciated


Sub Mail_Text_in_Body()
Dim msg As String, cell As Range
Dim Recipient As String, Subj As String, HLink As
String
Dim Recipientcc As String, Recipientbcc As String

Recipient = ""
Recipientcc = ""
Recipientbcc = ""
'Subj = "test"

msg = Sheets("PIVNUMS").Range("A18").Value & " - Piv
#s "
For Each cell In Sheets("PIVNUMS").Range("B18:AG18")
msg = msg & cell
Next cell
msg = WorksheetFunction.Substitute(msg, vbNewLine, "%
0D%0A")

HLink = "mailto:" & Recipient & "?" & "subject="
& msg & "&"
'HLink = HLink & "subject=" & Subj & "&"
HLink = HLink & "body=" & msg

ActiveWorkbook.FollowHyperlink (HLink)
'Application.Wait (Now + TimeValue("0:00:03"))
'Application.SendKeys "%s"
End Sub

Whilst you may get help here, this question clearly has nothing to do with
worksheet functions. As it is concerned with programming, you would do
better to post it in the Excel programming newsgroup.