VBA to send email Word document automatically based on Excel cell value criteria

L

Legacy 400604

Guest
Hi All,

Very new to VBA and learning on a daily basis albeit very slowly.

However, I have a project which needs to be completed urgently and hence reaching out for help.

Project Details: The business has set up a new warehouse facility. We have 100+ suppliers who currently deliver into 5 different warehouse facilities. Based on the specific suppliers who deliver to a specific warehouse, we need to send them a specific word document informing them of the new delivery point. Not all suppliers deliver to all warehouses and hence the word document needs to be specific to their criteria. This criteria will be determined by a specific cell value in Excel (lets say B1). Column A contacts a list of the Supplier Name(s).

Requirement: Need to create a macro which looks at the cell B1 and based on the value, refer to a separate worksheet which contains the Supplier Name, Supplier Contact, Supplier Email Address and then populate the email message (outlook 2010) with the relevant details along with the specific word document attached in the email.

Email to be sent as follows:

To: Supplier Email Address
Attachment: Specific Word document attachment based on cell value B1 in Excel workbook

Dear xxxx - Supplier Contact

Email body: We would like to inform you of our new warehouse..... (same static message to the supplier)

In a nutshell, seems a difficult project however I am sure with experts such as yourselves, there must be a way to do this using VBA. I have read some of Ron de Bruin's work and that gives me the confidence it is possible. Problem is just dont know how?

Thank you all, for any support and assistance you can provide.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
hi is it a case that the document specified in b1 will be the same for all suppliers e.g. certain suppliers will deliver only to one werehouse or would there be another field in the excel document saying which werehouse to deliver to on this occasion.

Are both excel sheets in the same work book or different work books, ( it would be far easier to do what you want if they are in the same work book)
Paul
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top