CODEWITHSUNDEEP
vbaexcelemailoutlook
USA
USA

Reputation: 19

How to delete the mail from the senders sent item from Outlook

I am getting run time error for the below Excel to VBA code to delete the senders mail

        Sub Worksheet_Change(ByVal Target As Range)
        Dim PriorVal As String
' For the worksheet AuditLog        
        With Sheets("AuditLog")
        If Selection(1).Value = "" Then
                PriorVal = "Blank"
            Else
                PriorVal = Selection(1).Value
            End If
        Set OutlookApp = CreateObject("Outlook.Application")
        Set OlObjects = OutlookApp.GetNamespace("MAPI")
        Set newmsg = OutlookApp.CreateItem(olMailItem)
        'add recipients
        newmsg.Recipients.Add ("add your mail id")
        'add subject
        newmsg.Subject = ("AuditLog has a violator") '"," Environ("UserName"), Environ("ComputerName"), & sh.Name "
        'add body
        newmsg.Body = Application.UserName & " has made changes to the AuditLog _ tab at cell position " & Target(1).Address & ", " & Target(1).Value
        newmsg.Display 'display
        newmsg.Send 'send message
        newmsg.DeleteAfterSubmit = True
        End With
            Set newmsg = Nothing:     Set OutlookApp = Nothing:
        End Sub

Upvotes: 0

Views: 852

Answers (2)

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66215

In addition to what @mrbungle wrote, as a rule of thumb, once you call Send, the only allowed operation is dereferencing the message - it now belongs to the spooler.

There is also no reason to call Display (modelessly) and then Send while the message is still being displayed. If you are displaying the message, would you not want the user to click the Send button?

Upvotes: 1

mrbungle
mrbungle

Reputation: 1931

newmsg.Send 'send message
newmsg.DeleteAfterSubmit = True

switch these around

newmsg.DeleteAfterSubmit = True
newmsg.Send 'send message

Upvotes: 3

Related Questions