GarnesGambit
New Member
- Joined
- Feb 23, 2024
- Messages
- 15
- Office Version
- 365
- Platform
- Windows
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 activate.
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.
What would I change in the below code to set this?
Thanks in advance!
My code
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 activate.
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.
What would I change in the below code to set this?
Thanks in advance!
My code
VBA Code:
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
