Need an Excel Macro to Forward Email to Certain Recipients Based on Content

**bosoxfan82** · 11-28-2017, 05:14 PM

Hi there!

I'm looking for help with an excel macro that I can throw in a button, that will forward the email I have either highlighted in Outlook or have opened in a separate window (whichever is easier) to certain email addresses based on contents in the email.

So here's the situation, I regularly receive emails regarding escalations that need to be forwarded to certain sites based on information included in the email, an example of what that would like in the email is:

Site: Call Center A
CSR: Agent Rep A

Site: Call Center B
CSR: Agent Rep A

Site: Call Center C
CSR: Agent Rep A

Now there can be several sites included in each email, with that said there is a list of email addresses for each site, so for instance because "Call Center A" is listed the email would need to go to 4 different email addresses and because "Call Center B" is listed it would need to go to six others.

What I need is something that will automatically scan the email for the sites that the email needs to be forwarded to and then of course...forward the email.

The sites in each email can be different each time and there are multiple recipients for each site, hence the macro to make things easier for each site.

I'm using Office 2016...thanks in advance for your help!

**dflak** · 11-28-2017, 06:21 PM

I assume you want to scan the body of the email for site names. You will need a table with the site names and the list of addresses for each site. One record per row. If you could post a sample email and dummy up the data so it is not sensitive, it will give us an idea of an email might look like and if there are any "gotchas" we'd have to look out for in the parsing. I think this is do-able.

**bosoxfan82** · 11-28-2017, 07:13 PM

Originally Posted by dflak

I assume you want to scan the body of the email for site names. You will need a table with the site names and the list of addresses for each site. One record per row. If you could post a sample email and dummy up the data so it is not sensitive, it will give us an idea of an email might look like and if there are any "gotchas" we'd have to look out for in the parsing. I think this is do-able.

Sorry I'm kinda new here and don't know how to include a table, here is an image of what it would look like.Table.PNG

**dflak** · 11-30-2017, 04:00 PM

Here is what I managed to work out from going out to the web and bolting and sewing pieces of code together.

On the Control Panel Sheet, set up the email addresses in the table in Columns A and B: one email per row. Since this is an Excel Table, the code knows how many rows it has, and you don't have to change anything if you add or delete data. To delete a row, right click on it and select Delete->Table Row. If you see Delete->Entire Row, it means that the data are filtered and you may no delete all the lines you want.

Column E is a pivot table that gets the unique names for the Call Center from the table. If you change the table information (add or delete a Call Center), refresh the pivot table. There is room for error here with partial matches. In your example you show Call Centers 1 though 10. If I am looking to see if Call Center 1 is in the body, it will also "hit" on Call Center 10. So if you have something like St. Louis and East St. Louis, you might think about this.

Select an email message and click on the Forward Mail button. If you do not select an email, or if the program cannot resolve an email address, you will get a warning message.

The program works by parsing for the key words in the body of the document.