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 <> "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
