Pop up Outlook window using excel

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi have following module that uses Outlook

Sub Send_Pic()
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

StrBody = "<BODY style = font-size:14pts, font-family:Arial>" & _
"Dear Resident, <p> There is a parcel ready for collection<br>" & _
"It can be picked up from concierge<br>" & _
"<br>Kind Regards<br>"

On Error Resume Next
With OutMail
.to = Range("H3").Value
.CC = ""
.BCC = ""
.Subject = "Parcel ready for collection " & Format(Date, "dd/mm/yyyy")
.Display
.HTMLBody = StrBody & _
"<img src='C:\Users\*******\Desktop\bla\Hyde Logo.jpg' height='10%'> " & _
.HTMLBody
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing


End Sub

What I would like to happen is before email is sent, that it could POP UP so I can edit email if need be. Currently when I run the module, Outlook flickers on the taskbar to let me know that an email is ready to be sent.
Any suggestions on how the email can pop up before it is sent?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Lee J

New Member
Joined
Jun 30, 2020
Messages
43
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi [U]Tonysdilemma,[/U]

Is the above the full code? I'll tell you why I'm asking it only displays for me without sending so this is probably a setting somewhere if the code is complete. You could also try moving the .Display to between the .HTMLBody and End With to see the full message.

You would normally have .send in there to have it go without reviewing
 

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi [U]Tonysdilemma,[/U]

Is the above the full code? I'll tell you why I'm asking it only displays for me without sending so this is probably a setting somewhere if the code is complete. You could also try moving the .Display to between the .HTMLBody and End With to see the full message.

You would normally have .send in there to have it go without reviewing

Hi Lee J
Yes it is complete code. I have .display rather than .send only because i may need to amend email before sending. I tried what you suggested same thing happens. Alerts me on taskbar rather than display as pop up window.
 

Lee J

New Member
Joined
Jun 30, 2020
Messages
43
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
When you say alerts/flickers has it sent or is it just minimised?
 

Tonysdilemma

New Member
Joined
Aug 2, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

When you say alerts/flickers has it sent or is it just minimised?
No, not sent, it is minimised. Would prefer pop up window before sending.
 

Lee J

New Member
Joined
Jun 30, 2020
Messages
43
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Aha, then use this under your other End With

VBA Code:
  With GetObject(, "Outlook.Application")
    .ActiveWindow.WindowState = 0
  End With
 

Watch MrExcel Video

Forum statistics

Threads
1,112,768
Messages
5,542,427
Members
410,550
Latest member
ganeshsamant
Top