I have coded an automatic process of sending multiple emails in excel VBA. But I don't want each email to be sent until I press "Send" button in Outlook by myself. For example, there are 3 persons I need to send email. The macro generates and displays the email for the first person and wait for me to press the "Send". When I press the "Send", the macro loops to next person and displays the email and wait for me to send.
I don't know how to write this code for allowing me to press "Send" button in Outlook and then trigger next round.
Here is my code. Thank you very much.
Sub MultiEmailSend()
Dim row_number As Integer
Dim OutApp As Object
Dim OutMail As Object
Dim AttachFile As Range
Dim OutWordEditor As Object
Dim WordDoc As Object
Dim WordFile As String
Dim FileFolder As String 'The folder for thisworkbook
Dim Fname As String
Dim EmployeeN As String
Dim rngSearch As Range 'The range which to search in
Dim rowFirstFind As Integer 'Return the first row# where the EmployeeN is found
Dim CountN As Integer 'Return the number of the same EmployeeN
Dim rngAttach As Range 'Return the range of attachment of certain EmployeeN
FileFolder = ThisWorkbook.Path
Sheets("Attachment List").Range("C2") = FileFolder
Set OutApp = CreateObject("Outlook.Application")
'Get word file and copy content
WordFile = Application.GetOpenFilename(Title:="Select MS Word file", MultiSelect:=False)
Set WordDoc = GetObject(WordFile)
WordDoc.Content.Copy
'WordDoc.Close
row_number = 1
Do Until row_number = 3
row_number = row_number + 1
'Get the range of attachments on sheets("Attachment List") of certain Employee
EmployeeN = Sheets("Home").Range("A" & row_number).Value
Set rngSearch = Sheets("Attachment List").Range("A:A")
rowFirstFind = rngSearch.Find(What:=EmployeeN, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
CountN = Application.WorksheetFunction.CountIf(Sheets("Attachment List").Range("A:A"), EmployeeN)
Set rngAttach = Sheets("Attachment List").Range("D" & rowFirstFind).Resize(CountN, 1)
'Create new email
Set OutMail = OutApp.CreateItem(0)
Set OutWordEditor = OutMail.GetInspector.WordEditor
'Paste content from word to email body
OutWordEditor.Content.PasteAndFormat Type:=wdFormatOriginalFormatting
'Writing an email
With OutMail
.BodyFormat = olFormatHTML
.To = Sheets("Home").Range("B" & row_number).Value
'.CC = ""
'.BCC = ""
.Subject = Sheets("Home").Range("E" & row_number).Value
For Each AttachFile In rngAttach 'Attach attachments
.Attachments.Add AttachFile.Value
Next AttachFile
.Display
Application.CutCopyMode = False
End With
Set OutMail = Nothing
Set AttachFile = Nothing
Set rngAttach = Nothing
With Sheets("Home").Range("A" & row_number).Interior 'Highlight the EmployeeN which has been sent
.Color = 5296274
End With
Loop
Set OutApp = Nothing
End Sub
Bookmarks