Combobox1 is populated with sheet names. The user will
select a sheetname from the combobox.

What is the code that will attach the sheet that is in
combobox1 and bring up an outlook email dialog box. I
want the email dialog box to have the subject of "Stats"
and an empty body and To address field.

For example, the user clicks the combobox and selects the
value "Todd Huttenstine". When the code is run, it must
attach worksheet "Todd Huttenstine" to the email.

Below is the code I have so far, but I want my email
address taken out. When I take it out I get an error.

Can anyone please show me the modified code to perform the
task?



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

Thank you

Todd Huttenstine

Do you mean when you remove these lines of code

Set objOneRecip = objMessage.Recipients.Add
objOneRecip.Name = "
objOneRecip.Type = 1
objOneRecip.Resolve
objMessage.Send showDialog:=True
objSession.Logoff

You get an error?

--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
Combobox1 is populated with sheet names. The user will
select a sheetname from the combobox.

What is the code that will attach the sheet that is in
combobox1 and bring up an outlook email dialog box. I
want the email dialog box to have the subject of "Stats"
and an empty body and To address field.

For example, the user clicks the combobox and selects the
value "Todd Huttenstine". When the code is run, it must
attach worksheet "Todd Huttenstine" to the email.

Below is the code I have so far, but I want my email
address taken out. When I take it out I get an error.

Can anyone please show me the modified code to perform the
task?



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

Thank you

Todd Huttenstine

No when I remove
the " out of that
line and only put "".


-----Original Message-----
Do you mean when you remove these lines of code

Set objOneRecip = objMessage.Recipients.Add
objOneRecip.Name = "
objOneRecip.Type = 1
objOneRecip.Resolve
objMessage.Send showDialog:=True
objSession.Logoff

You get an error?

--
Regards,
Tom Ogilvy


Todd Huttenstine
wrote in message
...
Combobox1 is populated with sheet names. The user will
select a sheetname from the combobox.

What is the code that will attach the sheet that is in
combobox1 and bring up an outlook email dialog box. I
want the email dialog box to have the subject of "Stats"
and an empty body and To address field.

For example, the user clicks the combobox and selects
the
value "Todd Huttenstine". When the code is run, it must
attach worksheet "Todd Huttenstine" to the email.

Below is the code I have so far, but I want my email
address taken out. When I take it out I get an error.

Can anyone please show me the modified code to perform
the
task?



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

Thank you

Todd Huttenstine



.

That isn't how you would leave the to address blank. You would need to at
least remove/comment out the lines

Set objOneRecip = objMessage.Recipients.Add
objOneRecip.Name = "
objOneRecip.Type = 1
objOneRecip.Resolve

--
Regards,
Tom Ogilvy



Todd uttenstine wrote in message
...
No when I remove
the " out of that
line and only put "".


-----Original Message-----
Do you mean when you remove these lines of code

Set objOneRecip = objMessage.Recipients.Add
objOneRecip.Name = "
objOneRecip.Type = 1
objOneRecip.Resolve
objMessage.Send showDialog:=True
objSession.Logoff

You get an error?

--
Regards,
Tom Ogilvy


Todd Huttenstine
wrote in message
...
Combobox1 is populated with sheet names. The user will
select a sheetname from the combobox.

What is the code that will attach the sheet that is in
combobox1 and bring up an outlook email dialog box. I
want the email dialog box to have the subject of "Stats"
and an empty body and To address field.

For example, the user clicks the combobox and selects
the
value "Todd Huttenstine". When the code is run, it must
attach worksheet "Todd Huttenstine" to the email.

Below is the code I have so far, but I want my email
address taken out. When I take it out I get an error.

Can anyone please show me the modified code to perform
the
task?



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

Thank you

Todd Huttenstine



.

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

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

it worked but the file is not an Excel file and when I
opened it it does not work.
-----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


.

ActiveWorkbook.SaveAs "C:\" & sStr & ".xls", _
FileFormat:=xlWorkbookNormal

saves it as a workbook if you are in Excel and xlWorkbookNormal is a defined
constant. If not, try changing the argument to its constant value
? xlworkbooknormal
-4143


--
Regards,
Tom Ogilvy

Todd Huttenstine wrote in message
...
it worked but the file is not an Excel file and when I
opened it it does not work.
-----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


.

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


.