In excel, highlight row and click "new email"

[ckporte]

HI!
I work on request lists that require me to send an email when an item has not met its due date.

The email address(es) I'd use are listed under "Company Contact" column. I would like to a highlight specific row(s) (example # 284 below) and click a "send email" button to open up a new email in lotus notes. I'd also like to include a generic subject line and a copy/picture of the contents in the row that I have highlighted.
<TABLE style="WIDTH: 1325pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1768 border=0><COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 4437" width=104><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2944" width=69><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3712" width=87><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 4309" width=101><COL style="WIDTH: 261pt; mso-width-source: userset; mso-width-alt: 14848" width=348><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 4053" width=95><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3712" width=87><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 3029" width=71><COL style="WIDTH: 155pt; mso-width-source: userset; mso-width-alt: 8832" width=207><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 7296" width=171><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 8917; mso-outline-level: 1" width=209><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 9344; mso-outline-level: 1" width=219><TBODY><TR style="HEIGHT: 67.5pt; mso-height-source: userset" height=90><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 67.5pt; BACKGROUND-COLOR: #ffcc00" width=104 height=90>Company</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=69>Request #</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=87>Audit Team</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=101>Audit Area</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 261pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=348>Item Needed / Description</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=95>Target Date</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=87>Date Received</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=71>Hard copy or direct deliver</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 155pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=207>Company Contact </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=171>Notes / Comments</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 157pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=209>Account Owner </TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 164pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=219>Audit Contacts</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 1325pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1768 border=0><COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 4437" width=104><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2944" width=69><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3712" width=87><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 4309" width=101><COL style="WIDTH: 261pt; mso-width-source: userset; mso-width-alt: 14848" width=348><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 4053" width=95><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3712" width=87><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 3029" width=71><COL style="WIDTH: 155pt; mso-width-source: userset; mso-width-alt: 8832" width=207><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 7296" width=171><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 8917; mso-outline-level: 1" width=209><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 9344; mso-outline-level: 1" width=219><TBODY><TR style="HEIGHT: 30pt" height=40><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 78pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: white" width=104 height=40>Company</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=69>284</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=87>Insurance</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=101>Policy Loans</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 261pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=348>Description </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 71pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=95>09/21/2011</TD><TD class=xl78 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 11pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; COLOR: #0d0d0d; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: 'Arial Rounded MT Bold'; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=87> </TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=71> </TD><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #a5a5a5 0.5pt solid; WIDTH: 155pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=207>Jim.Jim@????.com Dave.Dave@????.com</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=171> </TD><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #a5a5a5 0.5pt solid; WIDTH: 157pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=209>Jim.Jim@????.com Dave.Dave@????.com</TD><TD class=xl69 style="BORDER-RIGHT: #a5a5a5 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #a5a5a5; WIDTH: 164pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=219>Jim.Jim@????.com Dave.Dave@????.com</TD></TR></TBODY></TABLE>

Could you help me with what I've started below. I continue to get errors and I am a newbie at creating macros so I'm sure I'm messing it up.

Thanks!!!
Cheryl
__________________

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
For r = 8 To 50 'data in rows 8-50
' Get the email address
Email = Cells(r, 9)
' Message subject
Subj = "Upcoming Auditor Request item(s)/due date(s). Please review."
' Compose the message
Msg = ""
Msg = Msg & Cells(r, 9) & "," & vbCrLf & vbCrLf
Msg = Msg & "The following item(s) have upcoming due dates." & vbCrLf & vbCrLf
Msg = Msg & Cells(r, 1) & vbCrLf & Cells(r, 2) & vbCrLf & Cells(r, 3) & vbCrLf & _
Cells(r, 4) & vbCrLf & Cells(r, 5) & vbCrLf & vbCrLf & Cells(r, 6) & 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
End Sub

 

[ckporte]

One last question.
Below is the final coding. This works!! except not all the time. ugh

When I test it to try to figure out why it doesn't work for every row..all I can come up with is 2 separate reasons but neither works even alone.

1. BODY ISSUE: Too much text in a cell? In my file the 5th column of the row is a description field..and sometimes it has 1 or 2 complete lines.

2. EMAIL ISSUE: Sometimes the email contains 2 emails:
1stname.name@company.com, 2ndname.name@company.com

When it doesn't work I receive this message:
Error Processing Command Line Arguments.


___________________
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
For Each r In Selection.Rows()
Email = Msg & Cells(r.Row(), 9)
' Message subject
Subj = "The following auditor request item(s) are due. Please review."
' Compose the message
Msg = Msg & Cells(r.Row(), 2) & vbCrLf & Cells(r.Row(), 1) & vbCrLf & Cells(r.Row(), 5) & vbCrLf & _
Cells(r.Row(), 6) & vbCrLf & Cells(r.Row(), 9) & 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