Email from Excel

[Cindy]

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

 

[Ron de Bruin]

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

 

[Cindy]

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

Awesome email examples. Thanks!

Cindy

 

[Cindy]

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


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)




"Cindy" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

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

 

[Cindy]

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)




"Cindy" <[email protected]> wrote in

message news:[email protected]...