L
Legacy 316613
Guest
Hi All,
Firstly, I am very new to VBA but understand its power and the good work you all do to make life easier for novices like me.
Here is the synopsis of a project I have been tasked with which will help me tremendously in my job.
I have an Excel worksheet which captures all orders pending to deliver to customers for which we are awaiting goods from the suppliers. It is known as a Back Order report. The template of the Excel worksheet is as shown below.
As you will note this is a complex project and hence I have broken it down into 3 sections
Section 1 - Capturing Data from Excel into PDF and emailing via Outlook
Row 1 of Headers from Column A to Column J
Customer Account - Email Address - Order No - Product - Product Description - Supplier Name - Qty Ordered - Original Due Date - Expected Delivery Date - Reason for Delay
Rows 2 to Last Used Row contains details of orders placed by customers - there may be duplicated Customer Account References in Column A, and there may be duplicated Products ordered in Columns D to G however the Order No in Column C will be unique as customer may have placed two different orders for the same product on different dates.
What I am trying to achieve is a macro which will do the following:
1) Loop through the excel worksheet and capture all data for a customer account
2) Transfer this data into a PDF
3) Email the PDF to the customer using their email address in Column B
4) Loop through the entire excel worksheet repeating Steps 1 to 3
Section 2 - Ensuring Data Capture is a Dynamic Process
Furthermore I need another macro to Copy all the Data in Sheet 1 of the Workbook into Sheet 2 of the Workbook. I would then like to have an UPDATE command button that each time I click it, the macro checks to see if there is any variance (i.e. new updates in the data) in Worksheet 1 against the Data in Worksheet 2 and if so repeat process Steps 1 to 4 as stated above. Then delete Data in Worksheet 2 and overwrite it with Data in Worksheet 1.
The objective of this exercise is to have VBA automate the process of sending back order reports to the customers via email and to be dynamic in the sense that upon any updates in expected delivery dates re-send an updated report to the customer.
Section 3 - Template of PDF document to be emailed to customers
Lastly, when the data is captured using Steps 1-3 above, the PDF must also include as standard our company logo in the header (top right hand of page), a Title "Back Order Report" in the Centre and a Disclaimer in the footer which will be standard text as per our terms and conditions which I am unable to detail here in open forum. However for ready reference it will be free form text which is not dynamic. For example "Quantities once delivered cannot be returned. If you however want to cancel your order, please .......... etc."
I appreciate this is a big request but any assistance even if it be for part of the project will be most appreciated.
Thank you in advance.
Firstly, I am very new to VBA but understand its power and the good work you all do to make life easier for novices like me.
Here is the synopsis of a project I have been tasked with which will help me tremendously in my job.
I have an Excel worksheet which captures all orders pending to deliver to customers for which we are awaiting goods from the suppliers. It is known as a Back Order report. The template of the Excel worksheet is as shown below.
As you will note this is a complex project and hence I have broken it down into 3 sections
Section 1 - Capturing Data from Excel into PDF and emailing via Outlook
Row 1 of Headers from Column A to Column J
Customer Account - Email Address - Order No - Product - Product Description - Supplier Name - Qty Ordered - Original Due Date - Expected Delivery Date - Reason for Delay
Rows 2 to Last Used Row contains details of orders placed by customers - there may be duplicated Customer Account References in Column A, and there may be duplicated Products ordered in Columns D to G however the Order No in Column C will be unique as customer may have placed two different orders for the same product on different dates.
What I am trying to achieve is a macro which will do the following:
1) Loop through the excel worksheet and capture all data for a customer account
2) Transfer this data into a PDF
3) Email the PDF to the customer using their email address in Column B
4) Loop through the entire excel worksheet repeating Steps 1 to 3
Section 2 - Ensuring Data Capture is a Dynamic Process
Furthermore I need another macro to Copy all the Data in Sheet 1 of the Workbook into Sheet 2 of the Workbook. I would then like to have an UPDATE command button that each time I click it, the macro checks to see if there is any variance (i.e. new updates in the data) in Worksheet 1 against the Data in Worksheet 2 and if so repeat process Steps 1 to 4 as stated above. Then delete Data in Worksheet 2 and overwrite it with Data in Worksheet 1.
The objective of this exercise is to have VBA automate the process of sending back order reports to the customers via email and to be dynamic in the sense that upon any updates in expected delivery dates re-send an updated report to the customer.
Section 3 - Template of PDF document to be emailed to customers
Lastly, when the data is captured using Steps 1-3 above, the PDF must also include as standard our company logo in the header (top right hand of page), a Title "Back Order Report" in the Centre and a Disclaimer in the footer which will be standard text as per our terms and conditions which I am unable to detail here in open forum. However for ready reference it will be free form text which is not dynamic. For example "Quantities once delivered cannot be returned. If you however want to cancel your order, please .......... etc."
I appreciate this is a big request but any assistance even if it be for part of the project will be most appreciated.
Thank you in advance.
