Hi,

I am using this Code for sending mail from Excel especially the from a
rage to a particular range.

Option Explicit


Private Sub CommandButton1_Click()
'Working in 2000-2007
Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Set Source = Nothing
On Error Resume Next
Set Source = Range("A1:P500").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Source Is Nothing Then
MsgBox "The source is not a range or the sheet is protected,
please correct and try again.", vbOKOnly
Exit Sub
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)

Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With

TempFilePath = Environ$("temp") & "\"
TempFileName = "Selection of " & wb.Name & " " & Format(Now,
"dd-mmm-yy h-mm-ss")

If Val(Application.Version) < 12 Then
'You use Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
End If

With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", _
"Coil Schedule"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


I want that as soon as i click on the button a window of outlook should
open with the specified email address in the TO seciton. Suppose i want
(E-Mail Removed) should comes autometically in the To Column.

what should i add in the code to do that.

Thanks in Advance.

Regards

Akash Maheshwari

Here is some code to maile using Outlook. You haver to save your workbokk to
the HDD and link it to the email from there.


Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object


Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True


Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add("(E-Mail Removed) ")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "The extract has finished."
.Body = "This is an automatic email notification"
' .Attachments.Add ("filename") 'you only need this if
'you are sending attachments?
.Display 'use .Send when all testing done
End With


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Akash" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> I am using this Code for sending mail from Excel especially the from a
> rage to a particular range.
>
> Option Explicit
>
>
> Private Sub CommandButton1_Click()
> 'Working in 2000-2007
> Dim Source As Range
> Dim Dest As Workbook
> Dim wb As Workbook
> Dim TempFilePath As String
> Dim TempFileName As String
> Dim FileExtStr As String
> Dim FileFormatNum As Long
>
> Set Source = Nothing
> On Error Resume Next
> Set Source = Range("A1:P500").SpecialCells(xlCellTypeVisible)
> On Error GoTo 0
>
> If Source Is Nothing Then
> MsgBox "The source is not a range or the sheet is protected,
> please correct and try again.", vbOKOnly
> Exit Sub
> End If
>
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> End With
>
> Set wb = ActiveWorkbook
> Set Dest = Workbooks.Add(xlWBATWorksheet)
>
> Source.Copy
> With Dest.Sheets(1)
> .Cells(1).PasteSpecial Paste:=8
> .Cells(1).PasteSpecial Paste:=xlPasteValues
> .Cells(1).PasteSpecial Paste:=xlPasteFormats
> .Cells(1).Select
> Application.CutCopyMode = False
> End With
>
> TempFilePath = Environ$("temp") & "\"
> TempFileName = "Selection of " & wb.Name & " " & Format(Now,
> "dd-mmm-yy h-mm-ss")
>
> If Val(Application.Version) < 12 Then
> 'You use Excel 2000-2003
> FileExtStr = ".xls": FileFormatNum = -4143
> Else
> 'You use Excel 2007
> FileExtStr = ".xlsx": FileFormatNum = 51
> End If
>
> With Dest
> .SaveAs TempFilePath & TempFileName & FileExtStr,
> FileFormat:=FileFormatNum
> On Error Resume Next
> .SendMail "", _
> "Coil Schedule"
> On Error GoTo 0
> .Close SaveChanges:=False
> End With
>
> Kill TempFilePath & TempFileName & FileExtStr
>
> With Application
> .ScreenUpdating = True
> .EnableEvents = True
> End With
> End Sub
>
>
> I want that as soon as i click on the button a window of outlook should
> open with the specified email address in the TO seciton. Suppose i want
> (E-Mail Removed) should comes autometically in the To Column.
>
> what should i add in the code to do that.
>
> Thanks in Advance.
>
> Regards
>
> Akash Maheshwari
>

Hi Akash

Like Bob told you must use the Outlook object model to do what you want.
Look on my site for other examples.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Akash" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hi,
>
> I am using this Code for sending mail from Excel especially the from a
> rage to a particular range.
>
> Option Explicit
>
>
> Private Sub CommandButton1_Click()
> 'Working in 2000-2007
> Dim Source As Range
> Dim Dest As Workbook
> Dim wb As Workbook
> Dim TempFilePath As String
> Dim TempFileName As String
> Dim FileExtStr As String
> Dim FileFormatNum As Long
>
> Set Source = Nothing
> On Error Resume Next
> Set Source = Range("A1:P500").SpecialCells(xlCellTypeVisible)
> On Error GoTo 0
>
> If Source Is Nothing Then
> MsgBox "The source is not a range or the sheet is protected,
> please correct and try again.", vbOKOnly
> Exit Sub
> End If
>
> With Application
> .ScreenUpdating = False
> .EnableEvents = False
> End With
>
> Set wb = ActiveWorkbook
> Set Dest = Workbooks.Add(xlWBATWorksheet)
>
> Source.Copy
> With Dest.Sheets(1)
> .Cells(1).PasteSpecial Paste:=8
> .Cells(1).PasteSpecial Paste:=xlPasteValues
> .Cells(1).PasteSpecial Paste:=xlPasteFormats
> .Cells(1).Select
> Application.CutCopyMode = False
> End With
>
> TempFilePath = Environ$("temp") & "\"
> TempFileName = "Selection of " & wb.Name & " " & Format(Now,
> "dd-mmm-yy h-mm-ss")
>
> If Val(Application.Version) < 12 Then
> 'You use Excel 2000-2003
> FileExtStr = ".xls": FileFormatNum = -4143
> Else
> 'You use Excel 2007
> FileExtStr = ".xlsx": FileFormatNum = 51
> End If
>
> With Dest
> .SaveAs TempFilePath & TempFileName & FileExtStr,
> FileFormat:=FileFormatNum
> On Error Resume Next
> .SendMail "", _
> "Coil Schedule"
> On Error GoTo 0
> .Close SaveChanges:=False
> End With
>
> Kill TempFilePath & TempFileName & FileExtStr
>
> With Application
> .ScreenUpdating = True
> .EnableEvents = True
> End With
> End Sub
>
>
> I want that as soon as i click on the button a window of outlook should
> open with the specified email address in the TO seciton. Suppose i want
> (E-Mail Removed) should comes autometically in the To Column.
>
> what should i add in the code to do that.
>
> Thanks in Advance.
>
> Regards
>
> Akash Maheshwari
>