none
Outlook script to forward emails based on senders' address RRS feed

  • Question

  • Hello,

    I desperately need help for this. My office inbox receives 100-200 emails daily which we have to disseminate to different groups of people in the firm. I was hoping to semi-automate this process by forwarding each incoming email based on the senders' address to the corresponding group of recipients. For example, if the senders' address contains "@oaktree.com", then this email should be forwarded to a group of people handling the Oaktree account.

    Typically, if we only had a few accounts, I can upload each account one by one. However, we have 1000+ accounts to be forwarded and I cannot possibly upload them account by account. Each senders' address is also accompanied by a group of recipients and these are all recorded in an excel sheet.

    Is there anyway for the script to search the senders' address in the incoming email, and then forward it to the relevant account handlers stated in the excel sheet?

    Thanks so much so much in advance.

    Best regards,
    Project_paw
    Wednesday, February 3, 2016 3:33 PM

Answers

  • Hello,

    You can create a rule in Outlook which can trigger a VBA macro sub. The sub should look like the following one:

    Public Sub Test(mail as MailItem)
      ' your code goes here
    End Sub

    In the code you can check out the SenderEmailAddress property of the MailItem class. It returns a string that represents the e-mail address of the sender of the Outlook item. 

    If obj.SenderEmailAddress = "someone@example.com" Then 

    Or you may consider handling the NewMailEx event of the Application class. It is fired when a new item is received in the Inbox. The NewMailEx event fires when a new message arrives in the Inbox and before client rule processing occurs. You can use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item. Use this method with caution to minimize the impact on Outlook performance. However, depending on the setup on the client computer, after a new message arrives in the Inbox, processes like spam filtering and client rules that move the new message from the Inbox to another folder can occur asynchronously. You should not assume that after these events fire, you will always get a one-item increase in the number of items in the Inbox.

    The Forward method of the MailItem class executes the Forward action for an item and returns the resulting copy as a MailItem object. Use the Send method to send a copy.

    • Marked as answer by David_JunFeng Tuesday, February 16, 2016 9:46 AM
    Wednesday, February 3, 2016 7:56 PM

All replies

  • This sounds like something you could manage through Outlook rules.  Create a distribution list for each group of recipients.  Then, create a rule to forward incoming mail from a particular sender to the related distribution list.

    • Edited by RLWA32 Wednesday, February 3, 2016 3:40 PM
    Wednesday, February 3, 2016 3:39 PM
  • Hello,

    You can create a rule in Outlook which can trigger a VBA macro sub. The sub should look like the following one:

    Public Sub Test(mail as MailItem)
      ' your code goes here
    End Sub

    In the code you can check out the SenderEmailAddress property of the MailItem class. It returns a string that represents the e-mail address of the sender of the Outlook item. 

    If obj.SenderEmailAddress = "someone@example.com" Then 

    Or you may consider handling the NewMailEx event of the Application class. It is fired when a new item is received in the Inbox. The NewMailEx event fires when a new message arrives in the Inbox and before client rule processing occurs. You can use the Entry ID returned in the EntryIDCollection array to call the NameSpace.GetItemFromID method and process the item. Use this method with caution to minimize the impact on Outlook performance. However, depending on the setup on the client computer, after a new message arrives in the Inbox, processes like spam filtering and client rules that move the new message from the Inbox to another folder can occur asynchronously. You should not assume that after these events fire, you will always get a one-item increase in the number of items in the Inbox.

    The Forward method of the MailItem class executes the Forward action for an item and returns the resulting copy as a MailItem object. Use the Send method to send a copy.

    • Marked as answer by David_JunFeng Tuesday, February 16, 2016 9:46 AM
    Wednesday, February 3, 2016 7:56 PM
  • Hi, Project_paw

    Accoring to your description, I agree with Eugene and RLWA32's suggestion. In addition if you want to open excel file within outlook,

    Tools->References->Microsoft Excel xx.0 Object Library then refer to below code:

    Public Sub OpenMyExcelFile()
      Dim fileName As String
      Dim xlsxApp As Object ' Excel.Application
      Dim Wb As Object ' Excel.Workbook
      Dim Ws As Object ' Excel.Worksheet
      Dim Rn As Object ' Excel.Range
    
      fileName = "c:\file.xlsx"
    
      On Error Resume Next
      Set xlsxApp = GetObject(, "excel.application")
      On Error GoTo 0
      If xlsxApp Is Nothing Then Set xlsxApp = New Excel.Application
      Set Wb = xlsxApp.Workbooks.Open(fileName)
      Set Ws = Wb.Sheets(1)
      Ws.Activate
      Set Rn = Ws.Range("a1")
      Rn.Activate
      xlsxApp.Visible = True
    End Sub
    

    Thursday, February 4, 2016 7:33 AM