1

I want to save the email in my local folder, and I saw this link https://www.mrexcel.com/forum/excel-questions/361751-vba-saving-email-only-after-send-pushed.html which basically use the class module to save the email after sending it out. However the problem is, the email saved is the preview email (email that is being displayed before you send the email) instead of sent email (email in which you cannot edit anything anymore)

Dim cls_OL As New clsOutlook
Public objMail_SentMsg As Object
Public Emailpath As String

Sub SendEmail()
    Dim OutMail As Object
    Set cls_OL.obj_OL = CreateObject("Outlook.Application")
    cls_OL.obj_OL.Session.Logon
    Set OutMail = cls_OL.obj_OL.CreateItem(0)
    Set objMail_SentMsg = OutMail
    Emailpath = "V:\test\emailname.msg"
    With OutMail
    On Error Resume Next
        'Assume this all strings variables are fine
        .HTMLBody = strmsgContent1 & strmsgContent2
        .to = ToEmail
        .CC = CC
        .BCC = BCC
        .Subject = Subject
        .Display
    End With
    Set OutMail = Nothing
End Sub
Option Explicit
Public WithEvents obj_OL As Outlook.Application

Private Sub obj_OL_ItemSend(ByVal Item As Object, Cancel As Boolean)
    objMail_SentMsg.SaveAs Emailpath
    Set obj_OL = Nothing
End Sub

It saved the email succesfully but as mentioned, only saved the preview/display email not the sent email.

Thank you so much for your help.

Brian Chew
  • 55
  • 1
  • 9
  • The sent mail is passed to your event handler as `Item`, so you should work with that and not your global variable `objMail_SentMsg` – Tim Williams Oct 18 '18 at 03:56
  • @TimWilliams I have change the `objMail_SentMsg` to `Item`. However the result is still the same. Email saved as a preview instead of sent. – Brian Chew Oct 18 '18 at 05:43

1 Answers1

1

Instead of ItemSend monitor the SentItems folder with ItemAdd.

Do not save objMail_SentMsg, save the item identified by ItemAdd as being added to the folder.

If necessary to differentiate mail not to be saved, set up some unique characteristic in the mail when it is created.

niton
  • 8,771
  • 21
  • 32
  • 52
  • I change the code to follow your instruction, now there is no file in the folder. Do you mind to write the code here? @niton – Brian Chew Oct 19 '18 at 05:25
  • Without code I cannot know what you did. See https://stackoverflow.com/questions/48496150/how-do-i-use-itemadd-on-the-sent-items-folder – niton Oct 19 '18 at 15:27
  • Above are my codes in microsoft excel. basically I know how to do it as per normal without saving the message in my local computer drive. However, I want to save it now, and it doesnot save it as a 'sent email' but as a 'preview email'. I saw the link but it is more for the outlook vba instead of excel vba. – Brian Chew Oct 22 '18 at 02:31
  • Did you try the code in Outlook? If you want to use Excel exclusively see https://stackoverflow.com/questions/35894485/utilizing-outlook-events-from-excel – niton Oct 24 '18 at 20:16