[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

send excel by email

Sérgio Aires

12/12/2006 10:47:00 AM

hi
I'm trying to send a sheet by email, but I want to send just a sheet. I'm
using this code:
ActiveWorkbook.SendMail Recipients:="sergio.aires@tecnovia.pt"

but with this code all file is send.
how can I do for to send just a sheet??

thanks

--
Sérgio Aires
Lisboa
Portugal
6 Answers

Madhan

12/12/2006 12:01:00 PM

0

Hi,
A sheet is not a standalone object (otherwise, Microsoft would have
provided objects at operating system level to recognise them). It exists
within the workbook object, hence, you may not be able to send only worksheet
in an e-mail.

"Sérgio Aires" wrote:

> hi
> I'm trying to send a sheet by email, but I want to send just a sheet. I'm
> using this code:
> ActiveWorkbook.SendMail Recipients:="sergio.aires@tecnovia.pt"
>
> but with this code all file is send.
> how can I do for to send just a sheet??
>
> thanks
>
> --
> Sérgio Aires
> Lisboa
> Portugal

Duncan

12/12/2006 12:08:00 PM

0

Application.DisplayAlerts = False
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("Sheet2").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "onesheet.xls"
.ChangeFileAccess xlReadOnly
End With
Const cdoSendUsingPickup = 1 'Send message using the local SMTP service
pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP
over the network).
Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "A08 Processing Form"
objMessage.From = """Automatic Spreadsheet Submission""
<NotReal@citb.co.uk>"
objMessage.To = "grant.claimforms@citb.co.uk"
objMessage.TextBody = "Please find attached an A08 Processing Form"
objMessage.AddAttachment wb.FullName
'==This section provides the configuration information for the remote
SMTP server.
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendu...)
= 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpse...)
= "100.1.120.2"
'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti...)
= cdoBasic
'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/senduser...)
= ""
'Your password on the SMTP server
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpass...)
= ""
'Server port (typically 25)
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver...)
= 25
'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpus...)
= False
'Connection Timeout in seconds (the maximum time CDO will try to
establish a connection to the SMTP server)
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontim...)
= 60
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
objMessage.Send
wb.Close (False)
Application.DisplayAlerts = True
Application.ScreenUpdating = True



Madhan wrote:
> Hi,
> A sheet is not a standalone object (otherwise, Microsoft would have
> provided objects at operating system level to recognise them). It exists
> within the workbook object, hence, you may not be able to send only worksheet
> in an e-mail.
>
> "Sérgio Aires" wrote:
>
> > hi
> > I'm trying to send a sheet by email, but I want to send just a sheet. I'm
> > using this code:
> > ActiveWorkbook.SendMail Recipients:="sergio.aires@tecnovia.pt"
> >
> > but with this code all file is send.
> > how can I do for to send just a sheet??
> >
> > thanks
> >
> > --
> > Sérgio Aires
> > Lisboa
> > Portugal

Madhan

12/12/2006 12:20:00 PM

0

Hi, the code written by Duncan, still does what I have specified, that is, he
creates a workbook containing a single sheet and then sends it via e-mail.
This is just an observation and Mr.Duncan is requested to treat it as such.
Thanks.

"Duncan" wrote:

> Application.DisplayAlerts = False
> Dim wb As Workbook
> Dim strdate As String
> strdate = Format(Now, "dd-mm-yy h-mm-ss")
> Application.ScreenUpdating = False
> Sheets("Sheet2").Copy
> Set wb = ActiveWorkbook
> With wb
> .SaveAs "onesheet.xls"
> .ChangeFileAccess xlReadOnly
> End With
> Const cdoSendUsingPickup = 1 'Send message using the local SMTP service
> pickup directory.
> Const cdoSendUsingPort = 2 'Send the message using the network (SMTP
> over the network).
> Const cdoAnonymous = 0 'Do not authenticate
> Const cdoBasic = 1 'basic (clear-text) authentication
> Const cdoNTLM = 2 'NTLM
> Set objMessage = CreateObject("CDO.Message")
> objMessage.Subject = "A08 Processing Form"
> objMessage.From = """Automatic Spreadsheet Submission""
> <NotReal@citb.co.uk>"
> objMessage.To = "grant.claimforms@citb.co.uk"
> objMessage.TextBody = "Please find attached an A08 Processing Form"
> objMessage.AddAttachment wb.FullName
> '==This section provides the configuration information for the remote
> SMTP server.
> objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendu...)
> = 2
> 'Name or IP of Remote SMTP Server
> objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpse...)
> = "100.1.120.2"
> 'Type of authentication, NONE, Basic (Base64 encoded), NTLM
> objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti...)
> = cdoBasic
> 'Your UserID on the SMTP server
> objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/senduser...)
> = ""
> 'Your password on the SMTP server
> objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpass...)
> = ""
> 'Server port (typically 25)
> objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver...)
> = 25
> 'Use SSL for the connection (False or True)
> objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpus...)
> = False
> 'Connection Timeout in seconds (the maximum time CDO will try to
> establish a connection to the SMTP server)
> objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontim...)
> = 60
> objMessage.Configuration.Fields.Update
> '==End remote SMTP server configuration section==
> objMessage.Send
> wb.Close (False)
> Application.DisplayAlerts = True
> Application.ScreenUpdating = True
>
>
>
> Madhan wrote:
> > Hi,
> > A sheet is not a standalone object (otherwise, Microsoft would have
> > provided objects at operating system level to recognise them). It exists
> > within the workbook object, hence, you may not be able to send only worksheet
> > in an e-mail.
> >
> > "Sérgio Aires" wrote:
> >
> > > hi
> > > I'm trying to send a sheet by email, but I want to send just a sheet. I'm
> > > using this code:
> > > ActiveWorkbook.SendMail Recipients:="sergio.aires@tecnovia.pt"
> > >
> > > but with this code all file is send.
> > > how can I do for to send just a sheet??
> > >
> > > thanks
> > >
> > > --
> > > Sérgio Aires
> > > Lisboa
> > > Portugal
>
>

Duncan

12/12/2006 12:42:00 PM

0

Hi Madhan,

I didnt intend to post that code and tried to retract it, I apologise
if you think I am undermining your answer I was merely going to offer a
solution to the problem faced.

Duncan

Madhan wrote:
> Hi, the code written by Duncan, still does what I have specified, that is, he
> creates a workbook containing a single sheet and then sends it via e-mail.
> This is just an observation and Mr.Duncan is requested to treat it as such.
> Thanks.
>
> "Duncan" wrote:
>
> > Application.DisplayAlerts = False
> > Dim wb As Workbook
> > Dim strdate As String
> > strdate = Format(Now, "dd-mm-yy h-mm-ss")
> > Application.ScreenUpdating = False
> > Sheets("Sheet2").Copy
> > Set wb = ActiveWorkbook
> > With wb
> > .SaveAs "onesheet.xls"
> > .ChangeFileAccess xlReadOnly
> > End With
> > Const cdoSendUsingPickup = 1 'Send message using the local SMTP service
> > pickup directory.
> > Const cdoSendUsingPort = 2 'Send the message using the network (SMTP
> > over the network).
> > Const cdoAnonymous = 0 'Do not authenticate
> > Const cdoBasic = 1 'basic (clear-text) authentication
> > Const cdoNTLM = 2 'NTLM
> > Set objMessage = CreateObject("CDO.Message")
> > objMessage.Subject = "A08 Processing Form"
> > objMessage.From = """Automatic Spreadsheet Submission""
> > <NotReal@citb.co.uk>"
> > objMessage.To = "grant.claimforms@citb.co.uk"
> > objMessage.TextBody = "Please find attached an A08 Processing Form"
> > objMessage.AddAttachment wb.FullName
> > '==This section provides the configuration information for the remote
> > SMTP server.
> > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendu...)
> > = 2
> > 'Name or IP of Remote SMTP Server
> > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpse...)
> > = "100.1.120.2"
> > 'Type of authentication, NONE, Basic (Base64 encoded), NTLM
> > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti...)
> > = cdoBasic
> > 'Your UserID on the SMTP server
> > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/senduser...)
> > = ""
> > 'Your password on the SMTP server
> > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpass...)
> > = ""
> > 'Server port (typically 25)
> > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver...)
> > = 25
> > 'Use SSL for the connection (False or True)
> > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpus...)
> > = False
> > 'Connection Timeout in seconds (the maximum time CDO will try to
> > establish a connection to the SMTP server)
> > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontim...)
> > = 60
> > objMessage.Configuration.Fields.Update
> > '==End remote SMTP server configuration section==
> > objMessage.Send
> > wb.Close (False)
> > Application.DisplayAlerts = True
> > Application.ScreenUpdating = True
> >
> >
> >
> > Madhan wrote:
> > > Hi,
> > > A sheet is not a standalone object (otherwise, Microsoft would have
> > > provided objects at operating system level to recognise them). It exists
> > > within the workbook object, hence, you may not be able to send only worksheet
> > > in an e-mail.
> > >
> > > "Sérgio Aires" wrote:
> > >
> > > > hi
> > > > I'm trying to send a sheet by email, but I want to send just a sheet. I'm
> > > > using this code:
> > > > ActiveWorkbook.SendMail Recipients:="sergio.aires@tecnovia.pt"
> > > >
> > > > but with this code all file is send.
> > > > how can I do for to send just a sheet??
> > > >
> > > > thanks
> > > >
> > > > --
> > > > Sérgio Aires
> > > > Lisboa
> > > > Portugal
> >
> >

Duncan

12/12/2006 12:46:00 PM

0

P.s, just a suggestion:

Sergio says - "how can I do for to send just a sheet?? "

Sergio asks for a way to do this, not an answer telling him how it isnt
possible, I was only offering such so please try to be polite and not
take it as a personal insult.

Duncan


Duncan wrote:
> Hi Madhan,
>
> I didnt intend to post that code and tried to retract it, I apologise
> if you think I am undermining your answer I was merely going to offer a
> solution to the problem faced.
>
> Duncan
>
> Madhan wrote:
> > Hi, the code written by Duncan, still does what I have specified, that is, he
> > creates a workbook containing a single sheet and then sends it via e-mail.
> > This is just an observation and Mr.Duncan is requested to treat it as such.
> > Thanks.
> >
> > "Duncan" wrote:
> >
> > > Application.DisplayAlerts = False
> > > Dim wb As Workbook
> > > Dim strdate As String
> > > strdate = Format(Now, "dd-mm-yy h-mm-ss")
> > > Application.ScreenUpdating = False
> > > Sheets("Sheet2").Copy
> > > Set wb = ActiveWorkbook
> > > With wb
> > > .SaveAs "onesheet.xls"
> > > .ChangeFileAccess xlReadOnly
> > > End With
> > > Const cdoSendUsingPickup = 1 'Send message using the local SMTP service
> > > pickup directory.
> > > Const cdoSendUsingPort = 2 'Send the message using the network (SMTP
> > > over the network).
> > > Const cdoAnonymous = 0 'Do not authenticate
> > > Const cdoBasic = 1 'basic (clear-text) authentication
> > > Const cdoNTLM = 2 'NTLM
> > > Set objMessage = CreateObject("CDO.Message")
> > > objMessage.Subject = "A08 Processing Form"
> > > objMessage.From = """Automatic Spreadsheet Submission""
> > > <NotReal@citb.co.uk>"
> > > objMessage.To = "grant.claimforms@citb.co.uk"
> > > objMessage.TextBody = "Please find attached an A08 Processing Form"
> > > objMessage.AddAttachment wb.FullName
> > > '==This section provides the configuration information for the remote
> > > SMTP server.
> > > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendu...)
> > > = 2
> > > 'Name or IP of Remote SMTP Server
> > > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpse...)
> > > = "100.1.120.2"
> > > 'Type of authentication, NONE, Basic (Base64 encoded), NTLM
> > > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenti...)
> > > = cdoBasic
> > > 'Your UserID on the SMTP server
> > > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/senduser...)
> > > = ""
> > > 'Your password on the SMTP server
> > > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpass...)
> > > = ""
> > > 'Server port (typically 25)
> > > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver...)
> > > = 25
> > > 'Use SSL for the connection (False or True)
> > > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpus...)
> > > = False
> > > 'Connection Timeout in seconds (the maximum time CDO will try to
> > > establish a connection to the SMTP server)
> > > objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontim...)
> > > = 60
> > > objMessage.Configuration.Fields.Update
> > > '==End remote SMTP server configuration section==
> > > objMessage.Send
> > > wb.Close (False)
> > > Application.DisplayAlerts = True
> > > Application.ScreenUpdating = True
> > >
> > >
> > >
> > > Madhan wrote:
> > > > Hi,
> > > > A sheet is not a standalone object (otherwise, Microsoft would have
> > > > provided objects at operating system level to recognise them). It exists
> > > > within the workbook object, hence, you may not be able to send only worksheet
> > > > in an e-mail.
> > > >
> > > > "Sérgio Aires" wrote:
> > > >
> > > > > hi
> > > > > I'm trying to send a sheet by email, but I want to send just a sheet. I'm
> > > > > using this code:
> > > > > ActiveWorkbook.SendMail Recipients:="sergio.aires@tecnovia.pt"
> > > > >
> > > > > but with this code all file is send.
> > > > > how can I do for to send just a sheet??
> > > > >
> > > > > thanks
> > > > >
> > > > > --
> > > > > Sérgio Aires
> > > > > Lisboa
> > > > > Portugal
> > >
> > >

Ron de Bruin

12/12/2006 3:30:00 PM

0

See this page for a code example
http://www.rondebruin.nl/se...

--

Regards Ron de Bruin
http://www.rondebruin.n...


"Sérgio Aires" <SrgioAires@discussions.microsoft.com> wrote in message news:5203D33A-B08A-4A51-A420-D4656AB3B7F6@microsoft.com...
> hi
> I'm trying to send a sheet by email, but I want to send just a sheet. I'm
> using this code:
> ActiveWorkbook.SendMail Recipients:="sergio.aires@tecnovia.pt"
>
> but with this code all file is send.
> how can I do for to send just a sheet??
>
> thanks
>
> --
> Sérgio Aires
> Lisboa
> Portugal