Sending email from a selected row with body text from multiple cells

[Excel_Test_21]

Hello,
I need help creating a code to generate an email when I select a specific row.

For example: if my cursor is cell C4, I want specific cells in that row to populate in the email body as a reference and subject line
if I put the mouse cursor in C2 or C3 i would want the results from that row as followed.

subject would be A4 - B4 C4 - D4 - E4
body would be in separate lines

A4
B4 C4
D4
E4
F4
G4

I found a way to create this ribbon button and just need to active it with a code

 

[mumps]

You actually don't need to use a button. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Double click in a cell in column C in the desired row and the email will be created automatically.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = ""
        .Subject = Range("A" & Target.Row) & " - " & Range("B" & Target.Row) & " " & Range("C" & Target.Row) & " - " & Range("D" & Target.Row) & " - " & Range("E" & Target.Row)
        .HTMLBody = Range("A" & Target.Row) & " - " & Range("B" & Target.Row) & " " & Range("C" & Target.Row) & " - " & Range("D" & Target.Row) & " - " & Range("E" & Target.Row)
        .Display
    End With
    Application.ScreenUpdating = False
End Sub

 

[Excel_Test_21]

Hello,
I need help creating a code to generate an email when I select a specific row.

For example: if my cursor is cell C4, I want specific cells in that row to populate in the email body as a reference and subject line
if I put the mouse cursor in C2 or C3 i would want the results from that row as followed.

subject would be A4 - B4 C4 - D4 - E4
body would be in separate lines

A4
B4 C4
D4
E4
F4
G4

View attachment 44375


I found a way to create this ribbon button and just need to active it with a code
View attachment 44376

You actually don't need to use a button. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Double click in a cell in column C in the desired row and the email will be created automatically.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = ""
        .Subject = Range("A" & Target.Row) & " - " & Range("B" & Target.Row) & " " & Range("C" & Target.Row) & " - " & Range("D" & Target.Row) & " - " & Range("E" & Target.Row)
        .HTMLBody = Range("A" & Target.Row) & " - " & Range("B" & Target.Row) & " " & Range("C" & Target.Row) & " - " & Range("D" & Target.Row) & " - " & Range("E" & Target.Row)
        .Display
    End With
    Application.ScreenUpdating = False
End Sub

that worked well,
i am thinking of another idea.
any way to add the column headers and put the information in separate lines?
for example
Customer Name:
Customer Address:
CityStateZip:
Phone Number:

 

[mumps]

Customer Name:
Customer Address:
CityStateZip:
Phone Number:

This information is located in columns D to G. Are you saying that you don't want the data from columns A and B as described in your original post? I assume that you want this in the body of the email. Is this correct? What do you want in the subject line?

 

[Excel_Test_21]

This information is located in columns D to G. Are you saying that you don't want the data from columns A and B as described in your original post? I assume that you want this in the body of the email. Is this correct? What do you want in the subject line?

I like the subject line and body information, it works fine.
I am looking to make the body layout easier to read by adding line separations and use headers as I add more information to my spreadsheet i will add more columns to the email body.

thank you for working on this with me.

 

[mumps]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = ""
        .Subject = Range("A" & Target.Row) & " - " & Range("B" & Target.Row) & " " & Range("C" & Target.Row) & " - " & Range("D" & Target.Row) & " - " & Range("E" & Target.Row)
        .HTMLBody = "Customer Name: " & Range("D" & Target.Row) & "<br>" _
                    & "Customer Address: " & Range("E" & Target.Row) & "<br>" _
                    & "CityStateZip: " & Range("F" & Target.Row) & "<br>" _
                    & "Phone Number: " & Range("G" & Target.Row) & "<br>"
        .Display
    End With
    Application.ScreenUpdating = False
End Sub

 

[Excel_Test_21]

while this is working great, can i set up another column to click on and return a different subject and body range?
now that this works for our internal team, I was thinking of adding the email option when sending information to other external members.

example: keep everything the same for column 3 and make another set of results for Column 5
.To = ""
.Subject = "Status: " & Range("E" & Target.Row) & " - " & Range("D" & Target.Row)
.HTMLBody = "Please let me know the status for: " < br > "" _
& "Customer Name: " & Range("D" & Target.Row) & "<br>" _
& "Customer Address: " & Range("E" & Target.Row) & "<br>"

 

[mumps]

Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    Select Case Target.Column
        Case Is = 3
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = ""
                .Subject = Range("A" & Target.Row) & " - " & Range("B" & Target.Row) & " " & Range("C" & Target.Row) & " - " & Range("D" & Target.Row) & " - " & Range("E" & Target.Row)
                .HTMLBody = "Customer Name: " & Range("D" & Target.Row) & "<br>" _
                    & "Customer Address: " & Range("E" & Target.Row) & "<br>" _
                    & "CityStateZip: " & Range("F" & Target.Row) & "<br>" _
                    & "Phone Number: " & Range("G" & Target.Row)
                .Display
            End With
        Case Is = 5
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = ""
                .Subject = "Status: " & Range("E" & Target.Row) & " - " & Range("D" & Target.Row)
                .HTMLBody = "Please let me know the status for: " & "<br> " _
                    & "Customer Name: " & Range("D" & Target.Row) & "<br>" _
                    & "Customer Address: " & Range("E" & Target.Row)
                .Display
            End With
    End Select
    Application.ScreenUpdating = False
End Sub

 

[Excel_Test_21]

Awesome, I was away most of the week and just got to give it a try. Thank you so much.

 

[mumps]

You are very welcome.