Opening a NEW Blank Email from Excel using VBA

[mattmickle]

Hi All,
After countless hours of searching for an answer, I figured I would ask here:

I have a book that creates a unique document then prompts the user if they want to email. I'm fairly experienced at just filling in all the blanks (to, subject, body, etc.), but here's what I'm trying to do. When prompted to email, I would like to open a NEW email window, fill in everything EXCEPT the TO: field and allow the user to type the TO: themselves then send. Using my code, you never see the actual email window, it just does it...here's my code:

Sub EmailMacro()

Set srange = ActiveWorkbook.Worksheets("Email").Range("H9").Cells
For Each scount In ActiveWorkbook.Worksheets("Email").Range(srange).Cell

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
myItem.To = ActiveWorkbook.Worksheets("Email").Range("B" & scount).Cells
myItem.Subject = ActiveWorkbook.Worksheets("Email").Range("C" & scount).Cells
myItem.Body = ActiveWorkbook.Worksheets("Email").Range("D" & scount).Cells
Set myAttachments = myItem.Attachments
myAttachments.Add "\\depts\HR-Share\CHANGE_FORMS\" & ActiveWorkbook.Worksheets("Email").Range("F" & scount).Cells & ".pdf", _
olByValue, 1, "ok"
myItem.Send
Next

MsgBox "The Change Form has been emailed."

End Sub


Does what I'm asking make sense and is it possible?

Thanks,
Matt

 

[DeBeuz]

Hi,

I think you should change the line "myItem.Send" to "myItem.Display".
It should then display the email as far as it has been composed, instead of sending it.

Succes,

Paul

 

[mattmickle]

Hi,

I think you should change the line "myItem.Send" to "myItem.Display".
It should then display the email as far as it has been composed, instead of sending it.

Succes,

Paul

Such a simple little change! I'm self taught at VBA so most of the time I feel like a bumbling idiot!
Thanks!

 

[DeBeuz]

You're welcome.
Thanks for the feedback.
I always use the (translated) saying: Any question, no matter how "stupid", is a sign of intelligence.


Paul

 

[Kdbailey]

Is there any way to do this through chrome/gmail?