Hi

My manager now wants to add text to the body of email being sent by
excel macro with a workbook attached. I have seen on other postings
different method using .Mail rather than .SendMail (see snippet below)
As a newbie not sure of the differences but if I try .Mail it comes u
with 2 security warnings (which ideally I would like to skip). Whic
brings me onto the second part how to implement :-

.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

so the email sends without intervention.

Any help would be appreciated.

Andrew


Sub Email_test()
ActiveSheet.Copy
sheet_name = ActiveSheet.Name
Set wb = ActiveWorkbook
email_contact_address = “Test_Contact@xxx
File_Title = "Test_sheet"
ActiveSheet.Buttons.Delete
ActiveSheet.Range("A1:X35").ClearComments

ActiveWindow.DisplayGridlines = False

Range("X1:BB50").Select
Selection.Clear
Range("A1").Select

ActiveWindow.DisplayGridlines = False
With wb
.SaveAs File_Title
' .Body = "Please note:- " <<<<<<<<<<
.SendMail email_contact_address, _
Email_Title

' .Display <<<<<<<<<<
'Application.Wait (Now + TimeValue("0:00:02")) <<<<<<<<<<
'Application.SendKeys "%S" <<<<<<<<<<
.ChangeFileAccess xlReadOnly
.Close True
End With
Kill Timesheet_Title ' Attempt to delet
open
End Su

--
ap
-----------------------------------------------------------------------
aph's Profile: http://www.excelforum.com/member.php...fo&userid=1717
View this thread: http://www.excelforum.com/showthread.php?threadid=49071

Hi

With SendMail this is not possible.
You must use the Outlook Object model if you run Outlook.

See my site for example code
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"aph" wrote in message ...

Hi

My manager now wants to add text to the body of email being sent by a
excel macro with a workbook attached. I have seen on other postings a
different method using .Mail rather than .SendMail (see snippet below).
As a newbie not sure of the differences but if I try .Mail it comes up
with 2 security warnings (which ideally I would like to skip). Which
brings me onto the second part how to implement :-

Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

so the email sends without intervention.

Any help would be appreciated.

Andrew


Sub Email_test()
ActiveSheet.Copy
sheet_name = ActiveSheet.Name
Set wb = ActiveWorkbook
email_contact_address = "Test_Contact@xxx
File_Title = "Test_sheet"
ActiveSheet.Buttons.Delete
ActiveSheet.Range("A1:X35").ClearComments

ActiveWindow.DisplayGridlines = False

Range("X1:BB50").Select
Selection.Clear
Range("A1").Select

ActiveWindow.DisplayGridlines = False
With wb
SaveAs File_Title
' .Body = "Please note:- " <<<<<<<<<<
SendMail email_contact_address, _
Email_Title

' .Display <<<<<<<<<<
'Application.Wait (Now + TimeValue("0:00:02")) <<<<<<<<<<
'Application.SendKeys "%S" <<<<<<<<<<
ChangeFileAccess xlReadOnly
Close True
End With
Kill Timesheet_Title ' Attempt to delete
open
End Sub


--
aph
------------------------------------------------------------------------
aph's Profile: http://www.excelforum.com/member.php...o&userid=17175
View this thread: http://www.excelforum.com/showthread...hreadid=490717

Hi
You cannot avoid the warning but you could consider using CDO to send mail,
see the following excellent reference.....

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

--
Cheers
Nigel



"aph" wrote in message
...

Hi

My manager now wants to add text to the body of email being sent by a
excel macro with a workbook attached. I have seen on other postings a
different method using .Mail rather than .SendMail (see snippet below).
As a newbie not sure of the differences but if I try .Mail it comes up
with 2 security warnings (which ideally I would like to skip). Which
brings me onto the second part how to implement :-

Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"

so the email sends without intervention.

Any help would be appreciated.

Andrew


Sub Email_test()
ActiveSheet.Copy
sheet_name = ActiveSheet.Name
Set wb = ActiveWorkbook
email_contact_address = "Test_Contact@xxx
File_Title = "Test_sheet"
ActiveSheet.Buttons.Delete
ActiveSheet.Range("A1:X35").ClearComments

ActiveWindow.DisplayGridlines = False

Range("X1:BB50").Select
Selection.Clear
Range("A1").Select

ActiveWindow.DisplayGridlines = False
With wb
SaveAs File_Title
' .Body = "Please note:- " <<<<<<<<<<
SendMail email_contact_address, _
Email_Title

' .Display <<<<<<<<<<
'Application.Wait (Now + TimeValue("0:00:02")) <<<<<<<<<<
'Application.SendKeys "%S" <<<<<<<<<<
ChangeFileAccess xlReadOnly
Close True
End With
Kill Timesheet_Title ' Attempt to delete
open
End Sub


--
aph
------------------------------------------------------------------------
aph's Profile:
http://www.excelforum.com/member.php...o&userid=17175
View this thread: http://www.excelforum.com/showthread...hreadid=490717

Thanks to you both. I will give CDO a try.

Andrew


--
aph
------------------------------------------------------------------------
aph's Profile: http://www.excelforum.com/member.php...o&userid=17175
View this thread: http://www.excelforum.com/showthread...hreadid=490717