Convert from sending email immediately from Excel to displaying in Outlook

Question

In this code an Excel file table from active sheet is sent directly via email.

I need to change it to same result only difference is I need it open in Outlook as draft and not send it (there will be added more of text etc.).

I tried .Display but it won't open Outlook new email, it is displayed in Excel.

Sub Send()
    Dim AWorksheet As Worksheet
    Dim Sendrng As Range
    Dim rng As Range

    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Fill in the Worksheet/range you want to mail
    'Note: if you use one cell it will send the whole worksheet
    Set Sendrng = Range("B1:M44")

    'Remember the activesheet
    Set AWorksheet = ActiveSheet
 
    With Sendrng
        ' Select the worksheet with the range you want to send
        .Parent.Select
        
        'Remember the ActiveCell on that worksheet
        Set rng = ActiveCell 

        'Select the range you want to mail
        .Select

        ' Create the mail and send it
        ActiveWorkbook.EnvelopeVisible = True

        With .Parent.MailEnvelope
            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = "Dear All," & vbNewLine & vbNewLine & "Please find XXX." 

            With .Item
                .To = "XXXX"
                .Subject = "XXX"
                .Send
            End With

        End With

        'select the original ActiveCell
        rng.Select

    End With

    'Activate the sheet that was active before you run the macro
    AWorksheet.Select

StopMacro:
    With Application

Dmitry Streblechenko · Answer

Replace .Send with .Display. If you want to wait for the user to either click on the Send button or dismiss the message, use .Display(true) to show it modally.

Eugene Astafiev · Answer

You can automate Outlook directly from an Excel macro where you could display a new mail item to a user. For example, the following code creates a new mail items, set up properties on the item and then display it for a user:

variables declared as a specific object type ie. specific to the application which is being automated:
Dim applOL As Outlook.Application
Dim miOL As Outlook.MailItem

'Create a new instance of the Outlook application. Set the Application object as follows:
Set applOL = New Outlook.Application
'create mail item:
Set miOL = applOL.CreateItem(olMailItem)

 

With miOL

.To = "info@test.com"
.CC = ""
.Importance = olImportanceLow
.Subject = "Mail Automation"
.Body = "Sending the Active Excel Workbook as attachment!"
'add host workbook as an attachment to the mail:
.Attachments.Add ActiveWorkbook.FullName
.ReadReceiptRequested = True

.Display

End With


'clear the object variables:
Set applOL = Nothing
Set miOL = Nothing

Don't forget to add an Outlook COM reference to your VBA project in Excel. See Automating Outlook from a Visual Basic Application for more information.

Convert from sending email immediately from Excel to displaying in Outlook

2 Answers