E-Mail using BCC Fields

[Sean]

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 de Bruin]

Hi Sean

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

If you not use Outlook post back

 

[Sean]

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]

Hi Sean

Click on the Tip link on the page

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

 

[Sean]

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?

 

[Sean]

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

Thanks

Sean

 

[Ron de Bruin]

Hi Sean

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

 

[Ron de Bruin]

.Send instead of .Display

Ahhaa, you have test the code in the example workbook

 

[Sean]

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]

Hi Sean

You forgot this

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

 

[Sean]

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]

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

 

[Sean]

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]

Hi Sean

I not see this in Office 2003

 

[Sean]

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