Send Email from Excel
 

Dear all,

I have a macro to mail many sheets as belows:

Sub Mail_every_Worksheet()

'Mail every Worksheet with address in cell B4 using VBA in Microsoft
Excel

'Dim strDate As String
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("B4").Value Like "*@*" And sh.Name < "Form" Then
sh.Copy
'strDate = Format(Date, "dd-mm-yy") & " " & Format(Time,
"h-mm-ss")
ActiveWorkbook.SaveAs sh.Name & ".xls"
ActiveWorkbook.SendMail ActiveSheet.Range("B4").Value, _
ActiveSheet.Name
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End If
Next sh
Application.ScreenUpdating = True
End Sub

However, I don't know how to put the content of this email, for ex.
"This is the salary of Jun 05".

Could any one can help me to add the content to my email.

Thanks a lot!

Nam


--
lehainam
------------------------------------------------------------------------
lehainam's Profile: http://www.excelforum.com/member.php...o&userid=18615
View this thread: http://www.excelforum.com/showthread...hreadid=381126

Send Email from Excel
 

It depends on mail system installed on ur machine because u need to create
reference to library...
As far as I know excel doesn't support "e.g .body" method.


Dear all,

I have a macro to mail many sheets as belows:

Sub Mail_every_Worksheet()

'Mail every Worksheet with address in cell B4 using VBA in Microsoft
Excel

'Dim strDate As String
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("B4").Value Like "*@*" And sh.Name < "Form" Then
sh.Copy
'strDate = Format(Date, "dd-mm-yy") & " " & Format(Time,
"h-mm-ss")
ActiveWorkbook.SaveAs sh.Name & ".xls"
ActiveWorkbook.SendMail ActiveSheet.Range("B4").Value, _
ActiveSheet.Name
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End If
Next sh
Application.ScreenUpdating = True
End Sub

However, I don't know how to put the content of this email, for ex.
"This is the salary of Jun 05".

Could any one can help me to add the content to my email.

Thanks a lot!

Nam


--
lehainam
------------------------------------------------------------------------
lehainam's Profile:
http://www.excelforum.com/member.php...o&userid=18615
View this thread:
http://www.excelforum.com/showthread...hreadid=381126

Send Email from Excel
 

see http://www.rondebruin.nl/sendmail.htm to see all you wanted to know
about Excel and Mail.

DM Unseen

Send Email from Excel
 

lehainam wrote:
Dear all,

I have a macro to mail many sheets as belows:

Sub Mail_every_Worksheet()

'Mail every Worksheet with address in cell B4 using VBA in Microsoft
Excel

'Dim strDate As String
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Worksheets
If sh.Range("B4").Value Like "*@*" And sh.Name < "Form" Then
sh.Copy
'strDate = Format(Date, "dd-mm-yy") & " " & Format(Time,
"h-mm-ss")
ActiveWorkbook.SaveAs sh.Name & ".xls"
ActiveWorkbook.SendMail ActiveSheet.Range("B4").Value, _
ActiveSheet.Name
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End If
Next sh
Application.ScreenUpdating = True
End Sub

However, I don't know how to put the content of this email, for ex.
"This is the salary of Jun 05".

Could any one can help me to add the content to my email.

Thanks a lot!

Nam

There's another thread that might help:
http://groups-beta.google.com/group/...21d1eb90af0f1/


Iain