I have the following simple code (thanks to Ron De Bruin) which e-mails
out a seletion of sheets for me and takes variable values for the "To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to populate
the BCC Field. How would I alter my code below so that I could continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which e-mails
out a seletion of sheets for me and takes variable values for the "To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to populate
the BCC Field. How would I alter my code below so that I could continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks Ron, I did have a look through this code on your site, but it
has static CC; BCC Subject fields etc and not sure how I would refer
for eg the Subject field back to AG1 on the E-Mail sheet, I guess its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which e-mails
out a seletion of sheets for me and takes variable values for the "To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to populate
the BCC Field. How would I alter my code below so that I could continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site, but it
has static CC; BCC Subject fields etc and not sure how I would refer
for eg the Subject field back to AG1 on the E-Mail sheet, I guess its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which e-mails
out a seletion of sheets for me and takes variable values for the "To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to populate
the BCC Field. How would I alter my code below so that I could continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thanks again Ron, I was right it is easy, or should I say your site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel dialog
box appears, but using the new code the mail message goes straight to
Outlook whereby I must hit Send. Is there any way once the code is run
that the mail actually goes (maybe with the old Yes/No security dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site, but it
has static CC; BCC Subject fields etc and not sure how I would refer
for eg the Subject field back to AG1 on the E-Mail sheet, I guess its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which e-mails
out a seletion of sheets for me and takes variable values for the "To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to populate
the BCC Field. How would I alter my code below so that I could continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

I worked it out Ron - .Send instead of .Display - which again you have
covered in your example

Thanks

Sean

Sean wrote:

Thanks again Ron, I was right it is easy, or should I say your site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel dialog
box appears, but using the new code the mail message goes straight to
Outlook whereby I must hit Send. Is there any way once the code is run
that the mail actually goes (maybe with the old Yes/No security dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site, but it
has static CC; BCC Subject fields etc and not sure how I would refer
for eg the Subject field back to AG1 on the E-Mail sheet, I guess its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which e-mails
out a seletion of sheets for me and takes variable values for the "To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to populate
the BCC Field. How would I alter my code below so that I could continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Hi Sean

In Outlook there is a setting in the options to send immediately

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ups.com...
Thanks again Ron, I was right it is easy, or should I say your site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel dialog
box appears, but using the new code the mail message goes straight to
Outlook whereby I must hit Send. Is there any way once the code is run
that the mail actually goes (maybe with the old Yes/No security dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site, but it
has static CC; BCC Subject fields etc and not sure how I would refer
for eg the Subject field back to AG1 on the E-Mail sheet, I guess its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which
e-mails
out a seletion of sheets for me and takes variable values for the
"To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to
populate
the BCC Field. How would I alter my code below so that I could
continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

.Send instead of .Display

Ahhaa, you have test the code in the example workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I worked it out Ron - .Send instead of .Display - which again you have
covered in your example

Thanks

Sean

Sean wrote:

Thanks again Ron, I was right it is easy, or should I say your site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel dialog
box appears, but using the new code the mail message goes straight to
Outlook whereby I must hit Send. Is there any way once the code is run
that the mail actually goes (maybe with the old Yes/No security dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site, but it
has static CC; BCC Subject fields etc and not sure how I would refer
for eg the Subject field back to AG1 on the E-Mail sheet, I guess its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which
e-mails
out a seletion of sheets for me and takes variable values for the
"To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to
populate
the BCC Field. How would I alter my code below so that I could
continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Ron, I am trying to insert some message body that I have within my file
using some of your code but its coming out blank, my (partial) code is
as follows, any reason why that would be? Text is displayed in Sheet
E-Mail W5:W34

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value

For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Ron de Bruin wrote:

.Send instead of .Display

Ahhaa, you have test the code in the example workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I worked it out Ron - .Send instead of .Display - which again you have
covered in your example

Thanks

Sean

Sean wrote:

Thanks again Ron, I was right it is easy, or should I say your site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel dialog
box appears, but using the new code the mail message goes straight to
Outlook whereby I must hit Send. Is there any way once the code is run
that the mail actually goes (maybe with the old Yes/No security dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site, but it
has static CC; BCC Subject fields etc and not sure how I would refer
for eg the Subject field back to AG1 on the E-Mail sheet, I guess its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which
e-mails
out a seletion of sheets for me and takes variable values for the
"To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to
populate
the BCC Field. How would I alter my code below so that I could
continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Hi Sean

You forgot this

Change the Body line to .Body = strbody to use the string.




--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ups.com...
Ron, I am trying to insert some message body that I have within my file
using some of your code but its coming out blank, my (partial) code is
as follows, any reason why that would be? Text is displayed in Sheet
E-Mail W5:W34

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value

For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Ron de Bruin wrote:

.Send instead of .Display

Ahhaa, you have test the code in the example workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I worked it out Ron - .Send instead of .Display - which again you have
covered in your example

Thanks

Sean

Sean wrote:

Thanks again Ron, I was right it is easy, or should I say your site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel
dialog
box appears, but using the new code the mail message goes straight to
Outlook whereby I must hit Send. Is there any way once the code is run
that the mail actually goes (maybe with the old Yes/No security dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site, but
it
has static CC; BCC Subject fields etc and not sure how I would
refer
for eg the Subject field back to AG1 on the E-Mail sheet, I guess
its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which
e-mails
out a seletion of sheets for me and takes variable values for
the
"To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to
populate
the BCC Field. How would I alter my code below so that I could
continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Still coming up blank perhaps I've put it in the wrong place. I've
adjusted it to below

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value
For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
.Body = strbody & cell.Value & vbNewLine
Next

Ron de Bruin wrote:

Hi Sean

You forgot this

Change the Body line to .Body = strbody to use the string.




--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ups.com...
Ron, I am trying to insert some message body that I have within my file
using some of your code but its coming out blank, my (partial) code is
as follows, any reason why that would be? Text is displayed in Sheet
E-Mail W5:W34

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value

For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Ron de Bruin wrote:

.Send instead of .Display

Ahhaa, you have test the code in the example workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I worked it out Ron - .Send instead of .Display - which again you have
covered in your example

Thanks

Sean

Sean wrote:

Thanks again Ron, I was right it is easy, or should I say your site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel
dialog
box appears, but using the new code the mail message goes straight to
Outlook whereby I must hit Send. Is there any way once the code is run
that the mail actually goes (maybe with the old Yes/No security dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site, but
it
has static CC; BCC Subject fields etc and not sure how I would
refer
for eg the Subject field back to AG1 on the E-Mail sheet, I guess
its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin) which
e-mails
out a seletion of sheets for me and takes variable values for
the
"To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to
populate
the BCC Field. How would I alter my code below so that I could
continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Still coming up blank perhaps I've put it in the wrong place.

Yes, use it like this



For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With





--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Still coming up blank perhaps I've put it in the wrong place. I've
adjusted it to below

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value
For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
.Body = strbody & cell.Value & vbNewLine
Next

Ron de Bruin wrote:

Hi Sean

You forgot this

Change the Body line to .Body = strbody to use the string.




--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ups.com...
Ron, I am trying to insert some message body that I have within my file
using some of your code but its coming out blank, my (partial) code is
as follows, any reason why that would be? Text is displayed in Sheet
E-Mail W5:W34

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value

For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Ron de Bruin wrote:

.Send instead of .Display

Ahhaa, you have test the code in the example workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I worked it out Ron - .Send instead of .Display - which again you
have
covered in your example

Thanks

Sean

Sean wrote:

Thanks again Ron, I was right it is easy, or should I say your site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel
dialog
box appears, but using the new code the mail message goes straight
to
Outlook whereby I must hit Send. Is there any way once the code is
run
that the mail actually goes (maybe with the old Yes/No security
dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site,
but
it
has static CC; BCC Subject fields etc and not sure how I would
refer
for eg the Subject field back to AG1 on the E-Mail sheet, I
guess
its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin)
which
e-mails
out a seletion of sheets for me and takes variable values
for
the
"To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to
populate
the BCC Field. How would I alter my code below so that I
could
continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Thats it Ron, thanks for your help. The mail now looks very impressive

One final Q. Everytime the Dialog box appears it jumps to Outlook, how
would I change it that it remains on the Excel application?


Ron de Bruin wrote:

Still coming up blank perhaps I've put it in the wrong place.

Yes, use it like this



For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With





--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Still coming up blank perhaps I've put it in the wrong place. I've
adjusted it to below

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value
For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
.Body = strbody & cell.Value & vbNewLine
Next

Ron de Bruin wrote:

Hi Sean

You forgot this

Change the Body line to .Body = strbody to use the string.




--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ups.com...
Ron, I am trying to insert some message body that I have within my file
using some of your code but its coming out blank, my (partial) code is
as follows, any reason why that would be? Text is displayed in Sheet
E-Mail W5:W34

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value

For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Ron de Bruin wrote:

.Send instead of .Display

Ahhaa, you have test the code in the example workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I worked it out Ron - .Send instead of .Display - which again you
have
covered in your example

Thanks

Sean

Sean wrote:

Thanks again Ron, I was right it is easy, or should I say your site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel
dialog
box appears, but using the new code the mail message goes straight
to
Outlook whereby I must hit Send. Is there any way once the code is
run
that the mail actually goes (maybe with the old Yes/No security
dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your site,
but
it
has static CC; BCC Subject fields etc and not sure how I would
refer
for eg the Subject field back to AG1 on the E-Mail sheet, I
guess
its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin)
which
e-mails
out a seletion of sheets for me and takes variable values
for
the
"To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish to
populate
the BCC Field. How would I alter my code below so that I
could
continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr, Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Hi Sean

I not see this in Office 2003

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thats it Ron, thanks for your help. The mail now looks very impressive

One final Q. Everytime the Dialog box appears it jumps to Outlook, how
would I change it that it remains on the Excel application?


Ron de Bruin wrote:

Still coming up blank perhaps I've put it in the wrong place.

Yes, use it like this



For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With





--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Still coming up blank perhaps I've put it in the wrong place. I've
adjusted it to below

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value
For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
.Body = strbody & cell.Value & vbNewLine
Next

Ron de Bruin wrote:

Hi Sean

You forgot this

Change the Body line to .Body = strbody to use the string.




--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ups.com...
Ron, I am trying to insert some message body that I have within my
file
using some of your code but its coming out blank, my (partial) code
is
as follows, any reason why that would be? Text is displayed in Sheet
E-Mail W5:W34

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC =
ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject =
ThisWorkbook.Sheets("E-Mail").Range("BA1").Value

For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Ron de Bruin wrote:

.Send instead of .Display

Ahhaa, you have test the code in the example workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I worked it out Ron - .Send instead of .Display - which again you
have
covered in your example

Thanks

Sean

Sean wrote:

Thanks again Ron, I was right it is easy, or should I say your
site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel
dialog
box appears, but using the new code the mail message goes
straight
to
Outlook whereby I must hit Send. Is there any way once the code
is
run
that the mail actually goes (maybe with the old Yes/No security
dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your
site,
but
it
has static CC; BCC Subject fields etc and not sure how I
would
refer
for eg the Subject field back to AG1 on the E-Mail sheet, I
guess
its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin)
which
e-mails
out a seletion of sheets for me and takes variable values
for
the
"To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish
to
populate
the BCC Field. How would I alter my code below so that I
could
continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr,
Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

Ron, yes I'm using Office 2003 and everytime I run the code, the
security dialog box pops up, not in excel but in Outlook, thus I have
to go back to Excel

Not a major issue, but the dialog box used to remain on Excel on my
older code


Ron de Bruin wrote:

Hi Sean

I not see this in Office 2003

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thats it Ron, thanks for your help. The mail now looks very impressive

One final Q. Everytime the Dialog box appears it jumps to Outlook, how
would I change it that it remains on the Excel application?


Ron de Bruin wrote:

Still coming up blank perhaps I've put it in the wrong place.

Yes, use it like this



For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value
.Body = strbody
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With





--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Still coming up blank perhaps I've put it in the wrong place. I've
adjusted it to below

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject = ThisWorkbook.Sheets("E-Mail").Range("BA1").Value
For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
.Body = strbody & cell.Value & vbNewLine
Next

Ron de Bruin wrote:

Hi Sean

You forgot this

Change the Body line to .Body = strbody to use the string.




--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ups.com...
Ron, I am trying to insert some message body that I have within my
file
using some of your code but its coming out blank, my (partial) code
is
as follows, any reason why that would be? Text is displayed in Sheet
E-Mail W5:W34

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC =
ThisWorkbook.Sheets("E-Mail").Range("BA4:BA4").Value
.Subject =
ThisWorkbook.Sheets("E-Mail").Range("BA1").Value

For Each cell In ThisWorkbook.Sheets("E-Mail").Range("W5:W34")
strbody = strbody & cell.Value & vbNewLine
Next
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Ron de Bruin wrote:

.Send instead of .Display

Ahhaa, you have test the code in the example workbook

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I worked it out Ron - .Send instead of .Display - which again you
have
covered in your example

Thanks

Sean

Sean wrote:

Thanks again Ron, I was right it is easy, or should I say your
site
makes it that way

One last question, on the previous SendMail code a Yes/No/Cancel
dialog
box appears, but using the new code the mail message goes
straight
to
Outlook whereby I must hit Send. Is there any way once the code
is
run
that the mail actually goes (maybe with the old Yes/No security
dialog
bx?


Ron de Bruin wrote:

Hi Sean

Click on the Tip link on the page

You go to this page then
http://www.rondebruin.nl/mail/tips2.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
oups.com...
Thanks Ron, I did have a look through this code on your
site,
but
it
has static CC; BCC Subject fields etc and not sure how I
would
refer
for eg the Subject field back to AG1 on the E-Mail sheet, I
guess
its
easy, but I'm not code savvy


Ron de Bruin wrote:

Hi Sean

If you use Outlook then use
http://www.rondebruin.nl/mail/folder2/mail2.htm

If you not use Outlook post back


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Sean" wrote in message
ps.com...
I have the following simple code (thanks to Ron De Bruin)
which
e-mails
out a seletion of sheets for me and takes variable values
for
the
"To"
and "Subject" fields.

I believe that I can't use this code (SendMail) if I wish
to
populate
the BCC Field. How would I alter my code below so that I
could
continue
to use variable values for the BCC and Subject fields?

Thanks



Sub Mail_Report()
Dim wb As Workbook
Dim strdate As String
Dim MyArr As Variant
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets("E-Mail").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
MyArr = Sheets("E-Mail").Range("AG2:AG11")
.SendMail MyArr,
Sheets("E-Mail").Range("AG1").Value
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub