0

I am trying to generate an email from a button click and have it load data( as the subject line for the email) from the first cell in the row that the button is located in.

Example: In row 22 I place a button in column X and when it is clicked it creates an email using cell A22 as the subject for my email.

I know how to do this by inputting the subject line data directly into the script but, I want to avoid creating hundreds of scripts for the large amount of rows in my spreadsheet.

Here is the manual entry method I currently have:

Private Sub CommandButton1_Click()

          Dim outobj, mailobj
          Set outobj = CreateObject("Outlook.Application")
          Set mailobj = outobj.CreateItem(0)

            With mailobj
            .To = "xxxxxxxxx@xxxxxxxx.com"
            .Subject = "XXXXXXXXXXXX"
            .Body = "XXXXXXXXXXXXXXXXXXXXXXX"
            .Display
          End With
          'Clear the memory
          Set outobj = Nothing
          Set mailobj = Nothing

End Sub

I just need the subject to populate from column A in the corresponding button row. I think I need to create another object but I am not sure what the correct code would be and I am not sure how to have the script pull it for ".subject =".

Here is basic data of what I have tried:

► ActiveSheet.Buttons(Application.Caller).TopLeftCell

► "Row of pressed button: " & ActiveSheet.ShapesApplication.Caller).TopLeftCell.Row

► Dim SubjectTo As String
    SubjectTo = Cells(XXX,XXX).Value
    .subject = SubjectTo

And quite a few other suggestions around the net.

All have worked in their original scripts but I cannot figure out how to incorporate them into what I am trying to do.

I even tried using a Form Shape instead of an ActiveX Button.

Community
  • 1
  • 1
Paul M
  • 3
  • 2
  • Try modifying [this](http://stackoverflow.com/questions/32996385/vba-for-attaching-specific-files-to-corresponding-recipients/32997350#32997350). – findwindow Oct 12 '15 at 20:54
  • @findwindow - I don't know how relevant that link is. The OP seems to want to have a way to identify `.Cells(22, 1).Value` when the button located on the 22nd row is clicked. –  Oct 12 '15 at 21:45
  • @Jeeped `In row 22 I place a button in column X and when it is clicked it creates an email using cell A22 as the subject` Sounds like OP placed a button at `X22` and when it's clicked, to pull subject from `A22`. – findwindow Oct 12 '15 at 21:48
  • @findwindow - Yes, but the OP probably has buttons in multiple rows. Looking at the examples of non-working attempts, it seems that identifying row 22 (or 23 or 24 for different buttons) is the problem. –  Oct 12 '15 at 21:50
  • Er I guess I don't understand what OP wants to accomplish XD Edit: it will be really contrived to set a button for each subject.... – findwindow Oct 12 '15 at 21:52
  • HEY! OP! CAN YOU PROVIDED SOME CLARIFICATION AS TO WHAT YOU WANT TO DO? –  Oct 12 '15 at 22:01

1 Answers1

0

From your code sample, it seems that you are using an ActiveX style button. I know of no way to determine the row number from an ActiveX button but if you switch to a Form Control type button, retrieving the row is easy.

Sub Button2_Click()
    MsgBox ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
End Sub

Having one button per row for a large number of rows is impractical. You could have one button that relies on either the selected cells or the activecell to identify the row. Another possibility is a Worksheet.BeforeDoubleClick event macro that opens an email whenever one of the rows is double-clicked (thereby having no buttons at all).