Edit, send and save email to file system

Question

We currently have an email automatically created by Excel using VBA, with subject, recipient, message body with template text all filled in.

Sub CreateMail(Optional sFile As String = "")
'Create email to send to requestor with attachment sFile

'Declarations
    Dim app As Outlook.Application
    Dim msg As Outlook.MailItem
    Dim send_to As Recipient
    Dim send_tos As Recipients

'Initiations
    Set app = CreateObject("Outlook.Application")
    Set msg = app.CreateItem(olMailItem)
    Set send_tos = msg.Recipients
    Set send_to = send_tos.Add("receiver@email.com")
    send_to.Type = 1

'Create message
    With msg
        .SentOnBehalfOfName = "sender@email.com"
        .Subject = "This is the email subject"
        .HTMLBody = "This is the email body" & vbCrLf
        'Resolve each Recipient's name.
            For Each send_to In msg.Recipients
              send_to.Resolve
            Next
        If Len(sFile) > 0 Then
            .Attachments.Add sFile
        End If
        .Display
    End With

End sub

After making some manual changes to the email that is created, we'd like to send it and have a copy saved to a folder on the file system automatically (in addition to the usual sent folder in Outlook). Is there a way to do this all within Excel VBA?

I suspect it might be possible using Outlook VBA, however the folders are defined in Excel and we'd like to keep the code together in the one file.

Possible duplicate of [Macro to save selected emails of Outlook in Windows folder](http://stackoverflow.com/questions/28479157/macro-to-save-selected-emails-of-outlook-in-windows-folder) — Robin Mackenzie, Mar 24 '17 at 05:06
The method referred to in the other answer is available to Excel VBA if you add a reference to the Microsoft Outlook Object Model. Perhaps this would be better as an answer if you're not sure where to look? — Robin Mackenzie, Mar 24 '17 at 05:35
In the linked solution, emails are selected in outlook and then saved to a folder. Whereas I'm after saving emails to a folder upon the send event being triggered from an email created from Excel. — Vlad, Mar 24 '17 at 06:34
The referenced code has this line: `oMail.SaveAs sPath & sName, olMSG ` which is the line that saves an email. You should be able to adapt the inner loop of the referenced code to save the email — Nick.Mc, Mar 27 '17 at 00:02

June7 · Answer 1 · 2017-03-24T08:04:56.253

What is your code for sending email? This works for me in an Excel VBA module:

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email address"
    .Subject = "Test"
    .HTMLBody = "Test " & Now
    .DeleteAfterSubmit = True 'to not retain in sent folder
    .Display
    .SaveAs "C:\filepath\Test.txt", 0
'    .Send
End With

However, guess the real trick is allowing edit of the email before saving file. So far not seeing solution for that. Unfortunately the code execution does not pause while the message window is open. I was hoping for the pause since Office is supposed to be an integrated suite of apps - like opening a form in Access in dialog mode which does pause execution of code.

score 0 · Answer 2 · edited May 23 '17 at 12:10

With code in Excel only, monitor the SentItems folder.

Utilizing Outlook Events From Excel

Confirm the mail from a unique ID.

The unique ID could be in the subject or body.

You could try saving the unique ID in PR_SEARCH_KEY. It is the same idea How, can get the exact sent Email from Sent Items folder? and How to uniquely identify an Outlook email as MailItem.EntryID changes when email is moved

Edit, send and save email to file system

2 Answers2