Sending an email with the subject and the recipient filled i

[richrr31]

I know how to send an email automatically with a macro to a certain email address(or array of email addresses), with the subject filled out. I also know how to create a macro that will pop up the email window so that someone can enter an email address, message, subject, and then send the spreadsheet. Is there a way to pop up the window with the To and Subject fields filled out, but still allow the user to enter a message before sending the spreadsheet?

 

[lenze]

Here is a routine I use for this task. It was written by Tom Urtis and posted here on the old board

Sub Email()

'Input box to verify password

Dim myPassword As String

myPassword = InputBox(prompt:="Please enter the password to proceed:", _
Title:="Password is required to auto-email this file.")

If myPassword <> "Password" Then
MsgBox prompt:="Click OK to return to Report.", _
Title:="Cancelled -- correct password not entered", _
Buttons:=16

Else
Dim Resp As Integer
Resp = MsgBox(prompt:="Click Yes to review email, No to immediately send, or Cancel.", _
Title:="Email options: Want to review email before sending?", _
Buttons:=3 + 32)

End If

Select Case Resp

'Yes was clicked, user wants to review email first
Case Is = 6
Dim myOutlook As Object
Dim myMailItem As Object

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "" & ActiveWorkbook.Name

With otlNewMail
.To = "JohnDoe@anywhere.com; JaneDoe@anyplace.com"
.CC = "MarySmith@anywho.com"
.Subject = "Email from me"
.Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Greg" & Chr(13) & Chr(13)
.Attachments.Add fName
.Display

End With


Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing


'If no is clicked
Case Is = 7
Dim myOutlok As Object
Dim myMailItm As Object

Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "" & ActiveWorkbook.Name

With otlNewMail
.To = "JohnDoe@anywhere.com; JaneDoe@anyplace.com"
.CC = "MarySmith@anywho.com"
.Subject = "Email from me"
.Body = "Attached is today's Report." & Chr(13) & "Regards," & Chr(13) & "Greg" & Chr(13) & Chr(13)
.Attachments.Add fName
.Send

End With

otlApp.Quit

Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing


'If Cancel is clicked
Case Is = 2
MsgBox prompt:="Click OK to return to Report.", _
Title:="EMAIL CANCELLED", _
Buttons:=64

End Select

End Sub

 

[richrr31]

thanks!