VBA convert from sending email to display in new email-CodePudding

I'm hopeless with editing current code. It's excel file table from active sheet sent directly via email and it's working well. 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.). Could someone help me with it please?

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

CodePudding user response：

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.

CodePudding user response：

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 = "[email protected]"
.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.