Send Outlook Email From Excel With Preview

[Rich Locus]

Forum Members:
I can send an email from Excel with attachments through Outlook, but I'm
trying to PREVIEW the email, change it, and then click "SEND".

The .Display option only displays
The .Send option only sends without the chance of a review

Is there a way to preview, approve, then send?

Here's the SUB I'm using:

Sub SendWithAtt(strWorkBookPath As String)

Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = Worksheets("Master").Cells(104, 1).Value
.CC = ""
.BCC = ""
.Subject = "Charge Back On Products Received"
.Body = "SLW Credit Dept: Put Explanation Here"
.Attachments.Add strWorkBookPath
.Send (I can change to .Display, But It Won't Allow a Send After
Review)
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Steve Yandl]

Rich,

You need to return an Inspector object. The code below is a bit skimpy (I'd
add a means to check if Outlook was already open etc., but I think the code
will show you what you need to add. You will use .display instead of .Send
but using a Set statement with the GetInspector property will set you up to
edit the message body and then click the Send button.

'------------------------------------

Sub SendEmailTypeBody()
Set olApp = CreateObject("Outlook.Application")
olApp.Session.Logon

Set outMail = olApp.CreateItem(0)

On Error Resume Next

With outMail
.To = "(e-mail address removed)"
.Subject = "This is a test"
.Display
End With

Set myInspector = outMail.GetInspector

Set outMail = Nothing
Set olApp = Nothing
End Sub


'------------------------------------

Steve Yandl

 

[Steve Yandl]

Rich,

I went back and tested, with and without the inspector and I can send email
either way (by using .display instead of .send) and still edit the body.

Steve

Rich,

You need to return an Inspector object. The code below is a bit skimpy
(I'd add a means to check if Outlook was already open etc., but I think
the code will show you what you need to add. You will use .display
instead of .Send but using a Set statement with the GetInspector property
will set you up to edit the message body and then click the Send button.

'------------------------------------

Sub SendEmailTypeBody()
Set olApp = CreateObject("Outlook.Application")
olApp.Session.Logon

Set outMail = olApp.CreateItem(0)

On Error Resume Next

With outMail
.To = "(e-mail address removed)"
.Subject = "This is a test"
.Display
End With

Set myInspector = outMail.GetInspector

Set outMail = Nothing
Set olApp = Nothing
End Sub


'------------------------------------

Steve Yandl

 

[Joel]

change the code to display the email and then remove thesed two lines which
is shutting down outlook

Set OutMail = Nothing
Set OutApp = Nothing

the macro will finish and allow you to manually send the email.

 

[Rich Locus]

Joel:
Thanks!! A perfect solution, and not that obvious.
Regards,
Rich Locus

 

[Ron de Bruin]

Very strange, I never have this problem

If you download the second example file from my site, all examples
use display you say that you are nat able to click on send after that ?
http://www.rondebruin.nl/sendmail.htm

 

[Joel]

Ron: If you Set OutMail = Nothing you know destroyed the mail object and you
won't be able to send the mail. Setting the application to nothing is
probably ok becasue the mail object will launch outlook.

Very strange, I never have this problem

If you download the second example file from my site, all examples
use display you say that you are nat able to click on send after that ?
http://www.rondebruin.nl/sendmail.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Ron de Bruin]

Hi Joel

For a manual Send all the code on my site is working OK for me
If Outlook is closed it will send the mail maybe if you open Outlook but it is waiting in the Outbox

If you download the example file do you have different results


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Ron: If you Set OutMail = Nothing you know destroyed the mail object and you
won't be able to send the mail. Setting the application to nothing is
probably ok becasue the mail object will launch outlook.