Automating Simple Email Forwards using VBA

HashiramaSenju

New Member
Joined
Feb 26, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
709
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 26, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,566
Messages
5,741,864
Members
423,692
Latest member
Bhanu1988

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
Top