Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting cell range in an email
This is the code to take a cell range and pastes it in an
email's subject line and body. The flaw is that I will have manually update the cell range to get the last filled row I like to select a wider cell range so the macro would paste the last filled rowin that range. Any suggestionse will be 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pasting cell range in an email
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 " lastRow = Sheets("PIVNUMS").Range("B65536").End(xlup).Row For Each cell In Sheets("PIVNUMS").Range("B" & lastRow & ":AG" & lastRow) 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" wrote in message ... This is the code to take a cell range and pastes it in an email's subject line and body. The flaw is that I will have manually update the cell range to get the last filled row I like to select a wider cell range so the macro would paste the last filled rowin that range. Any suggestionse will be 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting an email into a cell in excel | Excel Discussion (Misc queries) | |||
Help with EMAIL coping and pasting | Excel Discussion (Misc queries) | |||
pasting non-contiguous range of cells to new row, same cell locati | New Users to Excel | |||
pasting a range of info based on the value in a particular cell | Excel Discussion (Misc queries) | |||
Pasting data top 1st empty cell in range | Excel Discussion (Misc queries) |