Dynamic Button for Email Macro Based on Cell You Click

**GarnesGambit** · 02-23-2024, 05:58 AM

Hi all,

I have created a macro that generates emails with attachments. It works wonderfully attaching it to a button. BUT, I have about 300 rows in my original workbook, and initially I thought I would have to set 300 buttons per row and attach 300 different macros to them to active.

What I'm wanting to do, is have one button, that dynamically selects the information for the cell that I am in. So if I click the button then click H6, it selects the info for my .to, .cc etc from row 6. Or backward clicking into the cell first then pressing the button perhaps?

So, my code is as below you'll see I have the email set to take the .to from A6, .cc from C6, part of the .subject from G6 and the .attachments name from H6. This would then change to A7, C7, G7, H7 etc etc. Each row is different depending on whether there is a 1 or blank in P:BL in my workbook.

I have attached an example workbook.

Sub send_EMAIL()


Dim OutApp As Object
Dim Outmail As Object
Dim Strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set Outmail = OutApp.CreateItem(0)

Strbody = "<BODY style = font-size:12pt; font-familt:Arial>" & _
"Hi all, <br><br> blah blah blah this is an example.<br><br>" & _
"also an example<br><br>" & _
"Still an example<br><br>" & _
"Thanks, <br> Joe Bloggs"


On Error Resume Next
With Outmail
.display
.to = Sheets("Sheet1").Range("A6").Value
.CC = Sheets("Sheet1").Range("c6").Value
.Bcc = ""
.Subject = "Planogram Update - " & Sheets("Sheet1").Range("g6").Value & " - " & Format(Date, "dd/mm/yy")
.HTMLBody = Strbody & .HTMLBody
.Attachments.Add "H:\Example\Example\Example\Example\" & Sheets("All Plans").Range("h6").Value & ".pdf"

End With
On Error GoTo 0

Set Outmail = Nothing

End Sub

Thanks in advance wonderful people!

**GarnesGambit** · 02-23-2024, 07:28 AM

Okay, I have actually solved this myself!

Where the code has:
.Range("A6").Value

Replace with
.Cells(ActiveCell.Row, "A")

This then means when I click into a cell (for the row which has my unique info) and then click the button, it generates me an email!

Dynamic Button for Email Macro Based on Cell You Click

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Dynamic Button for Email Macro Based on Cell You Click

Re: Dynamic Button for Email Macro Based on Cell You Click

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Dynamic option button click?

[SOLVED] Macro to Double Click on Cell in Col E to create Email to attach file

[SOLVED] USERFORM to BITMAP/ or pdf -- to email with a click of a button?

[SOLVED] Dynamic button on click event

[SOLVED] Macro to click on button and copy cell context into notepad...?

Click Command Button, run macros based on cell value

macro help: click button to update value of primary cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions