Macro to send email from excel

Posted by Greg on November 01, 2001 3:38 PM

I want to have a button on my sheet in excel, such that when activated, an email message will pop up to send to a certain address with the same body paragraph and subject. What is the best way of going about this?

Thanks for your help!

Posted by Tom Urtis on November 01, 2001 4:21 PM

Here's an Outlook email macro

Greg,

Here is a comprehensive and tested email code using Outlook, set up within the context of a VB Yes, No, Cancel button (if you want to review, send, or cancel), and a password (so not just anyone can email the file). You can assign this code to a command button or extricate the portion(s) you want and place into your existing code. Modify body text and password as desired.

Tom Urtis

Sub Email()
Macro by Thomas Urtis

'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 &LT;&GT; "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

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