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
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

Adding text to body of email with workbook attached.
 

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

Adding text to body of email with workbook attached.
 

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

Adding text to body of email with workbook attached.
 

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