emailing cell range

  • Thread starter Thread starter alldreams
  • Start date Start date
A

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
 
alldreams said:
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
Click to expand...

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.
 
Back
Top