Capture time mail was sent

Question

I have an Excel workbook that contains a list of email templates and attachments.

I have an insert button for each list item to separately combine each email (required as per procedure).

I have written the code to combine them and display an email:

Public Sub SendToThisPerson()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Dim r, c As Integer
    Dim b As Object
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        r = .Row
        c = .Column
    End With
    r = 14
    c = 3
    
    Dim filename As String, subject1 As String, path1, path2, wb As String
    Dim wbk As Workbook
    filename = ThisWorkbook.Worksheets(1).Cells(r, c + 4)
    path1 = Application.ThisWorkbook.Path & ThisWorkbook.Worksheets(1).Range("F4")
    path2 = Application.ThisWorkbook.Path & ThisWorkbook.Worksheets(1).Range("F6")
    wb = ThisWorkbook.Worksheets(1).Cells(r, c + 7)
    
    Dim outApp As Outlook.Application
    Dim oMail As Outlook.MailItem

    Set outApp = New Outlook.Application
    Set oMail = outApp.CreateItemFromTemplate(path1 & filename)

    oMail.Display
    subject1 = oMail.subject
    subject1 = Left(subject1, Len(subject1) - 10) & Format(ThisWorkbook.Worksheets(1).Range("D7"), "DD/MM/YYYY")

    Set wbk = Workbooks.Open(filename:=path2 & wb)
    
    wbk.Worksheets(1).Range("I4") = ThisWorkbook.Worksheets(1).Range("D7").Value
    wbk.Close True
    
    With oMail
        .subject = subject1
        .Attachments.Add (path2 & wb)
    End With
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

The user decides either to send or not to send.

I would like to catch if the "Send" button was clicked, add the time of the send to the worksheet.
I experimented with Application events.

Essentially, you want to catch the [Application.ItemSend](https://msdn.microsoft.com/en-us/library/office/ff865076.aspx) event in Outlook (not in Excel) and you want to make sure that you can distinguish between any other mail item and the ones you generate automatically. Maybe that's a certain `.To` email address or that there is a certain string such as `---auto email---` in the body or the subject line to identify these emails. If that's the case then Outlook VBA (once again, not Excel) will have to write into that Excel file the information that you want. — Ralph, Apr 28 '17 at 14:05
I don't want to write into email. I just need to know if the email was sent or not!? If user has clicked Send, the time of the system let say Now(), to be written on the worksheet in order to indicate that this list item was combined and sent. — vlad.lisnyi, Apr 28 '17 at 14:19
I have seen code on the internet, one guy was using interesting approach: create an object, put the opened email to the object and use application events. For example _Close event, if the email was closed with X(red button) or something in this direction. How do you think would it work? should I spend time on this or it would not work with outlook items? — vlad.lisnyi, Apr 28 '17 at 14:48
Maybe I should stop trying and leave it to someone else. Apparently I am unable to make my point. Once again I can only point out this link to you: https://msdn.microsoft.com/en-us/library/ff865379.aspx and hope that you understand that you are on the right track with your last comment. Look at the code example on the linked site and make sure that you create your mailItem `WithEvents` as suggested in the code. Then you should be able to trap the button press. — Ralph, Apr 28 '17 at 15:02
This looks like a duplicate post, see [VBA Outlook Mail .display, recording when/if sent manually](http://stackoverflow.com/questions/2533066/vba-outlook-mail-display-recording-when-if-sent-manually) and [Jon Fournier's response to it.](http://stackoverflow.com/users/5106/jon-fournier) — Boeryepes, Apr 29 '17 at 06:41
Possible duplicate of [VBA Outlook Mail .display, recording when/if sent manually](http://stackoverflow.com/questions/2533066/vba-outlook-mail-display-recording-when-if-sent-manually) — Ralph, Apr 29 '17 at 17:37

score 0 · Answer 1 · edited May 23 '17 at 11:54

0

For all who are interested in this problem, find the progression on the solution: EXCEL VBA, Manual Outlook email sender, Class module Issue

edited May 23 '17 at 11:54

Community

1
1

answered May 16 '17 at 10:01

vlad.lisnyi

325
2
12

Capture time mail was sent

1 Answers1

Linked