VBA auto outlook 2016 to send e-mail based off criteria

The attached code is a subroutine that works with Outlook and takes the following arguments
- To string (mandatory) - the list of people in the TO: block of the email
- Subject String (mandatory) - the subject of the email
- Body String (mandatory) - the body of the message
- CC String (optional) - the list of people in the CC: block of the email
- BCC String (optional) - the list of people in the BCC: block of the email
- Attachment Name (optional) - the file to attach to the email

Do what you need to do to calculate these parts and pass them to the subroutine.

Please Login or Register  to view this content.

It doesn't make sense since I don't have a workbook to look at.

I can help you along with that if you include a workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I need confirmation more than clarification. I go down row by row. If anything in column T:Z on that row is the correct RGB color, prepare an email to the address in column L.

I transmit the header row and just that one row with the data.

I might be able to do one better. It depends on your answer. Will there be a lot of the same email address in Column L or is it pretty much one row per customer.

If it’s one row per customer, I’ll just process linearly. On the other hand, if some customers have multiple rows, I can collect them all together and send out one email per customer.

Also, how are you getting this data in? Are you copy and pasting the data in or is this a report you just “get?” The reason I ask is that the code has to “live” somewhere.

If you are copy / pasting the data in, then the code can live in the workbook that the data are pasted to.

If you are getting this as a report, then I can make this a stand-alone report that opens the report file (all it needs to know is what its name is) and process it.

I think I have enough to get started. I'll get to this on Monday.

Here is an intermediate file. There are a couple of thing I still need to tidy up. Some features I put in are that the Shipping and Forecast sheets are now Excel Tables. I added a column (which can be hidden) to determine if there are cells shaded red.

The way the program works is that I concatenate the addresses from both tables and run a pivot table off the concatenated list to get a unique list of addresses.

I took the Ship Table and Forecast Table and built pivot tables off of them. The pivot table does the bulk of the work of grouping the emails so you are not sending the information out piecemeal. I do some checking and I set the pivot table filters to look at the records for one email address at a time where Has Color is TRUE.

Then I copy these onto a report sheet.

Currently this part of the program is "stubbed out" when you run the MakeReport macro, a message box pops up stating that the report will be mailed. You can take a look at what the outgoing attachment will look like.

What is left to do is to copy the report sheet to a separate spreadsheet and to hook up the mail program.

Take a look at what the outgoing attachments will look like and give me feedback. I'll press on with it after I'm sure I'm going in the right direction.

Here is an intermediate file. There are a couple of thing I still need to tidy up. Some features I put in are that the Shipping and Forecast sheets are now Excel Tables. I added a column (which can be hidden) to determine if there are cells shaded red.

The way the program works is that I concatenate the addresses from both tables and run a pivot table off the concatenated list to get a unique list of addresses.

I took the Ship Table and Forecast Table and built pivot tables off of them. The pivot table does the bulk of the work of grouping the emails so you are not sending the information out piecemeal. I do some checking and I set the pivot table filters to look at the records for one email address at a time where Has Color is TRUE.

Then I copy these onto a report sheet.

Currently this part of the program is "stubbed out" when you run the MakeReport macro, a message box pops up stating that the report will be mailed. You can take a look at what the outgoing attachment will look like.

What is left to do is to copy the report sheet to a separate spreadsheet and to hook up the mail program.

Take a look at what the outgoing attachments will look like and give me feedback. I'll press on with it after I'm sure I'm going in the right direction.