0

I would like to send a mail to different team mail addresses when a new mail is drag-and-dropped to their respective folder.

If someone drag and drop a mail into SubFolderTeam1, a mail will be sent to MailTeam1.

Same when we drag and drop a mail into SubFolderTeam2, a mail will be sent to MailTeam2.

My folder structure:

  • Inbox
  • Parent Folder:
    • SubFolderTeam1
    • SubFolderTeam2

Team mail addresses:

  • MailTeam1
  • MailTeam2

The Parent Folder containing the Sub Folders is at the same level as the Default Folder "Inbox".

I have tried something based on the answers of this question: How do I trigger a macro to run after a new mail is received in Outlook?

Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()
  Dim olApp As Outlook.Application
  Dim objNS As Outlook.NameSpace
  Set olApp = Outlook.Application
  Set objNS = olApp.GetNamespace("MAPI")
  ' default local Inbox
  Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub Items_ItemAdd(ByVal item As Object)

  On Error GoTo ErrorHandler
  Dim Msg As Outlook.MailItem
  If TypeName(item) = "MailItem" Then
    Set Msg = item
    ' ******************
    
    'do the magic please
    
    ' ******************
  End If
ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

Combined with the below.

Sub Send_Emails()

  Dim OutlookApp As Outlook.Application
  Dim OutlookMail As Outlook.MailItem

  Set OutlookApp = New Outlook.Application
  Set OutlookMail = OutlookApp.CreateItem(olMailItem)
  
  With OutlookMail
    .BodyFormat = olFormatHTML
    .Display
    .HTMLBody = "Dear Team1" & "<br>" & "<br>" & "Please do you job. Thanks" & .HTMLBody
    .To = "MailTeam1@gmail.com"
    .Subject = "Test Subject"
    .Send
  End With

End Sub

I tried to insert the second structure of code within the first one.
I tried to run them separately: first code on a Class Module, the second on a classic Module.
And several other things that have no real sense in our dimension.

Community
  • 1
  • 1

2 Answers2

0

There is no need to create a new Outlook Application instance for sending emails:

Private WithEvents Items As Outlook.Items
Private Sub Application_Startup()
  Dim olApp As Outlook.Application
  Dim objNS As Outlook.NameSpace
  Set olApp = Outlook.Application
  Set objNS = olApp.GetNamespace("MAPI")
  ' default local Inbox
  Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub
Private Sub Items_ItemAdd(ByVal item As Object)

  On Error GoTo ErrorHandler
  Dim Msg As Outlook.MailItem
  If TypeName(item) = "MailItem" Then
    Set Msg = item
    ' ******************
    
    'do the magic please
    
    ' ******************
    Send_Emails
  
  End If
ProgramExit:
  Exit Sub
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

Sub Send_Emails()

  Set OutlookMail = Application.CreateItem(olMailItem)
  
  With OutlookMail
    .BodyFormat = olFormatHTML
    .Display
    .HTMLBody = "Dear Team1" & "<br>" & "<br>" & "Please do you job. Thanks" & .HTMLBody
    .To = "MailTeam1@gmail.com"
    .Subject = "Test Subject"
    .Send
  End With

End Sub
Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
  • Hi Eugene, thanks for your suggestions, however it does not take into account that i have to send a different mail to different team depending on where i drag and drop my mail (SubFolderTeam1 or SubFolderTeam2). – KevDaNoob2020 Jul 13 '20 at 11:15
0

You can start at a default folder then navigate to the applicable folders. You will need ItemAdd code for each folder.

Code goes in the ThisOutlookSession module.

Option Explicit

Private WithEvents Items1 As items
Private WithEvents Items2 As items

Private Sub Application_Startup()

    Dim objNS As Namespace
    Set objNS = GetNamespace("MAPI")
    
    Dim inBox As folder
    Dim mailBox As folder
    
    Dim firstLevelFldr As folder
    Dim secondLevelFldr As folder
    
    Set inBox = objNS.GetDefaultFolder(olFolderInbox)
    
    Set mailBox = inBox.Parent
    'Debug.Print mailBox
    
    Set firstLevelFldr = mailBox.folders("Parent Folder")
    'Debug.Print firstLevelFldr
    
    Set secondLevelFldr = firstLevelFldr.folders("SubFolderTeam1")
    'Debug.Print secondLevelFldr
    Set Items1 = secondLevelFldr.items
    
    Set secondLevelFldr = firstLevelFldr.folders("SubFolderTeam2")
    'Debug.Print secondLevelFldr
    Set Items2 = secondLevelFldr.items
    
End Sub


Private Sub Items1_ItemAdd(ByVal item As Object)
    
    Dim OutlookMail As mailItem
    
    If TypeName(item) = "MailItem" Then
    
        Set OutlookMail = CreateItem(olMailItem)
  
        With OutlookMail
            .BodyFormat = olFormatHTML
            .Display
            .HTMLBody = "Dear Team1" & "<br>" & "<br>" & "Please do you job. Thanks" & .HTMLBody
            .To = "MailTeam1@gmail.com"
            .Subject = "Test Subject"
            .Send
        End With
  
    End If
  
End Sub


Private Sub Items2_ItemAdd(ByVal item As Object)
    
    Dim OutlookMail As mailItem
    
    If TypeName(item) = "MailItem" Then
    
        Set OutlookMail = CreateItem(olMailItem)
        
        With OutlookMail
            .BodyFormat = olFormatHTML
            .Display
            .HTMLBody = "Dear Team2" & "<br>" & "<br>" & "Please do you job. Thanks" & .HTMLBody
            .To = "MailTeam2@gmail.com"
            .Subject = "Test Subject"
            .Send
        End With
  
    End If
  
End Sub
niton
  • 8,771
  • 21
  • 32
  • 52
  • Hi Niton, Thanks a lot for your suggestion. It looks good to me, but when i tried to drag and drop a mail in the subfolders SubFolderTeam1 and SubFolderTeam2, no mail was sent . I have entered the Application_Startup() part under a class module and Items1_ItemAdd/Items2_ItemAdd under a normal Modules. I have also tried to do a simple copy/paste in the class module (obviously adapting my variables and text to my needs) but result was the same and no mail was sent. Is there something i have done wrong ? the ItemAdd approach seems to be the solution but could not say where i failed. – KevDaNoob2020 Jul 13 '20 at 11:09
  • The last line here https://stackoverflow.com/a/11267757/1571407 indicates where the code goes. – niton Jul 13 '20 at 16:35
  • It worked as expected. You are awesome Niton. thanks a lot for your help. – KevDaNoob2020 Jul 15 '20 at 11:51