|
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
.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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
E-Mail using BCC Fields
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 |
| All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com