Email row of data if criteria selected from drop down box in same row

[englandmark]

Hey guys and gals, hope your all having a great Sunday.
I have a phone call log with entries from column A to N (Sheet name 2018 Call Log)
Column M has the e-mail address i need to send the e-mail to
Column N has a drop down box to select Sent
The data i need to send is Column A to K and send in the body of the email as Text not table
Column A= Date, B= Time, C,D,E,F = Text, G =Phone number, H,I,J,K = Text

My goal here is for my receptionist to enter the data and when she selects sent from the drop down box the information from the customer sends to the appropriate employee automatically.

Hope you all have a great day, thanks in advance for any help.

 

[Kenneth Hobson]

Re: Help, Email row of data if criteria selected from drop down box in same row

I don't know what subject you want nor the format of the body. Concatenate your body string to suit.

Right click your sheet's tab, View Code, and paste. Add reference as commented.

Private Sub Worksheet_Change(ByVal Target As Range)
'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  Dim r As Range, c As Range, NL As String, cr As Long
  
  Set r = Intersect(Target, Range("N2", Range("N" & Rows.Count).End(xlUp)))
  If r Is Nothing Then Exit Sub
  NL = vbCrLf & vbCrLf
  
  Set olApp = New Outlook.Application
  For Each c In r
    If c = "Sent" Then
      cr = c.Row
      Set olMail = olApp.CreateItem(olMailItem)
      With olMail
        .To = Cells(cr, "M")
        .Subject = "FYI"
        .Body = Cells(cr, "A").Text & vbCrLf & Cells(cr, "B").Text & NL & _
          Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose((Range(Cells(cr, "C"), Cells(cr, "K"))))), " ")
        '.Display
        .Send
        Exit Sub
      End With
    End If
  Next c
  
  Set olMail = Nothing
  Set olApp = Nothing
End Sub

 

[englandmark]

Re: Help, Email row of data if criteria selected from drop down box in same row

Thank you Kenneth, great response worked first time.
You have just made my receptionist 's life a hole lot better and saved me a lot of research finding the correct code.
If there is an easy way to send each cell to the next line in email i would really appreciate it, would just make clearer for the recipient.
Currently shows:
[FONT=&quot]December 16, 2018[/FONT]
[FONT=&quot]11:40[/FONT] <o></o>
[FONT=&quot]Mainline Mark Abraham RoofingProfessionals Inc 429 Longwood Drive Richmond Hill 912-756-7663 rpiroof.com RPIEmployee Referral RHCS Richmond Hill Commercial Service Have roof leaksin multiple areas

Would like it to show as:
[/FONT]
[FONT=&quot]
December 16, 2018[/FONT]
[FONT=&quot]11:40

[/FONT] <o></o>
[FONT=&quot]Mainline
Mark Abraham
RoofingProfessionals Inc
429 Longwood Drive Richmond Hill
912-756-7663
rpiroof.com
RPIEmployee Referral
RHCS Richmond Hill Commercial Service
Have roof leaksin multiple areas

Have a great day, you are a superstar![/FONT]<o></o>

<o></o>

 

[Kenneth Hobson]

Re: Help, Email row of data if criteria selected from drop down box in same row

Change the " " to vbcrlf.

 

[englandmark]

Re: Help, Email row of data if criteria selected from drop down box in same row

Thanks Kenneth works perfect, thanks for the help.
I have a more challenging task if your up for it?

 

[englandmark]

Re: Help, Email row of data if criteria selected from drop down box in same row

Kenneth
any way I can have the data from certain columns of a row copied to next blank row certain columns on another worksheet
if column L is Mitch.
thanks in advance
Mark

 

[Kenneth Hobson]

Re: Help, Email row of data if criteria selected from drop down box in same row

You should start another thread and explain in more detail.

 

[englandmark]

Help needed to move data from one sheet to another if criteria met

Help needed,
Goal - Move cells data from one worksheet to next blank row to cells in another worksheet when criteria from first sheet is met.
Entry worksheet named 2018 Call Log, copy data and paste to worksheet Sales Log next blank row
Criteria to be met in worksheet 2018 Call Log column L is Mitch
Capture data from same row from column D and copy and paste to worksheet Sales Log column B
Capture data from same row from column F and copy and paste to worksheet Sales Log column C
Capture data from same row from column G and copy and paste to worksheet Sales Log column D
Capture data from same row from column H and copy and paste to worksheet Sales Log column E
Capture data from same row from column A and copy and paste to worksheet Sales Log column F
Capture data from same row from column I and copy and paste to worksheet Sales Log column G
Capture data from same row from column K and copy and paste to worksheet Sales Log column O
Capture data from same row from column L and copy and paste to worksheet Sales Log column J

Thanks in advance
Mark

 

[englandmark]

Re: Help, Email row of data if criteria selected from drop down box in same row

Kenneth, can you help
i keep getting an error saying missing object library
i add like you said to references and get this error
Name conflicts with existing module, project or object library