Automatically sending mail from drafts folder whenever new draft is created RRS feed

  • Question

  • Daily I need to send report to my Manager.

    I already return VBA code in spreadsheet that will create report and will process sending mail also. The processed mail will come to my mailbox and saved into my draft folder.

    I want to write VBA code in outlook and the requirement is whenever new draft is created in draft folder, system has to send that mail automatically.

    I wrote following code.


    Sub Send_Drafts()

        Dim oOa As New Outlook.Application

        Dim oNS As Outlook.NameSpace

        Dim oFldr As Outlook.MAPIFolder


        On Error Resume Next


        Set oNS = Application.Session

        Set oFldr = oNS.GetDefaultFolder(olFolderDrafts)


        For Each oMessage In oFldr.Items

                If Left(oMessage.Subject, 11) = "SHYAMScript" Then

                    oMessage.Subject = Right(oMessage.Subject, (Len(oMessage.Subject) - 11))


            End If



        Set oNS = Nothing

    End Sub


    In the above code, the only problem is, it will not run automatically whenever new draft is created in drafts folder.

    As I mentioned above, I need VBA code that will execute automatically with the above logic, whenever new draft is created in drafts folder.

    Your solution is highly appreciated.

    Thanks in advance.


    • Edited by Shyamkh Thursday, February 9, 2012 10:56 PM altered Title
    Thursday, February 9, 2012 10:54 PM

All replies

  • Hi

    I am assuming you are using SendMail from Excel to send a Workbook to your mail Id, and send a formal mail to your manager from Outlook.

    Why can't you try to use the NewMail event in Outlook to handle this. This event fires whenever a new mail hits your inbox.



    Friday, February 10, 2012 1:41 AM
  • Hi Shasur,

    Thanks for the reply. infact i tried Newmail event. But it didn't trigger, if you get new mail in drafts folder. I tried with the following test code.


    Private Sub Application_NewMail()
        MsgBox "testing"

    End Sub




    Friday, February 10, 2012 3:07 PM
  • Hi Sham

    NewMail gets triggered whenever a new mail comes to the Inbox. You can use this to reply and save the mail in Drafts folder



    Sunday, February 12, 2012 1:55 AM
  • Hi Shasur,

    Thanks for your input.

    But I my case, as I am triggering mail from excel, it is directly coming into my Drafts Folder. So, i don't think NewMail event will not help me.

    All I want is whenever there is an new mail item in my Drafts Folder, system has to trigger VBA code.



    Monday, February 13, 2012 2:24 PM