How do i know if an email was sent and not closed while sending it through vba excel

Question

I have a vba code which generates a outlook email, populates with required To, CC, Subject and Body when i change a particular column in excel. And when the email is sent my status column updates to 'Closed' and Email Sent Flag column updates to '1'. But the problem is when i click on close instes on Send on my email( which was generated and auto populated) even then my status and Email sent flag column gets updated with Closed and 1 respectively. Below is my code.

Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    Dim html As String
    Dim intR As String
    Dim ccStr As String
    Dim Signature As String
    Dim html1 As String
    'Dim itmevt As New CMailItemEvents
    'Dim tsp As String        

    lRow = Cells(Rows.Count, 17).End(xlUp).Row
    lRow1 = ThisWorkbook.Sheets("Validation Lists").Cells(Rows.Count, 4).End(xlUp).Row

    html = "<br>" & ("Hi,") & ("Please spare some time to provide feedback for our service. This will help us to serve you better for upcoming services.") & "<br>"

    For i = 2 To lRow1        
        ccStr = ";" & ThisWorkbook.Sheets("Validation Lists").Cells(i, "D").Value & ccStr
    Next i

    For i = 1 To lRow
        If (Cells(i, "Q").Value = "Closed") And (Cells(i, "R").Value <> "1") Then
            intR = MsgBox("Do you want to send a feedback for " & Cells(i, "B") & "Viz." & Cells(i, "C").Value & " to " & Cells(i, "C") & "?", vbQuestion + vbYesNo)

            If intR = vbYes Then
                Set xOutApp = CreateObject("Outlook.Application")
                Set xMailItem = xOutApp.CreateItem(0)

                With xMailItem
                    .To = Cells(i, "I").Value
                    .CC = ccStr
                    .display
                    Signature = .HTMLBody
                    .Subject = "Feedback for " & Cells(i, "B").Value & " viz. " & Cells(i, "C").Value
                    .HTMLBody = html & "This request was assited by " & Cells(i, "K").Value & "<br><br>" & Signature
                    '.dispaly

                    '.Send
                End With

                Cells(i, "R").Value = "1"
                Set xRgSel = Nothing
                Set xOutApp = Nothing
                Set xMailItem = Nothing
                On Error Resume Next
            End If

            If intR = vbNo Then Cells(i, "Q").Value = "In Progress"     
        End If
    Next i  
End Sub

Note that you should remove `On Error Resume Next`. If you get errors then fix them `On Error Resume Next` will hide all error messages but the errors still occur. If you cannot see them you cannot fix them, if you don't fix them your code cannot work. — Pᴇʜ, Jan 24 '19 at 11:03
If you send the email manually by pressing the send button in Outlook I see no chance for Excel to check if it was sent or closed. To be sure you can only let VBA send the mail with `.Send`, then it is sent. — Pᴇʜ, Jan 24 '19 at 11:12

score 0 · Answer 1 · answered Jan 24 '19 at 11:22

0

You have to check if the message has been sent. There exists a boolean message property named Sent.

answered Jan 24 '19 at 11:22

Victor Ivanidze

397
2
7

score 0 · Answer 2 · answered Jan 24 '19 at 11:46

0

Untested but could work:

Loop until .Sent is True.

With xMailItem
    .To = Cells(i, "I").Value
    .CC = ccStr
    .display
    Signature = .HTMLBody
    .Subject = "Feedback for " & Cells(i, "B").Value & " viz. " & Cells(i, "C").Value
    .HTMLBody = html & "This request was assited by " & Cells(i, "K").Value & "<br><br>" & Signature

    Do Until .Sent = True
       DoEvents
    Loop
End With

answered Jan 24 '19 at 11:46

Pᴇʜ

56,719
10
49
73

1-It works with the .SEND and the .Display ? 2-if they user didn't send or the mail wasn't sent, we will be in an infinite loop and excel will freeze ? – JustGreat Oct 05 '22 at 16:26
@JustGreat The issue is that Excel cannot check if the user closed the email. The `.Sent` parameter will be set to `True` if the user sent the email. In the case above the code will get stuck in the loop until the user sends the email. You can add a timeout so eg. Excel will wait 5 min in the loop. If the user hasn't sent the mail within 5 min Excel will consider it as not sent. But if the user sends the mail after 5 min Excel will not be able to know that. So the only secure way to know if it was sent or not is letting Excel send it with `.Send` instead of `.Display`. – Pᴇʜ Oct 06 '22 at 09:29
thanks for your reply. is there any way with the .send to make sure that the mail was sent ? I mean if we got a server problem or something we will get a notification or by simply using .send we consider it as sent ? Regarding the .Display I saw solutions using class module with an event, like using _Send event in the Outlook.MailItem class – JustGreat Oct 06 '22 at 13:13
by using `.send` you either consider it was sent or you check `.sent` after (note the difference D and T) and if `.sent = true` Outlook considers it as sent (your sending server responded to Outlook that he took the email, doesn't mean the reciepient got it). Well if there are class module solutions then use some of those. – Pᴇʜ Oct 06 '22 at 14:07
thanks again, the problem is that, I may be wrong but as long as I know, the .SenD will kill the object, so if you check the .SenT after you use the .SenD I think that you will get an error like object not found. Regarding the class module solution for the display, here is a link about https://stackoverflow.com/questions/2533066/vba-outlook-mail-display-recording-when-if-sent-manually – JustGreat Oct 07 '22 at 08:20

How do i know if an email was sent and not closed while sending it through vba excel

2 Answers2