Code help for auto invoice from sales sheet when criteria met

[englandmark]

Hi all, would appreciate a little help here.
I have a sales sheet that once the completion date is entered i would like certain info automated to an invoice form on another worksheet same workbook and automated to send by email.
Cell F needs to be the trigger as it will not always be the last row entered.

If
Worksheet Sales, cell F has date entered
Copy and paste to Worksheet Invoice,
Cell A = Worksheet Sales cell A
Cell B = Worksheet Sales cell B
Cell C = Worksheet Sales cell G
Cell D = Worksheet Sales cell H
Cell E = Worksheet Sales cell I
Cell F = Worksheet Sales cell K
Cell G = Worksheet Sales cell V
Cell H = Worksheet Sales cell W
Then
Worksheet Invoice
Send to email address

 

[DanteAmor]

Put the following code in the events of your sales sheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F:F")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Target.Value = "" Then Exit Sub
        If IsDate(Target.Value) Then
            Application.ScreenUpdating = False
            Application.DisplayAlerts = False
            fila = Target.Row
            Set h1 = Sheets("sales")
            Set h2 = Sheets("invoice")
            '
            h2.Range("A2").Value = h1.Range("A" & fila).Value
            h2.Range("B2").Value = h1.Range("B" & fila).Value
            h2.Range("G2").Value = h1.Range("C" & fila).Value
            h2.Range("H2").Value = h1.Range("D" & fila).Value
            h2.Range("I2").Value = h1.Range("E" & fila).Value
            h2.Range("K2").Value = h1.Range("F" & fila).Value
            h2.Range("V2").Value = h1.Range("G" & fila).Value
            h2.Range("W2").Value = h1.Range("H" & fila).Value
            h2.Copy
            Set l2 = ActiveWorkbook
            arch = ThisWorkbook.Path & "\" & "invoice" & ".xlsx"
            l2.SaveAs Filename:=arch, _
                FileFormat:=xlOpenXMLWorkbook
            l2.Close False
            '
            Set dam = CreateObject("Outlook.Application").CreateItem(0)
            dam.To = "name_mail@gmail.com"
            dam.Subject = "Invoice"
            dam.Body = "Send file with invoice"
            dam.Attachments.Add arch
            'dam.Send                                'send mail
            dam.Display                             'show mail
            MsgBox "Email sent"
        End If
    End If
End Sub

The invoice sheet will be sent as a file in outlook mail.
Change in the code A2, B2, G2, H2.. for the real celll.

Let me know if you have any questions.

 

[englandmark]

Thanks Dante

Only issue is it opens to send doesn't just send

i have more than on order for the same job but there on separate rows and i need them to be on one invoice instead of several invoices for the same job

Code needs to:

Worksheet invoice to reset blank and add next number in cell E10
If Worksheet sales column H is same Job name
and column F Same completed date
Then
Worksheet sales copy and paste column K,I,R,
To
worksheet invoice next row column B,C,D
I'm thinking Worksheet sales Needs to have option to add these before sent so as one invoice (maybe a pop up that says add or send or it adds and only sends when it opens if send is hit)

Hope you can help

 

[DanteAmor]

Thanks Dante

Only issue is it opens to send doesn't just send

i have more than on order for the same job but there on separate rows and i need them to be on one invoice instead of several invoices for the same job

change this

            'dam.Send                                'send mail
            dam.Display                             'show mail

for this

            dam.Send                                'send mail
            'dam.Display                             'show mail

---

Thanks Dante

i have more than on order for the same job but there on separate rows and i need them to be on one invoice instead of several invoices for the same job


Code needs to:


Worksheet invoice to reset blank and add next number in cell E10
If Worksheet sales column H is same Job name
and column F Same completed date
Then
Worksheet sales copy and paste column K,I,R,
To
worksheet invoice next row column B,C,D
I'm thinking Worksheet sales Needs to have option to add these before sent so as one invoice (maybe a pop up that says add or send or it adds and only sends when it opens if send is hit)

Could you put an example, please?

 

[englandmark]

Heres the link to the work book
I have orders on several rows for the same job that need to be one invoice.
If you cant access the workbook i can email it

https://www.dropbox.com/s/do62zuwd0l09g0q/MPI Inc Sales .xlsm?dl=0

 

[englandmark]

Would also be great if it could automate the date on the invoice

 

[englandmark]

Did you get access to the workbook Dante?

 

[DanteAmor]

I have the file, but I need some time to review it. Give me an opportunity to review it

 

[englandmark]

Certainly, thanks again for your efforts Dante