Excel 2010 - Use a Button on each row to send e-mail with string of data from row - help wanted!

[truchiller]

Team,

I am using excel as a checkbook and would like to be able to

• Create a function e-mail button - specific to each row line item
• Assign an e-mail action to the button when clicked
• Once the button is clicked - it pastes the word "Closed" into a specific cell P7:P375 - pending which row is active
• The e-mail would format the following:
  • Open Outlook
  • Autofill To: address@address.com -
  • Autofill Cc: address@address.com
  • Subject: "Action to Close Purchase Order"
  • Body: "Team, please close and pay the following purchase order:" "Stringdata" (Information comes from L7:L375 & M7:M375 pending which row is active for sending e-mail) - so the button references only the data in that line in which the button is sitting on) "Thank you"
• For example: I want the following to happen
  • Click Close PO Button on Row 7
  • P7 Status now reads "Closed"
  • E-mail Opens up
  • To: Address@address.com
  • Cc: Address@address.com
  • Subject: Action to Close Purchase Order
  • Body: "Team, please close and pay the following purchase order: PO#LLF20154096 - Louise Lumber." "Thank you."
  • Click Send E-mail
  • Now "Close PO" button grays out Q7
• And do a separate e-mail for each line item - not always in order either - so row 7, then row 9 (3) days later
• I can create a new sheet as well for e-mail addresses as these may change and need to be updated.

I have only found VB Scripting for sending whole page or workbook and not data strings specific. Any help is much appreciated -I am beyond my excel limitations now.

K	L	M	N	O	P	Q
Req.#	PO#	Vendor ID	Reqr.	Date	Status	Close PO?
F2015709	PO# LLF20154096	Louies Lumber	J. Doe	1/1/2015		-- removed inline image --- <tbody> </tbody>	Row 7
F2015713	PO# LLF20155017	Joes Paint	J. Doe	1/1/2015		-- removed inline image --- <tbody> </tbody>	Row 8
F2015716	PO# LLF20155111	Marks Liquids	J. Doe	1/1/2015		-- removed inline image --- <tbody> </tbody>	Row 9
F2015721	PO# LLF20155115	Sams Carpet	J. Doe	1/1/2015		-- removed inline image --- <tbody> </tbody>	Row 10

<tbody>
</tbody>

- JP

 

[CalcSux78]

I would reconsider adding the button per row (for over 350 rows) with specific code for each button. The filesize and code required would be way larger than needed.

Instead write code to this effect on a module

Sub SendEmail(Row As Long)
Dim OutApp As Object
Dim OutMail As Object
Dim msg As String
On Error GoTo cleanup
msg = "Team, please close and pay the following purchase order: " & _
    Range("K" & Row) & " - " & Range("M" & Row) & ".  Thank you."
Set OutApp = CreateObject("Outlook.Application")
With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With
Set OutMail = OutApp.createitem(0)
With OutMail
    .to = "[EMAIL="Address@Address.com"]Address@Address.com[/EMAIL]"
    .cc = "[EMAIL="Address2@Address.com"]Address2@Address.com[/EMAIL]"
    .Subject = "Action to Close Purchase Order"
    .Body = msg
    .Display  'Or use Send
End With
Range("P" & Row).Value = "Closed"
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
With Application
    .EnableEvents = True
    .ScreenUpdating = True
End With
End Sub

Then you will need something to trigger the macro to run. I recommend a double-click event in column P. Under Excel Objects and the Sheet you are wanting to watch changes, insert something like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Targ As Range
Set Targ = Intersect(Target, Range("P7:P375"))
'Doubleclick in column P to trigger macro
If Not Targ Is Nothing Then
    SendEmail Target.Row
    Cancel = True       'undo the doubleclick effect if macro triggered
End If
End Sub

for more: www.rondebruin.nl/win/s1/outlook/mail.htm