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