Automating Simple Email Forwards using VBA

[HashiramaSenju]

Hey Guys,

I have a really annoying manual task I do everyday for work:

1. Receive an email (always same subject and body format) into a shared mailbox in Outlook.
2. In the body of that email it has a client name (again, always same format).
3. I look up the client against the account manager name in an excel spreadsheet, and manually forward it on.

Currently, I have created a lookup file I Excel (Client, Account Manager, AM Email Address) and populated it with the information.

Now I need to produce some VBA script which will get this running!

N.b. I have never used VBA before but have other language experience

Many thanks all

 

[Dan_W]

Hi. This seems pretty straightforward. If it were me, I would use Outlook VBA rather than Excel VBA, simply because: (1) the bulk of the work seems to be undertaken by Outlook; and (2) the code is for you and it is not something that needs to be ciruclated to other people (meaning that you can circulate VBA code in macros of an excel spreadsheet, whereas there isn't an easy way of circulating code for use in Outlook). Diane Poremsky over at Slipstick answered a simialr issue a few years back.

Step 1 "Receive an email (always same subject and body format) into a shared mailbox in Outlook."

The article at this link sets out how to 'listen' our for any emails that meet certain conditions - if the subject is always the same, I wouldn't too much about the body format. (see also this post).

Step 2 "In the body of that email it has a client name (again, always same format)."

There are a number of ways you could extract the client name - if it's just one of half-dozen possible names, it would just be a matter of cycling through an array of the possible names an testing to see whether one string (the client name) exists within the second string (the text of the body of the email) (link). Alternatively, if the client name sits structurally in the same place (in each of the emails) a simple regular expreession might be the better approach. It is, of course, possible to extract the information through text formatting, but I suspect it's the more cumebrsome of the options availavble.

Step 3 "I look up the client against the account manager name in an excel spreadsheet, and manually forward it on."

Is this the script you say already have? If so, then the auto-forwarding component in that process is dealt with by Diane in her sponse to the OP in the link I put at the top of my response. Is the lookup data in an XLSX document?

Anwyay, hope that helps. Let me know if you have any questions.

 

[HashiramaSenju]

Hey Dan,

Thanks so much for the response and really appreciate the links guiding me to the right places!

To answer your question - my lookup table is in an Excel spreadsheet, has about 700ish clients/rows. I was originally thinking to just run loads of IF (client1), IF(client2) but this would take ages! So my solution would be to try and use a lookup table the script can use, lookup the client in the email against which AM email and then send out.

I will give this a go when I have some time to work on it then come back with any trouble I have, many thanks again for the helpful and constructive response