Email from Excel
 

Can you help?

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to open
Outlook with code and add the attachment. I don't want to
send or address the email - the user needs to do this.

I tried the following code but it seems to force me to
include the email address and automatically send it.
Don't know what I'm doing wrong. Thank you

Dim OLF As Outlook.MAPIFolder, olMailItem As
Outlook.MailItem
Dim ToContact As Outlook.Recipient
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace
("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail
message
With olMailItem
.Subject = "PDA Request" ' message subject
Set ToContact = .Recipients.Add("email
address.com") ' add a recipient
.Body = "test PDA request" & Chr(13)
' the message text with a line break
.Attachments.Add "P:\PDA\PDA Request shell.xlt",
olByValue, , _
"Attachment" ' insert attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olByReference, , _
"Shortcut to Attachment" ' insert shortcut
' .Attachments.Add "C:\FolderName\Filename.txt",
olEmbeddedItem, , _
"Embedded Attachment" ' embedded attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olOLE, , _
"OLE Attachment" ' OLE attachment
' .OriginatorDeliveryReportRequested = True '
delivery confirmation
' .ReadReceiptRequested = True ' read confirmation
' .Application
.Save ' saves the message for later editing
.Send ' sends the e-mail message (puts it in the
Outbox)
End With
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing

Email from Excel
 

Hi Cindy

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to open
Outlook with code and add the attachment. I don't want to
send or address the email - the user needs to do this.

the To line is "" and I use Display instead of Send

Sub Mail_workbook_Outlook()
'This example send the last saved version of the Activeworkbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
'In Excel 97 use ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Cindy" wrote in message ...
Can you help?

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to open
Outlook with code and add the attachment. I don't want to
send or address the email - the user needs to do this.

I tried the following code but it seems to force me to
include the email address and automatically send it.
Don't know what I'm doing wrong. Thank you

Dim OLF As Outlook.MAPIFolder, olMailItem As
Outlook.MailItem
Dim ToContact As Outlook.Recipient
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace
("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail
message
With olMailItem
.Subject = "PDA Request" ' message subject
Set ToContact = .Recipients.Add("email
address.com") ' add a recipient
.Body = "test PDA request" & Chr(13)
' the message text with a line break
.Attachments.Add "P:\PDA\PDA Request shell.xlt",
olByValue, , _
"Attachment" ' insert attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olByReference, , _
"Shortcut to Attachment" ' insert shortcut
' .Attachments.Add "C:\FolderName\Filename.txt",
olEmbeddedItem, , _
"Embedded Attachment" ' embedded attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olOLE, , _
"OLE Attachment" ' OLE attachment
' .OriginatorDeliveryReportRequested = True '
delivery confirmation
' .ReadReceiptRequested = True ' read confirmation
' .Application
.Save ' saves the message for later editing
.Send ' sends the e-mail message (puts it in the
Outbox)
End With
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing

Email from Excel
 

I found out how to do this on
http://www.rondebruin.nl/sendmail.htm

Awesome email examples. Thanks!

Cindy
-----Original Message-----
Can you help?

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to
open
Outlook with code and add the attachment. I don't want
to
send or address the email - the user needs to do this.

I tried the following code but it seems to force me to
include the email address and automatically send it.
Don't know what I'm doing wrong. Thank you

Dim OLF As Outlook.MAPIFolder, olMailItem As
Outlook.MailItem
Dim ToContact As Outlook.Recipient
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace
("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail
message
With olMailItem
.Subject = "PDA Request" ' message subject
Set ToContact = .Recipients.Add("email
address.com") ' add a recipient
.Body = "test PDA request" & Chr(13)
' the message text with a line break
.Attachments.Add "P:\PDA\PDA Request shell.xlt",
olByValue, , _
"Attachment" ' insert attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olByReference, , _
"Shortcut to Attachment" ' insert shortcut
' .Attachments.Add "C:\FolderName\Filename.txt",
olEmbeddedItem, , _
"Embedded Attachment" ' embedded attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olOLE, , _
"OLE Attachment" ' OLE attachment
' .OriginatorDeliveryReportRequested = True '
delivery confirmation
' .ReadReceiptRequested = True ' read confirmation
' .Application
.Save ' saves the message for later editing
.Send ' sends the e-mail message (puts it in the
Outbox)
End With
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing
.

Email from Excel
 

Thanks Ron,

I did find this in an article you must have written.

One more question - when using the activebook.fullname it
requires the workbook to first be saved. I cannot save
the workbook in code because this will be used by many
users and the paths are all different. I tried to trap the
error number but it doesn't remain the same. Any ideas
how I can "trap" that the document isn't saved and prompt
the user to save? Thanks

Cindy
-----Original Message-----
Hi Cindy

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to
open
Outlook with code and add the attachment. I don't want
to
send or address the email - the user needs to do this.

the To line is "" and I use Display instead of Send

Sub Mail_workbook_Outlook()
'This example send the last saved version of the
Activeworkbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
'In Excel 97 use ActiveWorkbook.Path & "\" &
ActiveWorkbook.Name
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Cindy" wrote in
message ...
Can you help?

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to
open
Outlook with code and add the attachment. I don't want
to
send or address the email - the user needs to do this.

I tried the following code but it seems to force me to
include the email address and automatically send it.
Don't know what I'm doing wrong. Thank you

Dim OLF As Outlook.MAPIFolder, olMailItem As
Outlook.MailItem
Dim ToContact As Outlook.Recipient
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace
("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail
message
With olMailItem
.Subject = "PDA Request" ' message subject
Set ToContact = .Recipients.Add("email
address.com") ' add a recipient
.Body = "test PDA request" & Chr(13)
' the message text with a line break
.Attachments.Add "P:\PDA\PDA Request shell.xlt",
olByValue, , _
"Attachment" ' insert attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olByReference, , _
"Shortcut to Attachment" ' insert shortcut
' .Attachments.Add "C:\FolderName\Filename.txt",
olEmbeddedItem, , _
"Embedded Attachment" ' embedded attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olOLE, , _
"OLE Attachment" ' OLE attachment
' .OriginatorDeliveryReportRequested = True '
delivery confirmation
' .ReadReceiptRequested = True ' read
confirmation
' .Application
.Save ' saves the message for later editing
.Send ' sends the e-mail message (puts it in the
Outbox)
End With
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing


.

Email from Excel
 

Hi Cindy

You can check it like this
If the workbook is not saved the path length is 0

ActiveWorkbook.Save
will prompt the user to save the file

If Len(ActiveWorkbook.Path) = 0 Then
ActiveWorkbook.Save
End If


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Cindy" wrote in message ...
Thanks Ron,

I did find this in an article you must have written.

One more question - when using the activebook.fullname it
requires the workbook to first be saved. I cannot save
the workbook in code because this will be used by many
users and the paths are all different. I tried to trap the
error number but it doesn't remain the same. Any ideas
how I can "trap" that the document isn't saved and prompt
the user to save? Thanks

Cindy
-----Original Message-----
Hi Cindy

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to
open
Outlook with code and add the attachment. I don't want
to
send or address the email - the user needs to do this.

the To line is "" and I use Display instead of Send

Sub Mail_workbook_Outlook()
'This example send the last saved version of the
Activeworkbook
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
'In Excel 97 use ActiveWorkbook.Path & "\" &
ActiveWorkbook.Name
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Cindy" wrote in
message ...
Can you help?

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to
open
Outlook with code and add the attachment. I don't want
to
send or address the email - the user needs to do this.

I tried the following code but it seems to force me to
include the email address and automatically send it.
Don't know what I'm doing wrong. Thank you

Dim OLF As Outlook.MAPIFolder, olMailItem As
Outlook.MailItem
Dim ToContact As Outlook.Recipient
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace
("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-mail
message
With olMailItem
.Subject = "PDA Request" ' message subject
Set ToContact = .Recipients.Add("email
address.com") ' add a recipient
.Body = "test PDA request" & Chr(13)
' the message text with a line break
.Attachments.Add "P:\PDA\PDA Request shell.xlt",
olByValue, , _
"Attachment" ' insert attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olByReference, , _
"Shortcut to Attachment" ' insert shortcut
' .Attachments.Add "C:\FolderName\Filename.txt",
olEmbeddedItem, , _
"Embedded Attachment" ' embedded attachment
' .Attachments.Add "C:\FolderName\Filename.txt",
olOLE, , _
"OLE Attachment" ' OLE attachment
' .OriginatorDeliveryReportRequested = True '
delivery confirmation
' .ReadReceiptRequested = True ' read
confirmation
' .Application
.Save ' saves the message for later editing
.Send ' sends the e-mail message (puts it in the
Outbox)
End With
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing


.

Email from Excel
 

Thanks for all your help!
-----Original Message-----
Hi Cindy

You can check it like this
If the workbook is not saved the path length is 0

ActiveWorkbook.Save
will prompt the user to save the file

If Len(ActiveWorkbook.Path) = 0 Then
ActiveWorkbook.Save
End If


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Cindy" wrote in
message ...
Thanks Ron,

I did find this in an article you must have written.

One more question - when using the activebook.fullname
it
requires the workbook to first be saved. I cannot save
the workbook in code because this will be used by many
users and the paths are all different. I tried to trap
the
error number but it doesn't remain the same. Any ideas
how I can "trap" that the document isn't saved and
prompt
the user to save? Thanks

Cindy
-----Original Message-----
Hi Cindy

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to
open
Outlook with code and add the attachment. I don't
want
to
send or address the email - the user needs to do
this.

the To line is "" and I use Display instead of Send

Sub Mail_workbook_Outlook()
'This example send the last saved version of the
Activeworkbook
'You must add a reference to the Microsoft outlook
Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
'In Excel 97 use ActiveWorkbook.Path & "\" &
ActiveWorkbook.Name
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Cindy" wrote in
message ...
Can you help?

via a command button, I need to be able to email the
workbook on the screen as an attachment. I'd like to
open
Outlook with code and add the attachment. I don't
want
to
send or address the email - the user needs to do
this.

I tried the following code but it seems to force me
to
include the email address and automatically send it.
Don't know what I'm doing wrong. Thank you

Dim OLF As Outlook.MAPIFolder, olMailItem As
Outlook.MailItem
Dim ToContact As Outlook.Recipient
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace
("MAPI").GetDefaultFolder(olFolderInbox)
Set olMailItem = OLF.Items.Add ' creates a new e-
mail
message
With olMailItem
.Subject = "PDA Request" ' message subject
Set ToContact = .Recipients.Add("email
address.com") ' add a recipient
.Body = "test PDA request" & Chr(13)
' the message text with a line break
.Attachments.Add "P:\PDA\PDA Request
shell.xlt",
olByValue, , _
"Attachment" ' insert attachment

' .Attachments.Add "C:\FolderName\Filename.txt",
olByReference, , _
"Shortcut to Attachment" ' insert
shortcut

' .Attachments.Add "C:\FolderName\Filename.txt",
olEmbeddedItem, , _
"Embedded Attachment" ' embedded
attachment

' .Attachments.Add "C:\FolderName\Filename.txt",
olOLE, , _
"OLE Attachment" ' OLE attachment
' .OriginatorDeliveryReportRequested = True '
delivery confirmation
' .ReadReceiptRequested = True ' read
confirmation
' .Application
.Save ' saves the message for later editing
.Send ' sends the e-mail message (puts it in
the
Outbox)
End With
Set ToContact = Nothing
Set olMailItem = Nothing
Set OLF = Nothing


.



.