Conditional Sheet Attachment
I made the following changes to the code and now it works.
Thank you for your help.
Dim sStr As String
Dim objAttachmt As Object
sStr = ComboBox1.Value
Worksheets(sStr).Copy
ActiveWorkbook.SaveAs "C:\Stats.xls", _
FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close SaveChanges:=False
Dim objSession As Object, objMessage As Object,
objOneRecip As Object
Set objSession = CreateObject("MAPI.Session")
objSession.Logon
Set objMessage = objSession.Outbox.Messages.Add
objMessage.Subject = "Stats"
objMessage.Text = ""
'Set objOneRecip = objMessage.Recipients.Add
'objOneRecip.Name = "
'objOneRecip.Type = 1
'objOneRecip.Resolve
'objMessage.Attachments.Add "C:\Stats.xls"
Set objAttachmt = objMessage.Attachments.Add
objAttachmt.Source = ("C:\Stats.xls")
objMessage.Send showDialog:=True
objSession.Logoff
On Error Resume Next
Kill "C:\Stats.xls"
On Error GoTo 0
-----Original Message-----
I will have to assume that objMessage has an Attachments
collection:
If so, it would be something like:
Option Explicit
Private Sub CommandButton1_Click()
Dim sStr as String
sStr = Listbox1.Value
worksheets(sStr).Copy
ActiveWorkbook.SaveAs "C:\" & sStr & ".xls", _
FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close SaveChanges:=False
Dim objSession As Object, objMessage As Object,
objOneRecip As Object
Set objSession = CreateObject("MAPI.Session")
objSession.Logon
Set objMessage = objSession.Outbox.Messages.Add
objMessage.Subject = "Stats"
objMessage.Text = ""
'Set objOneRecip = objMessage.Recipients.Add
'objOneRecip.Name = "
'objOneRecip.Type = 1
'objOneRecip.Resolve
objMessage.Attachments.Add "C:\" & sStr & ".xls"
objMessage.Send showDialog:=True
objSession.Logoff
On Error Resume Next
kill "C:\" & sStr & ".xls"
On Error goto 0
End Sub
--
Regards,
Tom Ogilvy
Todd Huttenstine
wrote in message
...
Tom, that worked. Thank you. Below is the modified
code.
Combobox1 contains sheet names. I need for it to look
in
Combobox1, and attach that sheet to the email. How
would
I do this? For example the value "Todd Huttenstine" is
in
combobox1. Therefore I need for it to attach
sheet "Todd
Huttenstine" to the email.
Option Explicit
Private Sub CommandButton1_Click()
Dim objSession As Object, objMessage As Object,
objOneRecip As Object
Set objSession = CreateObject("MAPI.Session")
objSession.Logon
Set objMessage = objSession.Outbox.Messages.Add
objMessage.Subject = "Stats"
objMessage.Text = ""
'Set objOneRecip = objMessage.Recipients.Add
'objOneRecip.Name
= "
'objOneRecip.Type = 1
'objOneRecip.Resolve
objMessage.Send showDialog:=True
objSession.Logoff
End Sub
.
|