Prompt the start of an email...user fills in email address

[gab1972]

There is a lot of great information for sending emails...but all of
them seem to do one of two things...or both. Either they
automatically send the email (no prompt) and/or it's to attach the
workbook they are in.

What I want to do...
My workbook has the option for my users to send a permit application
package. I want the user to click a macro assigned button that will
start an email (Outlook) with just a subject (New Permit Application
Package) and body (Attached is the permit application package you will
need to submit for a new permit.) and then attach two files (a pdf,
and a word document). The macro would then unload and the user is
left to fill in the To line. Then when the user is ready, they
themselves click the Send button. I know this should be relatively
easy...just can't seem to figure it out. Ron has a lot of good
information but either I can't piece it together to make it work or I
have a lot of errors in my own tweaking. Any help would be greatly
appreciated.

 

[gab1972]

There is a lot of great information for sending emails...but all of
them seem to do one of two things...or both.  Either they
automatically send the email (no prompt) and/or it's to attach the
workbook they are in.

What I want to do...
My workbook has the option for my users to send a permit application
package.  I want the user to click a macro assigned button that will
start an email (Outlook) with just a subject (New Permit Application
Package) and body (Attached is the permit application package you will
need to submit for a new permit.) and then attach two files (a pdf,
and a word document).  The macro would then unload and the user is
left to fill in the To line.  Then when the user is ready, they
themselves click the Send button.  I know this should be relatively
easy...just can't seem to figure it out.  Ron has a lot of good
information but either I can't piece it together to make it work or I
have a lot of errors in my own tweaking.  Any help would be greatly
appreciated.

Okay...so I attempted to tackle this on my own (with some reference
help...thanks Ron, Oz, and D Peterson). Here is what I got to work in
case anyone is interested. It allows me to not have to store email
addresses and file names in cells because email addresses could be
different and my attachments are canned. This also allows the user to
view the email before it is sent to maybe add something extra in the
body. This was just a way to get the process started so the user
wouldn't have to go searching for the files to be added.

Option Explicit
Sub SendEmail()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim olMyApp As Outlook.Application
Dim olMyEmail As Outlook.MailItem

Set wkb = ThisWorkbook
Set wks = wkb.Worksheets("Sheet3")
Set rng = wks.Range("A2")
Set olMyApp = New Outlook.Application
Set olMyEmail = olMyApp.CreateItem(olMailItem)

olMyEmail.Subject = "Permit Application Package"
olMyEmail.Body = "Attached is a permit application package"
olMyEmail.Attachments.Add ("C:\Temp\testpic.png")
olMyEmail.Attachments.Add ("C:\My Documents\testlog.log")
olMyEmail.Display

Set olMyApp = Nothing
Set olMyEmail = Nothing
End Sub


The only thing I don't know yet is if everyone needs to have the
Microsoft Outlook reference added in VBE. Anyone care to elaborate?
Maybe have a simpler solution?

 

[Ron de Bruin]

With Outlook object model code you can use .Display instead of .Send