Email Macro o send Outlook Email

[asivaprakash]

Hi ,

I have completed writing the macro to send an email from excel to mail address.However these are my challenges

1.I want the mail to be reviewed before it is sent but it goes out automatically.

2.Also multiple windows open when the macro is run

I have searched the forum there is none similar.

Any one can help

SP -Please see file below

https://hotfile.com/dl/225330406/4f8030d/email.xlsm.html

 

[FormR]

I have not followed your link - it is much better to just post your code here.

But for (1) if you have:
.send
Change to:
.display

 

[asivaprakash]

As Requested

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 2 To 4 'data in rows 2-4
' Get the email address
Email = Cells(r, 2)

' Message subject
Subj = "Case#,Order#,$"


' Compose the message
Msg = ""
Msg = Msg & "Hi " & Cells(r, 1) & "," & vbCrLf & vbCrLf
Msg = Msg & "Customer is disputing the charges "


Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
Msg = Msg & "Sivaprakash A" & vbCrLf
Msg = Msg & "Chargebacks"

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg


' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus


' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Next r
End Sub

 

[FormR]

For 1.

Try removing this section.

'       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"

Out of interest, what email client are you using?

 

[Ron de Bruin]

I have a lot of examples here
Mail from Excel with Outlook (VBA)

Hi ,

I have completed writing the macro to send an email from excel to mail address.However these are my challenges

1.I want the mail to be reviewed before it is sent but it goes out automatically.

2.Also multiple windows open when the macro is run

I have searched the forum there is none similar.

Any one can help

SP -Please see file below

https://hotfile.com/dl/225330406/4f8030d/email.xlsm.html

 

[asivaprakash]

Outlook Sir