Emailing using VBA
 

XL 97

Below is a copy of code that I am using to email some
worksheets (many worksheets) in a workbook to different
individuals (many individuals) I need to be able to add
text msgs in the body

Sub Mail_every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim wb1 As Workbook
Dim strdate As String
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
For Each sh In wb1.Worksheets
If sh.Range("a1").Value Like "*@*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " & _
wb1.Name & " " & strdate & ".xls"
.SendMail ", _
"I have emailed using VB now"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub


This line Below will pause the send mail and let me type
the msg in the body, but then I have to select/type all
the email address individually (that will take forever
cause of the number of names).

..SendMail "", "This is the Subject line"


I'm using a form letter for the body. Is their some way
to enter text into the body with vba with out having to
use the sendkey command?


Code form:
http://www.rondebruin.nl/sendmail.htm#sheets

Emailing using VBA
 

With SendMail you can't Steve

Tell me your mail program and operating system then
I can help you maybe


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"steve" wrote in message ...
XL 97

Below is a copy of code that I am using to email some
worksheets (many worksheets) in a workbook to different
individuals (many individuals) I need to be able to add
text msgs in the body

Sub Mail_every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim wb1 As Workbook
Dim strdate As String
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
For Each sh In wb1.Worksheets
If sh.Range("a1").Value Like "*@*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " & _
wb1.Name & " " & strdate & ".xls"
.SendMail ", _
"I have emailed using VB now"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub


This line Below will pause the send mail and let me type
the msg in the body, but then I have to select/type all
the email address individually (that will take forever
cause of the number of names).

.SendMail "", "This is the Subject line"


I'm using a form letter for the body. Is their some way
to enter text into the body with vba with out having to
use the sendkey command?


Code form:
http://www.rondebruin.nl/sendmail.htm#sheets

mail program & operating system.
 

opps hit new post the first time

MS Outlook

NT 4.0

thanks
..



-----Original Message-----
With SendMail you can't Steve

Tell me your mail program and operating system then
I can help you maybe


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"steve" wrote in message
...
XL 97

Below is a copy of code that I am using to email some
worksheets (many worksheets) in a workbook to different
individuals (many individuals) I need to be able to add
text msgs in the body

Sub Mail_every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim wb1 As Workbook
Dim strdate As String
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
For Each sh In wb1.Worksheets
If sh.Range("a1").Value Like "*@*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " & _
wb1.Name & " " & strdate & ".xls"
.SendMail ", _
"I have emailed using VB now"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub


This line Below will pause the send mail and let me type
the msg in the body, but then I have to select/type all
the email address individually (that will take forever
cause of the number of names).

.SendMail "", "This is the Subject line"


I'm using a form letter for the body. Is their some way
to enter text into the body with vba with out having to
use the sendkey command?


Code form:
http://www.rondebruin.nl/sendmail.htm#sheets



.

mail program & operating system.
 

I see <g

See your new thread

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"steve" wrote in message ...
opps hit new post the first time

MS Outlook

NT 4.0

thanks
.



-----Original Message-----
With SendMail you can't Steve

Tell me your mail program and operating system then
I can help you maybe


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"steve" wrote in message
...
XL 97

Below is a copy of code that I am using to email some
worksheets (many worksheets) in a workbook to different
individuals (many individuals) I need to be able to add
text msgs in the body

Sub Mail_every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim wb1 As Workbook
Dim strdate As String
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
For Each sh In wb1.Worksheets
If sh.Range("a1").Value Like "*@*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")
sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & sh.Name & " of " & _
wb1.Name & " " & strdate & ".xls"
.SendMail ", _
"I have emailed using VB now"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next sh
Application.ScreenUpdating = True
End Sub


This line Below will pause the send mail and let me type
the msg in the body, but then I have to select/type all
the email address individually (that will take forever
cause of the number of names).

.SendMail "", "This is the Subject line"


I'm using a form letter for the body. Is their some way
to enter text into the body with vba with out having to
use the sendkey command?


Code form:
http://www.rondebruin.nl/sendmail.htm#sheets



.