Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Send email from Excel through Outlook 97 (with or without attachments)
VBA How To
To send an email from Excel through Outlook 97 using VBA, the following code should be a good starting point:
[color blue]
[/color]
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!
VBAjedi![[swords]](/data/assets/smilies/swords.gif "[swords] [swords]")
[color blue]
Code:
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.
.send
End With
Set App = Nothing
Set Itm = Nothing
End SubHope 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!
VBAjedi