Help sending active sheet to email list

  • Thread starter Thread starter Kingtriotn
  • Start date Start date
K

Kingtriotn

I am trying to send an active sheet to a set of different email
address's and I keep geting error messages. Here is the code I am
trying to use

Sub mail()

Dim olApp As Outlook.Application
Dim olMail As MailItem

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

ActiveSheet.Copy

ActiveSheet.SaveAs ActiveSheet.Path & "\" & _
"Sheet2.xls"

With olMail
..Recipients.Add "(e-mail address removed)"
..Recipients.Add "(e-mail address removed)"
..Recipients.Add "(e-mail address removed)"
..Subject = "Weekly Recap"
..Body = "Here is this weeks recap"
..Attachments.Add ActiveSheet.FullName
..Display
End With

ActiveSheet.Close False

Kill Sheet.Path & "\" & "Sheet2.xls"

Set olMail = Nothing
Set olApp = Nothing

End Sub
I am really new to Macro writing so I am sure I am doing this all
wrong. Am I at least in the ballpark? Another problem I may face is
that the active sheet that I want to send gets data from other sheets
so I need to find a way to send it witout error messages in the fields
that data from other sheets goes.
Is there hope?
Thanks in advance for any help
 
Thanks, I tried the mail one sheet example and it works fine from home.
I then emailed the workbook that I am developing to work and when
tried to run it, I get an error that higlights the body text when
debug. The error message says somthing to the tune of bad sendmai
type. Any ideas? All of my other macros worked fine on the emaile
version.
Thanks in advance,
Kingtrito
 
this is the example that I used.

Sub Mail_ActiveSheet()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
..SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
..SendMail "(e-mail address removed)", _
"This is the Subject line"
..ChangeFileAccess xlReadOnly
Kill .FullName
..Close False
End With
Application.ScreenUpdating = True
End Sub
The part where the subject goes is what is getting highlighted in
debuger.
Thanks
 
Look at the example on my website again you see that there is a dot before
SendMail and other lines to refer to WB.
Post back if you got problems

Sub Mail_ActiveSheet()
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail "(e-mail address removed)", _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub
 
Back
Top