Dear Experts,
Regarding this topic, I have read following path. That's rally useful.

http://www.rondebruin.nl/sendmail.htm

I have tested following scripts. That's work.
Now I want to have something changed.

Please imagine following as a spreadsheet.
Row/Column

A B C D
E F
_|________________________________________________ ________________
1| Marco Buttons Send to CC List BCC List Subject
Body Text
2| RUN-Client A Testing
Hello,Test
3| Run-Income Income Stmt Pls
note $

I want to set a list of buttons in Column A, then send the Excel files as
attachment to recipients based on the text I typed in Column B. (I can add,
modify / delete easily from now on if it is succeeded). Also, can I use comma
, or ; to separate the recipients if they are more than one please ?
Activate the Excel windows (file has been opened) and send that file should
not be a problem. I can manage the scripts. Just want to send to the people
based on the text in a cell only.

CC list and BCC list are in Column C and D respectively (if any).
Column E is meant for Subject of the mail.
F is a Simple Body text for the mail.

The final part is that ... Is it possible to put in MS outlook 2003 Draft
first please ?
(I can re-check before I really press the send button in MS outlook.)
If possible, can you tell the scripts for Send and the scripts to be Draft
as well ?

Please apologize if it is too complicated. Thanks for help!

(Quote)
Sub Mail_Workbook_2()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" &
vbNewLine & _
"be no VBA code in the file you send. Save the" &
vbNewLine & _
"file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If

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

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy
h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", ,
1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

With wb2
On Error Resume Next
.SendMail ", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
(End of Quote)

Hi all,
I dont know why after uploading ... it becomes so ugly.

Anyway, column A is a list of marco buttons.
Cells in Column B contains the Email address for recipents.
Cells in Column C contains the Email address in CC list.
Cells in Column D contains the Email address for BCC list.
Cells in Column E contains the Email address for Mail Subject.
Cells in Column F contains the Email address for Body Text for the mail.

Thanks,

"Elton Law" wrote:

Dear Experts,
Regarding this topic, I have read following path. That's really useful.

http://www.rondebruin.nl/sendmail.htm

I have tested following scripts. That's work.
Now I want to have something changed.

Please imagine following as a spreadsheet.
Row/Column

A B C D
E F
_|________________________________________________ ________________
1| Marco Buttons Send to CC List BCC List Subject
Body Text
2| RUN-Client A Testing
Hello,Test
3| Run-Income Income Stmt Pls
note $

I want to set a list of buttons in Column A, then send the Excel files as
attachment to recipients based on the text I typed in Column B. (I can add,
modify / delete easily from now on if it is succeeded). Also, can I use comma
, or ; to separate the recipients if they are more than one please ?
Activate the Excel windows (file has been opened) and send that file should
not be a problem. I can manage the scripts. Just want to send to the people
based on the text in a cell only.

CC list and BCC list are in Column C and D respectively (if any).
Column E is meant for Subject of the mail.
F is a Simple Body text for the mail.

The final part is that ... Is it possible to put in MS outlook 2003 Draft
first please ?
(I can re-check before I really press the send button in MS outlook.)
If possible, can you tell the scripts for Send and the scripts to be Draft
as well ?

Please apologize if it is too complicated. Thanks for help!

(Quote)
Sub Mail_Workbook_2()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" &
vbNewLine & _
"be no VBA code in the file you send. Save the" &
vbNewLine & _
"file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If

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

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy
h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", ,
1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

With wb2
On Error Resume Next
.SendMail ", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
(End of Quote)

Hi Elton

Start with the Outlook mail template
http://www.rondebruin.nl/mail/templates.htm

--

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




"Elton Law" wrote in message ...
Dear Experts,
Regarding this topic, I have read following path. That's rally useful.

http://www.rondebruin.nl/sendmail.htm

I have tested following scripts. That's work.
Now I want to have something changed.

Please imagine following as a spreadsheet.
Row/Column

A B C D
E F
_|________________________________________________ ________________
1| Marco Buttons Send to CC List BCC List Subject
Body Text
2| RUN-Client A Testing
Hello,Test
3| Run-Income Income Stmt Pls
note $

I want to set a list of buttons in Column A, then send the Excel files as
attachment to recipients based on the text I typed in Column B. (I can add,
modify / delete easily from now on if it is succeeded). Also, can I use comma
, or ; to separate the recipients if they are more than one please ?
Activate the Excel windows (file has been opened) and send that file should
not be a problem. I can manage the scripts. Just want to send to the people
based on the text in a cell only.

CC list and BCC list are in Column C and D respectively (if any).
Column E is meant for Subject of the mail.
F is a Simple Body text for the mail.

The final part is that ... Is it possible to put in MS outlook 2003 Draft
first please ?
(I can re-check before I really press the send button in MS outlook.)
If possible, can you tell the scripts for Send and the scripts to be Draft
as well ?

Please apologize if it is too complicated. Thanks for help!

(Quote)
Sub Mail_Workbook_2()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" &
vbNewLine & _
"be no VBA code in the file you send. Save the" &
vbNewLine & _
"file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If

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

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy
h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", ,
1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

With wb2
On Error Resume Next
.SendMail ", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
(End of Quote)

That's fantastic ... I will have a try ... let you know the outcome later.
At least I can see the sunrise !

"Ron de Bruin" wrote:

Hi Elton

Start with the Outlook mail template
http://www.rondebruin.nl/mail/templates.htm

--

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




"Elton Law" wrote in message ...
Dear Experts,
Regarding this topic, I have read following path. That's rally useful.

http://www.rondebruin.nl/sendmail.htm

I have tested following scripts. That's work.
Now I want to have something changed.

Please imagine following as a spreadsheet.
Row/Column

A B C D
E F
_|________________________________________________ ________________
1| Marco Buttons Send to CC List BCC List Subject
Body Text
2| RUN-Client A Testing
Hello,Test
3| Run-Income Income Stmt Pls
note $

I want to set a list of buttons in Column A, then send the Excel files as
attachment to recipients based on the text I typed in Column B. (I can add,
modify / delete easily from now on if it is succeeded). Also, can I use comma
, or ; to separate the recipients if they are more than one please ?
Activate the Excel windows (file has been opened) and send that file should
not be a problem. I can manage the scripts. Just want to send to the people
based on the text in a cell only.

CC list and BCC list are in Column C and D respectively (if any).
Column E is meant for Subject of the mail.
F is a Simple Body text for the mail.

The final part is that ... Is it possible to put in MS outlook 2003 Draft
first please ?
(I can re-check before I really press the send button in MS outlook.)
If possible, can you tell the scripts for Send and the scripts to be Draft
as well ?

Please apologize if it is too complicated. Thanks for help!

(Quote)
Sub Mail_Workbook_2()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String

Set wb1 = ActiveWorkbook

If Val(Application.Version) = 12 Then
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" &
vbNewLine & _
"be no VBA code in the file you send. Save the" &
vbNewLine & _
"file first as xlsm and then try the macro again.",
vbInformation
Exit Sub
End If
End If

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

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy
h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, _
Len(wb1.Name) - InStrRev(wb1.Name, ".", ,
1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

With wb2
On Error Resume Next
.SendMail ", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
(End of Quote)