Improvedfly
New Member
- Joined
- Dec 13, 2017
- Messages
- 1
Hello All,
Please let me start by saying that this is my first post after years of using these forums to learn excel when I only knew "Sum(A1:B2)". Thank you!
I have spent a couple of days going through the best way to code something I have made to make my life easier at work.
Essentially what I want to do is press a button and generate two mails: one for the customer and one for the supplier.
How I have designed the structure of things is through mainly using vlook-up (Application.WorksheetFunction.Vlookup ...).
Email 1: Customer
Table A4:C6 contains the choice number, customer name and the price. In cell H4 (in yellow) I will put my choice number and in doing so it will store the name of the customer in column "B". For example I put 2 in H4 and the code will know that I have selected "Customer 2". In a sheet named "Contacts" I also have a table A1:B10 with customer names and an e-mail address to each customer. So whatever I choose in H4, the program will vlookup the customer name in the table A4:C6, find out what supplier name I am talking about and generate an outlook mail with email taken from Worksheets."Contacts".Range("A1:B10"). "To: Customer_2@vba.com".
In the body of the text I want to copy a table (say A16:E24) and paste it after my text in the body of the mail.
Email 2: Supplier
Table A4:D6 will contain the supplier column this time and when requesting the 1st choice in cell I4, the program will do the same thing. It will store the fact that I have chosen "Supplier 2" , vlookup his name in the sheet called "Contacts" range("A1:B10") and generate an e-mail "To: Supplier_2@vba.com".
Subject of both e-mails:
The subject will be "Order" & cell "B10" & "for delivery date" & "B9".
I have attached an image of the main table, perhaps it would be useful to visualize.
Thank you immensely for the help guys.
Flickr photo: https://www.flickr.com/photos/155082293@N06/24173381987/in/dateposted-public/
Please let me start by saying that this is my first post after years of using these forums to learn excel when I only knew "Sum(A1:B2)". Thank you!
I have spent a couple of days going through the best way to code something I have made to make my life easier at work.
Essentially what I want to do is press a button and generate two mails: one for the customer and one for the supplier.
How I have designed the structure of things is through mainly using vlook-up (Application.WorksheetFunction.Vlookup ...).
Email 1: Customer
Table A4:C6 contains the choice number, customer name and the price. In cell H4 (in yellow) I will put my choice number and in doing so it will store the name of the customer in column "B". For example I put 2 in H4 and the code will know that I have selected "Customer 2". In a sheet named "Contacts" I also have a table A1:B10 with customer names and an e-mail address to each customer. So whatever I choose in H4, the program will vlookup the customer name in the table A4:C6, find out what supplier name I am talking about and generate an outlook mail with email taken from Worksheets."Contacts".Range("A1:B10"). "To: Customer_2@vba.com".
In the body of the text I want to copy a table (say A16:E24) and paste it after my text in the body of the mail.
Email 2: Supplier
Table A4:D6 will contain the supplier column this time and when requesting the 1st choice in cell I4, the program will do the same thing. It will store the fact that I have chosen "Supplier 2" , vlookup his name in the sheet called "Contacts" range("A1:B10") and generate an e-mail "To: Supplier_2@vba.com".
Subject of both e-mails:
The subject will be "Order" & cell "B10" & "for delivery date" & "B9".
I have attached an image of the main table, perhaps it would be useful to visualize.
Thank you immensely for the help guys.
Flickr photo: https://www.flickr.com/photos/155082293@N06/24173381987/in/dateposted-public/
