Help very challenging, convert order to PDF and email from multiple sheets

[englandmark]

This seems to be very challenging for me, but i will thank you in advance excel Guru's.
I have several sheets used for ordering from different divisions and also one for outside sales.
I would like to generate an invoice from any of the worksheets and send a PDF invoice to my coordinator for sending to the customer.
Thanks again for any help in advance guys and gals.

Goal
To create invoice from several order sheets and email when cell specifies yes to specific employee (Yes is selected Y in last column named Invoice)

Order sheets (Sheets named RH Commercial, RH Service, RH Residential, Aug Commercial, Aug Service, Outside Sales)
[TABLE="width: 0"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[/TR]
[TR]
[TD]Invoice #
[/TD]
[TD]Delivery
[/TD]
[TD]Customers Name
[/TD]
[TD]Address
[/TD]
[TD]City
[/TD]
[TD]State
[/TD]
[TD]Phone number
[/TD]
[TD]Date Ordered
[/TD]
[TD]Date fabricated
[/TD]
[TD]Job number
[/TD]
[TD]Job name
[/TD]
[TD]Description
[/TD]
[TD]Gauge
[/TD]
[TD]Quantity L/f
[/TD]
[TD]Girth
[/TD]
[TD]Sheets
[/TD]
[TD]Sheet cost
[/TD]
[TD]Material cost
[/TD]
[TD]Fab cost
[/TD]
[TD]Sell price
[/TD]
[TD]L/F Cost
[/TD]
[TD]RPI Price
[/TD]
[TD]RPI L/F or Each
[/TD]
[TD]RPI Discount
[/TD]
[TD]Invoice
[/TD]
[/TR]
</tbody>[/TABLE]

Invoice (Sheet name invoice)
Invoice Number: D6 copy from cell A
Delivery: D29 copy from cell B
Customers name: B8 copy from cell C
Job Name: B9 copy from cell K
Job Number: D9 copy from cell J
Billing Address: B10 copy from cell D
City: B11 copy from cell E
State: B12 copy from cell F
ZIP: B13 auto generate from address
Quantity: A16 copy from cell N
Description: B16 copy from cell L
Unit Price: C16 copy from cell W, Outside Sales sheet copy U
Total: D16 copy from cell V, Outside Sales sheet copy T

If same customer same address same date fabricated and yes selected add below A16, B16, C16, D16 and below on same invoice
(Multiple products same order)

Invoice range A1:D33
Create PDF attachment to email and send to soandso@gmail.com