Hi, thanks for taking the time to read this problem. I'm trying to build a spreadsheet which will be used every time a new contract is booked centrally to email the offices who will receive it, and tell them what they need to know about it. I have one sheet which is just a big email list that will look somewhat like this:</SPAN>
<TBODY>
</TBODY>
Etc, down the page for 100 or so addresses.
Then a second sheet will contain all the data that needs to be sent out, which is already generated in another file. That table, with the top left cell A1 will look like this:</SPAN>
<TBODY>
</TBODY>
This table will extend horizontally for as many offices as will be involved in the contract, but always only 8 cells down.
I've got all this format set up, but what I'm struggling with is the VBA to check which offices are in the contracts table (this will vary hugely by contract) and then send each one an email containing only the two columns of data pertaining to them. (The headings in column A for everyone, and the specific column for the office.)
Complicating things slightly further I have so far been unable to send any emails from excel (in my heavily restricted work computer system) other than by using the "mail envelope" VBA. I believe this is to do with emails being sendable by macro in excel but not through Outlook, however I'm no expert, as you may be able to tell from this problem, so any proposed solution at all would be massively appreciated.</SPAN>
| Office</SPAN> | Manager Email</SPAN> |
| Office 1</SPAN> | Email 1</SPAN> |
| Office 2</SPAN> | Email 2</SPAN> |
| Office 3</SPAN> | Email 3</SPAN> |
<TBODY>
</TBODY>
Etc, down the page for 100 or so addresses.
Then a second sheet will contain all the data that needs to be sent out, which is already generated in another file. That table, with the top left cell A1 will look like this:</SPAN>
| Office</SPAN> | Office 1</SPAN> | Office 2</SPAN> | Office 3</SPAN> |
| Office Area</SPAN> | |||
| Client</SPAN> | |||
| Client Area</SPAN> | |||
| Project Code:</SPAN> | |||
| Date Begun:</SPAN> | |||
| Due Completion:</SPAN> | |||
| Number of Items:</SPAN> |
<TBODY>
</TBODY>
This table will extend horizontally for as many offices as will be involved in the contract, but always only 8 cells down.
I've got all this format set up, but what I'm struggling with is the VBA to check which offices are in the contracts table (this will vary hugely by contract) and then send each one an email containing only the two columns of data pertaining to them. (The headings in column A for everyone, and the specific column for the office.)
Complicating things slightly further I have so far been unable to send any emails from excel (in my heavily restricted work computer system) other than by using the "mail envelope" VBA. I believe this is to do with emails being sendable by macro in excel but not through Outlook, however I'm no expert, as you may be able to tell from this problem, so any proposed solution at all would be massively appreciated.</SPAN>
