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

 

[rcbunting]

This is the command for your loop:

For Each r In Selection.Rows()
.
.
.
Next r

The code you're using above looks fine except that it's missing a 'Next r' right now. If you use the loop that I've just described, keep in mind that all your references to row 'r' will have to be updated to r.row()

GL

 

[ckporte]

Thank you! I'm a little closer although whatever I did made my lotus notes bomb and i had to reboot. Please see the updated code.

Also, The email address and the information in the row didn't populate into my lotus notes.

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 Each r In Range("F8:F400").Rows
' Get the email address
Email = Cells(r.Row(), 9)

' Message subject
Subj = "Upcoming Auditor Request item(s)/due dates. Please review."

' Compose the message
Msg = ""
Msg = Msg & "The following item(s) have upcoming due dates." & vbCrLf & vbCrLf
Msg = Msg & Cells(r.Row(), 1) & vbCrLf & Cells(r.Row(), 2) & vbCrLf & Cells(r.Row(), 3) & vbCrLf & _
Cells(r.Row(), 4) & vbCrLf & Cells(r.Row(), 5) & vbCrLf & vbCrLf & Cells(r.Row(), 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

Next r

End Sub

 

[rcbunting]

It looks like your code will generate like 390 email items. I'm not sure about the behavior of lotus notes, but excel doesn't seem to send them off straight away; the email items are created and wait for you to click 'send' on each one. All those emails open at once would probably make the application bomb. Try the execution once with just one row? That will give you an idea if it's an issue with the code or the number of emails your sending.

I misunderstood your original post -- i thought that you wanted to select certain rows out of the data set and just send those. That was the reason for the whole 'for each row in the selection' re-write. If the new syntax is breaking something that was working before, you may want to roll back to the loop as it was before.

Although it usually does work fine without it, i'm a bit of stickler on including ".Value" on my Cells(x,y).Value references. Might be worth trying.

 

[ckporte]

Hi
No.. I think I am misunderstanding how it works. You were right.

I'm not sure why I wrote 8-400.. I thought I had to give it a parameter.

Ultimately, I was hoping there was a way that I could just highlight the row (or rows) that the macro would use to grab the information from once I clicked the radio button.

Am I making this more confusing ?
Cheryl

 

[ckporte]

Is there a code I could use to replace this line ..

For Each r In Range("F8:F400").Rows

that says..

For each row that is highlighted perform this action?

 

[ckporte]

Hi Mr. Excel.
I feel like I am making this more confusing that should be. Originally I found the following code on your site (see below) and it works great! so I thought I could apply it to my circumstances.... with a few tweaks.

1. For the email: I was hoping that instead of a value in the macro, I could set it up to grab column 9's info from the row that was currently selected.

2. For the "Msg = Msg & Cells" line below, I was trying to change it from a value to variable info again. I was trying to figure out how to grab info from columns 1-6 in the row that was currently selected.

_____________

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

Email = "me@2you.co.uk"

' Message subject
Subj = "The following PwC Auditor Request item(s) are due soon. Please review."
' Compose the message
Msg = ""
Msg = Msg & Cells(1, 1) & vbCrLf & Cells(1, 2) & vbCrLf & Cells(1, 3) & vbCrLf & _
Cells(1, 4) & vbCrLf & Cells(1, 5) & 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

 

[rcbunting]

Is there a code I could use to replace this line ..

For Each r In Range("F8:F400").Rows

that says..

For each row that is highlighted perform this action?

ckPorte, the loop syntax that I gave you above does what you're asking.

For Each r In Selection.Rows()

'
'  (your code here)
'
Next r

This means 'do (your code here) for every row in the selection'

when I replace your code with the for condition above, it works very well (although my email client is outlook and not lotus notes)

 

[ckporte]

Hi Mr. Excel,
I think I got it to work...I really need to find an online class on coding, any ideas?

But.. I can't get my email to work. This is the code.. can you tell me why its not working?

' Get the email address
Email = Cells(r.Row(), 9)

 

[ckporte]

I got it!
For some reason I had to write
Email = Mgs & Cells(r.Row(), 9)

I just had
Email = Cells(r.Row(),9)

Thank you for all your help!!
ckporte