Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



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!


Check out our Excel VBA Resources

Here's an Outlook email macro

Posted by Tom Urtis on November 01, 2001 4:21 PM
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




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.