Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VB code to send Mail from Excel?

  Asked By: Bonni    Date: Nov 01    Category: MS Office    Views: 28006
  

Is there some VB code that I can use to incorporate into some existing
code which formulate a Workbook and then save the report on my C
Drive.

The new code needs to send a mail from with in Excel to 2 persons and
CC another 2 persons with the subject line "VaF Reports", with some
text in the body of the mail, and attaches the Excel file that was
just stored on my C Drive.

I'm using Excel 2000 on Win XP and MS Outlook 2000.

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Aaminah Khan     Answered On: Nov 01

These links have code  that will send  emails from inside Excel. They are
slightly different variations on how to send it, but either one should work
for you.

www.vba-programmer.com/.../Automating_Outlook.txt
http://www.tek-tips.com/faqs.cfm?fid=4334

Hopefully, that helps you out.

 
Answer #2    Answered By: Anne Powell     Answered On: Nov 01

I don't use XL2000, so I'm not sure the code  I use will work. You will need
to add a reference in the VBE to the Outlook object model version you are
using. There are many excellent examples of code that will help you do what
you describe at Ron de Bruin's web site http://www.rondebruin.nl/tips.htm
and http://www.rondebruin.nl/sendmail.htm

 
Answer #3    Answered By: Tate Thompson     Answered On: Nov 01

Here's the code  I use in XL 2003 with Outlook 2003. This is a "one button"
solution for me . . . it creates a subject, body  text, email addresses,
etc., from information in the workbook. I usually display the email and
then send  to avoid the security warnings and to provide an opportunity to
add any changes to the body text. 99% of the time, I can just click send
and be done.

Sub btnEmail_Click()
' btnEmailReport_Click 12/13/2004 by Rick Teale
' Saves a copy of the CapJob-Master.xls and renames it to the Plant & Job
Title
' New workbook  is attached to an email and sent to Plant Manager.
' You must add a reference to the Microsoft Outlook 11.0 Object Library
'
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim txtSubject As String
Dim txtJobNumber As String
Dim txtPlant As String
Dim txtPltJobNumber As String
Dim txtJobTitle As String
Dim txtSupr As String
Dim txtPlantName As String
Dim txtBodyText As String

Application.ScreenUpdating = False
txtJobTitle = Range("JobTitle").Value
txtJobNumber = Range("JobNumber").Value
txtPlant = Left(Range("JobNumber"), 3)
txtPltJobNumber = Right(Range("JobNumber"), 3) & " "
If txtPlant = "100" Then
txtPlantName = "Plant 100 "
txtSupr = "Supr100@..."
Else: txtPlantName = "200 ": txtSupr = "Supr200@..."
End If
' Set up file  name variable based on Plant Job Vs. Capital Job . . .
If Range("PltJob").Value = "" Then
txtJobFileName = txtPlant & "-" & txtJobTitle & ".xls"
txtBodyText = "George, " & vbCrLf & vbCrLf & "Attached for
routing approval is " & txtPlantName & "Capital Job " & Chr(34) &
txtJobTitle & Chr(34) & "." & vbCrLf & vbCrLf & "Rick"
txtSubject = txtPlant & " Capital Job for Routing"
Else: txtJobFileName = txtPlant & "-" & txtPltJobNumber & txtJobTitle &
".xls"
txtBodyText = "George, " & vbCrLf & vbCrLf & "Attached for
routing approval is " & txtPlantName & "Plant Job " & Chr(34) & txtJobTitle
& Chr(34) & "." & vbCrLf & vbCrLf & "Rick"
txtSubject = txtPlant & " Plant Job for Routing"
End If
Set wb = ActiveWorkbook
With wb
.UpdateLinks = xlUpdateLinksNever
.SaveAs "C:\CAP JOBS\2004\" & txtPlant & "\" & txtJobFileName
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "George@..."
.CC = txtSupr
.BCC = "rteale@..."
.Subject = txtSubject
.Body = txtBodyText
.Attachments.Add wb.FullName
'.Send 'or use .Display
.Display 'for debug
End With
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing

End Sub

 
Answer #4    Answered By: Charlie Evans     Answered On: Nov 01

I didn't get as far as to make it to work though, but will keep on
trying.

 
Answer #5    Answered By: Jawna Mohammad     Answered On: Nov 01

This is what I came up with, and what do you know?

It even works asit should.

Rick, as you can see I used some of your code, however modified and
added some code.


Sub Send_Mail()
'
Dim wb As Workbook
Dim txtSubject As String
Dim txtBodyText As String
Application.Visible = False
Application.ScreenUpdating = False

On Error GoTo ZZ:

Set wb = ActiveWorkbook
With wb
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "<E-mail Address>"
.CC = ""
.BCC = ""
.Subject = "VaF 2nd Report"
.Body = "Colleague, " & vbCrLf & vbCrLf & "<Body Text in Here>" &
vbCrLf & vbCrLf & "With Kind Regards" & vbCrLf & vbCrLf & "Johan"
.Attachments.Add "C:\SAP\<filename>.xls"
.Display
Application.SendKeys "%S"
End With
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True

Exit Sub
ZZ:
D = MsgBox("The file  to be attached could not be found, " & vbCr &
"please check and try again.", 16, "")
If D = vbOK Then
Application.DisplayAlerts = False
Application.Quit
End If
End Sub

 
Didn't find what you were looking for? Find more on VB code to send Mail from Excel? Or get search suggestion and latest updates.




Tagged: