Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

Send email from Excel through Outlook 97 (with or without attachments) by VBAjedi
Posted: 9 May 03 (Edited 6 Nov 03)

To send an email from Excel through Outlook 97 using VBA, the following code should be a good starting point:

Sub SendEmail()

ESubject = "This is a test email"
SendTo = "Some_Address@ADomain.com"
CCTo = "Different_Address@ADomain.com"
Ebody = "Testing VBA's ability to send an email."
NewFileName = "C:\My Documents\TestFile.xls"

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo
.CC = CCTo
.Body = Ebody
'.Attachments.Add (NewFileName) ' Must be complete path
'.Display ' This property is used when you want
' the user to see email and manually send. Then
' comment out rest of code except ôEnd Withö statement
' and "End Sub" statement.
End With
Set App = Nothing
Set Itm = Nothing
End Sub

Hope this was helpful! Note that after Outlook 97 Microsoft added anti-virus measures that generate a popup confirmation box when you generate an email using this method. There's no really easy way around it, but some workarounds have been found (most notably a little program called Redemption that must be installed on each users PC). Search these forums or Google for "Outlook security prompt" for more information.

Good luck!


Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close