P
Prets
Hi,
I am using the code below to send an email using the email address in A
but I cant seem to include any text in the main email.
I got this code from the below site but cannot make sense of the sectio
at the bottom where it explains how to add text to the actual email.
http://www.rondebruin.nl/mail/folder3/mail5.htm
What ever I try, it doesnt seem to work
Sub Mail_Every_Worksheet()
'Working in 97-2010
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim I As Long
TempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010
FileExtStr = ".xlsm": FileFormatNum = 52
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each sh In ThisWorkbook.Worksheets
If sh.Range("A2").Value Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
TempFileName = "NSA " _
& ThisWorkbook.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail sh.Range("A2").Value, _
"Reminder"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With
'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr
End If
Next sh
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Can anybody give me some advise please?
Many thanks
Pr
I am using the code below to send an email using the email address in A
but I cant seem to include any text in the main email.
I got this code from the below site but cannot make sense of the sectio
at the bottom where it explains how to add text to the actual email.
http://www.rondebruin.nl/mail/folder3/mail5.htm
What ever I try, it doesnt seem to work
Sub Mail_Every_Worksheet()
'Working in 97-2010
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim I As Long
TempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010
FileExtStr = ".xlsm": FileFormatNum = 52
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each sh In ThisWorkbook.Worksheets
If sh.Range("A2").Value Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
TempFileName = "NSA " _
& ThisWorkbook.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
For I = 1 To 3
.SendMail sh.Range("A2").Value, _
"Reminder"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
.Close SaveChanges:=False
End With
'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr
End If
Next sh
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Can anybody give me some advise please?
Many thanks
Pr