1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Run Outlook Macro For Received Emails.

Discussion in 'Business Applications' started by geek123, Dec 7, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. geek123

    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. :)
     
  2. geek123

    geek123 Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    44
    Can anyone please let me know about this
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  4. geek123

    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.
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  6. geek123

    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!!
     
  7. geek123

    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!!
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  11. geek123

    geek123 Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    44
    Thanks a ton Rollin i will try this and let u know thanks again
     
  12. geek123

    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
     
  13. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    In Outlook do you have macros enabled and macro security set to low?

    Rollin
     
  14. geek123

    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.
     
  15. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1030249

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice