Adding text to body of email with workbook attached.

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

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" <[email protected]> wrote in message news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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