Single Active CTRL Button to send email basedon Row

[VBANEWBIE1229]

Hi guys,

I am trying to send an email from excel 2013 based on the data in the spreadsheet. I currently have an active button on each row with a macro assigned. This works perfectly, however, I would like to be able to only have one button per sheet that will create the email based on the active selected row. The current code I'm using is below. Thanks in advance for your help!!!!!

Private Sub CommandButton21_Click()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Range("E9")
.CC = Range("F9")
.BCC = ""
.Subject = "SUBJECT TEXT"
.Body = "Hello, " & vbNewLine & vbNewLine & "EMAIL TEXT"
.Attachments.Add (ActiveSheet.Range("C9").Value)
.Display
MsgBox ("Email Created")
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Kenneth Hobson]

Please click the # icon on the toolbar and paste code between tags.

Here is an example:

'.Attachments.Add (ActiveSheet.Range("C9").Value)
.Attachments.Add .cells(activecell.Row, "C").value

 

[VBANEWBIE1229]

Thank you. This didn't work for me. What I am actually trying to do is have one button that will pull from 3 cells depending on which row is selected. My data is in colums C, E, & F. So if I have row 6 selected, I would like it to get the values from cells C6, E6 &F6.

 

[Kenneth Hobson]

Did you not get that ActiveCell.Row returns the active cell's row value? I left it to you to change the others. Do you understand now?

 

[VBANEWBIE1229]

Thank you for your posts. VBA is very new for me, I understand...this is what I have so far for the rows that I need values.

.To = .Cells(ActiveCell.Row, "E")
.CC = .Cells(ActiveCell.Row, "F")
.Attachments.Add .Cells(.ActiveCell.Row, "C").Value

 

[Kenneth Hobson]

Does that solve your issue?

Private Sub CommandButton21_Click()
  Dim OutApp As Object, OutMail As Object, r As Long
  
  r = ActiveCell.Row
  
  Set OutApp = CreateObject("Outlook.Application")
  OutApp.Session.Logon
  Set OutMail = OutApp.CreateItem(0)
  On Error Resume Next
  
  With OutMail
    .To = Cells(r, "E").Value2 'Range("E" & r)
    .CC = Cells(r, "F").Value2 'Range("F9")
    .BCC = ""
    .Subject = "SUBJECT TEXT"
    .Body = "Hello, " & vbNewLine & vbNewLine & "EMAIL TEXT"
    .Attachments.Add Cells(r, "C").Value2 '(ActiveSheet.Range("C9").Value)
    .Display
    MsgBox ("Email Created")
  End With
  
  On Error GoTo 0
  Set OutMail = Nothing
  Set OutApp = Nothing
End Sub

FWIW: Cells() does not enable intellisense but does allow either letter string or column number for the 2nd parameter, column. Range() has intellisense but because we have to use string concatenation for this case, it is a bit slower to execute if you do alot of those.