Run Outlook Macro For Received Emails.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

geek123

Thread Starter
Joined
Dec 7, 2011
Messages
44
Hello,

I need to come up with an outlook macro using vbscript that runs whenever a new email meeting certain criteria is received.
Then after I want to update an excel sheet with some part of the data of the email received.
For example :-

1.A new Email with subject as "EXAMPLE".
2.Outlook then should run this vbscript macro so as to update that excel sheet with some part of the content of the email.
As I am a beginner not having enough knowledge on vbscript please help me out on this one.

Thanka a lot in advance any help appreciated. :)
 
Joined
Sep 4, 2003
Messages
4,916
What version of Outlook are you using? FYI.....The newer versions of Outlook (2003+) already have functionality built in that allow you to set up a mail rule that will fire a macro. In order to help more I would need to see a sample email.

Rollin
 

geek123

Thread Starter
Joined
Dec 7, 2011
Messages
44
Hey Rollin thanks for the reply. I am using Outlook 2007.

Due to security restrictions I cannot attach a sample email too.But i will just tell you the process.
1.An email is received with the subject as "SAMPLE SUBJECT".
2.Now as soon as such an email is received based on its subject it should send subject and body to the excel sheet with subject in one row and body in another.
Thats it.Everytime this same process should happen for the subject mentioned.

Thanks in advance.
 
Joined
Sep 4, 2003
Messages
4,916
How much text will be in the message body? Will you be creating a new workbook for each new email or will you be using 1 workbook and appending the new message bodies to the existing data rows?

Regards,
Rollin
 

geek123

Thread Starter
Joined
Dec 7, 2011
Messages
44
Its about 3-4 lines in the body and i want to use only one workbook for each new email and append the new message bodies to the existing data rows....

Thanks for ur help!!
 

geek123

Thread Starter
Joined
Dec 7, 2011
Messages
44
Hey Rollin can you please help me on this because i gotta complete before this saturday so please any help appreciated.

Thanks in advance!!
 
Joined
Sep 4, 2003
Messages
4,916
There are two possible ways to automatically fire the macro code. The first way is using the built-in Outlook event called NewMailEx. This event will fire each time new email is received so you would need to add logic directly to the macro to read the email subject, body, sender, etc. and then determine what action to take. The second way is to set up a rule in Outlook that would automatically fire the macro. I've found that setting up rules is slightly easier for the novice but it is not as reliable or as versatile in my experience. I will explain both methods and provide examples of both.

Rollin
 
Joined
Sep 4, 2003
Messages
4,916
The first method uses the Outlook event called NewMailEx and is the method that I prefer to use.

First open Outlook and press ALT + F11 to open the VBA editor. On the left side of the editor you will see the "Project Explorer" window. Expand the view in the project explorer window by clicking on the little plus signs (+) next to any folders you see. Look for the object called "ThisOutlookSession" and right click it and select "view code"

Paste the code below into the empty code module and change the values highlighted in red to reflect the correct subject line, workbook path, and workbook sheet name that you will be saving the emails to. The code below will open the workbook and then save the subject of the email to the next available cell in column "A" and the email body to the next available cell in Column "B"

When you close Outlook you will promted to save changes so make sure to click YES to save changes.

Code:
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)

vID = Split(EntryIDCollection, ",")

For i = 0 To UBound(vID)

Set objMail = Application.Session.GetItemFromID(vID(i))

vSubject = UCase(objMail.Subject)
vBody = objMail.Body
vFrom = objMail.SenderEmailAddress

If vSubject = "[COLOR="Red"]TEST SUBJECT[/COLOR]" Then

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("[COLOR="red"]C:\Test\Test.xls[/COLOR]")
Set xlSheet = xlWB.Sheets("[COLOR="red"]Sheet1[/COLOR]")

vRow = xlSheet.Range("A" & xlApp.Rows.Count).End(-4162).offset(1, 0).row

xlSheet.Range("A" & vRow).Value = vSubject
xlSheet.Range("B" & vRow).Value = vBody

xlWB.Save
xlApp.Quit

Set objMail = Nothing
Set xlApp = Nothing

End If

Next i

End Sub
Rollin
 
Joined
Sep 4, 2003
Messages
4,916
The second way of doing this is by using the built in Outlook rules to fire your code. To do this open Outlook and paste the code below into the ThisOutlookSession code module the same way I describe in my previous post. After pasting the code into the module you can close the editor window and begin to set up your custom rule in Outlook. When selecting the rule action to take you would select the option to "Run a Script" and select the procedure that you pasted into the code module previously. Like I said this method works but I have found it to be less reliable and less versatile than using the NewMail event in my previous post.


Code:
Sub SaveIt(myMailItem As Outlook.MailItem)

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("C:\Test\Test.xls")
Set xlSheet = xlWB.Sheets("Sheet1")

vRow = xlSheet.Range("A" & xlApp.Rows.Count).End(-4162).offset(1, 0).row

xlSheet.Range("A" & vRow).Value = myMailItem.Subject
xlSheet.Range("B" & vRow).Value = myMailItem.Body

xlWB.Save
xlApp.Quit

Set objMail = Nothing
Set xlApp = Nothing


End Sub
Rollin
 

geek123

Thread Starter
Joined
Dec 7, 2011
Messages
44
Hey Rollin I am doing the first method but it is not updating the excel sheet. I am following the exact procedure as u mentioned can you please help
 

geek123

Thread Starter
Joined
Dec 7, 2011
Messages
44
Yes macros are enabled and macro security is low only.
When i am clicking on debug it is asking me for the macro name.
 
Joined
Sep 4, 2003
Messages
4,916
Hmmm.....Not sure what is going on as I don't get an error using Outlook 2003. What is the exact error message and which line of code is highlighted when you click debug?

Rollin
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top