PDA

View Full Version : Email Selection



Djblois
05-03-2007, 12:43 PM
I use code to send out the Activeworkbook or the activeworksheet depending on what the user wants but now I am trying to set it up to send only what the user has selected. Preferably in the email if possible, if not as an attachment. here is the code I am trying:


Dim OL As Object
Dim EmailItem As Object
Dim FileName As String
Dim y As Long
Dim TempChar As String
Dim SaveName As String

Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(0)
FileName = ActiveSheet.Name & " - " & ActiveWorkbook.Name
For y = 1 To Len(FileName)
TempChar = Mid(FileName, y, 1)
Select Case TempChar
Case Is = "/", "\", "*", "?", """", "<", ">", "|", ":"
Case Else
SaveName = SaveName & TempChar
End Select
Next y
Selection.Copy
Set WB(1) = ActiveWorkbook
WB(1).SaveAs SaveName
WB(1).ChangeFileAccess xlReadOnly
With EmailItem
.Attachments.Add WB(1).FullName
.Display
End With
Kill WB(1).FullName
WB(1).Close False

Djblois
05-03-2007, 12:51 PM
and one other question for emailing purposes. All the code I use will crash if the program has never been saved before. So I tried this code:


If ThisWorkbook.Saved = False Then
Error00_01_04.Show
End If

and Error00_01_04 shows up all the time. Even if the workbook has been saved before?

lucas
05-03-2007, 01:11 PM
Here's a link to John's stab at a similar task:
http://vbaexpress.com/kb/getarticle.php?kb_id=880

Djblois
05-03-2007, 01:29 PM
Thank you steve but I alread have the Active workbook and the Active Worksheet ones working (With help form the kb here and gurus like yourself) I am trying to make it even more useful by allowing the user to be able to select a few cells and then email just what the user selected and also I have the problem if the workbook has never been saved before?

Ivan F Moala
05-04-2007, 03:59 AM
Have a look here

http://www.mrexcel.com/board2/viewtopic.php?t=8251

lucas
05-04-2007, 09:09 AM
Set 2 references....change the path in a couple of places...works great Ivan....thanks

creates an html file which you can save or delete.