Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    962
    Thanks
    3
    Thanked 12 Times in 11 Posts

    Outlook VBA - Delete Mail Item after Sending

    I have a VBA macro that is used in Outlook that automatically sends an email. I don't want that email to be saved in the user's Sent folder.

    First, I tried setting DeleteAfterSubmit to True. That did not delete the email from the Sent folder.

    Code:
     Set msg = Outlook.Application.CreateItem(olMailItem)
        msg.Subject = sMsg
        msg.Body = sBody
        msg.To = "user@domain.com"
        msg.DeleteAfterSubmit = True
        msg.Send
    My second attempt was as follows, but it takes too long because the For loop processes the Items from the oldest to the most recent. There could be hundred or even thousands of Items in the Sent folder. (Debug.Print shows me the subject line of each Item in the For loop.)

    Code:
        For Each oMail In ns.GetDefaultFolder(olFolderSentMail).Items
            Debug.Print oMail.Subject
            If oMail.Subject = sSubject Then
                Beep
                Set NewMail = oMail
                Exit For
            End If
        Next
        If Not NewMail Is Nothing Then
            NewMail.Delete
        End If
    It would make sense to process the Items in reverse order (Newest first), since we know that this Item will be the most recent Sent item. Can someone offer a suggestion on how to do that... or how to ensure the DeleteAfterSubmit will function.

    Many thanks.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Surrey, UK
    Posts
    251
    Thanks
    10
    Thanked 59 Times in 54 Posts
    Well, I tried your send code in Outlook 2000 and it worked fine: msg got sent, but did not appear in Outlook's 'sent' folder. However, I am using gmail, and it did get 'filed' in the gmail sent folder - there may be no way to stop that, as it was presumably done by gmail when it received the insruction from outlook.

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    962
    Thanks
    3
    Thanked 12 Times in 11 Posts
    I am using a gmail account also. My code looks for the default Sent folder, which is where I expect the sent mail item to be. My code to delete the message functions well -- in the Sent folder where the message was "filed". My goal is to speed up execution by processing newest to oldest, as I know the "just sent" mail item will be the most recent. Also, why do you believe that Outlook gives gmail "instructions"?

  4. #4
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Surrey, UK
    Posts
    251
    Thanks
    10
    Thanked 59 Times in 54 Posts
    [QUOTE=richardbarrett;1092726]Also, why do you believe that Outlook gives gmail "instructions"?/QUOTE] Well, Outlook sends the email to a server (in my case smtp.gmail.com), so by instructions I mean it tells the server to do something with the email, which it does (bear in mind I am no IT or email expert, so I may use the wrong terms): the server sends the email off to wherever. The gmail server knows nothing about the VBA instruction to delete after submit, so it files a copy in its sent folder, and (in my case) Outlook then updates its view of that server, and shows the sent message (and maybe Outlook does it all - I don't know!).

    Now I still don't know if the sent folder copy you want to remove is in a remote (server) folder or a local Outlook (maybe .pst or .ost) folder (I can't tell which is the default, and wouldn't know at my end how to check), but I can have a think about controlling the order of processing. What is 'ns' in your delete code? OK, its NameSpace.

    Would the 'sort' function be any help? I realise that may just take ages itself. Then I found some code here that allows you to define where the message is saved on sending (so deleting would be simple), but its a lot of code!
    Last edited by mngerhold; 2018-07-10 at 11:24. Reason: add idea at end #2

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    962
    Thanks
    3
    Thanked 12 Times in 11 Posts
    Thank you. The code you reference for saving the message to a particular folder allows the user to select the folder (the "PickFolder" method) from an Outlook dialog box, and I have used something several times in a custom macro called "Send and File". My purpose here is to send an email without any user interaction and to avoid having the user see the sent email and wonder what it is.

    I've continued to research this issue and have learned that a better way is to Find the mail item by Subject:
    Code:
    Set oMail = oItems.Find("[Subject] = ""Test Email""")
    where oMail is the returned mail item, oItems represents the items in the folder which I have defined (ns.GetDefaultFolder(olFolderSentMail)). This works very well, with one issue which I have not yet resolved: Find looks only in the "Today" group of the Sent folder. That's not really a problem because I can be certain that the email was sent today. A For loop with the FindNext method will find multiple emails with the subject "Test Email"and delete all of them -- in the Today group. For my purpose, that is sufficient, but I am curious about how to have the code process "Yesterday", "Last week" and other groups. (I am using Outlook 2016, and do not recall if Outlook 2000 divided emails in this way.)

    As I watch my code run, the Send method causes the message to appear in the default Sent folder almost immediately. It doesn't appear that the gmail server is involved in that. I'm sending the email to myself (my email account on my personal domain), and that takes about 2 minutes.
    Last edited by richardbarrett; 2018-07-10 at 15:30.

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,914
    Thanks
    63
    Thanked 1,115 Times in 1,037 Posts
    Rather than trying to delete the email, save the sent mail in the Trash on send.
    msg.SaveSentMessageFolder

    cheers, Paul

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    962
    Thanks
    3
    Thanked 12 Times in 11 Posts
    Thank you. That seemed like a good idea, but I get an Out of Memory error. I Google'd that and it seems gmail is not amenable to the code. It looks like mngerhold (post above) had a point about the gmail server. I'm not familiar with gmail and how it differs and gmail would never come into play with my code -- other than that I am using gmail for my testing purposes. Were it not for gmail, my origianal line

    Code:
    msg.DeleteAfterSubmit = True
    would probably work, as mngerhold stated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •