Run Outlook Macro For Received Emails.

[geek123]

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.

 

[geek123]

Can anyone please let me know about this

 

[Rollin_Again]

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]

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.

 

[Rollin_Again]

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]

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]

Hey Rollin can you please help me on this because i gotta complete before this saturday so please any help appreciated.

Thanks in advance!!

 

[Rollin_Again]

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

 

[Rollin_Again]

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.

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

 

[Rollin_Again]

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.

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]

Thanks a ton Rollin i will try this and let u know thanks again

 

[geek123]

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

 

[Rollin_Again]

In Outlook do you have macros enabled and macro security set to low?

Rollin

 

[geek123]

Yes macros are enabled and macro security is low only.
When i am clicking on debug it is asking me for the macro name.

 

[Rollin_Again]

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