Private Sub Workbook_Open() Dim toProcessSheets As Variant Dim thisSheet As Variant 'name of sheets to process toProcessSheets = Array("Sheet1", "niel") For Each thisSheet In toProcessSheets Sheets(thisSheet).Select Call ThisWorkbook.SendEMail Next End Sub
Private Sub Workbook_Open() Dim toProcessSheets As Variant Dim thisSheet As Variant 'name of sheets to process toProcessSheets = Array("Sheet1", "niel") For Each thisSheet In toProcessSheets Sheets(thisSheet).Select Call ThisWorkbook.SendEMail Next 'save the work book ActiveWorkbook.Save 'close excel application Application.Quit End Sub
Private Sub Workbook_Open() Call ThisWorkbook.SendEMail End Sub
DON'T MISS
I know I am pushing my luck but I wondered if you help me just once more. I am adding my workbook into my scheduled tasks so that it will open automatically at about 2am hence the openwork book part of the code. once its run the macro I need to save the workbook and auto close. any ideas?
activeworkbook.Save ' this will save the activeworkbook
application.Quit ' to close out excel completely
i have copied and pasted your code but it doesnt seem to work. the macro doesnt run upon opening of the workbook. (d53) has a formula in it that counts entries in the range d2:d51 that have negative value in them (less than 0) what I need the macro to do is when the workbook is opened I need it to check cell d53 and if that value is greater than 0, I need it to send an email (the email address is not anywhere in the workbook) to a certain address informing the recipiant of the value in d53. I would like if its poss for the email to read. " you have ?? of overdue entries on the not posted list, please action immediatley." the ?? being the value shown in d53. I then need it to check the same cell on sheet 2 and perform the same action if the value in that cell is greater then 0, but sheet 2 and the info on that sheet would be sent to a different email address. once the 2 actions are done and any relevant emails are sent I would then like the book to save and close. Thank you so much for your help so far, I have been struggling with this for about 2 weeks and its been driving me crazy.
the error is syntax error and the line highlighted is <<... you email code ..>>. I am way out of depth with this project as you can probably tell. I am taking a course on vba but am only 1 lesson in and dont really know a great deal about it yet. I have found some examples of macros to email on the net but the only one that works is this one. Private Sub Workbook_Open()
ActiveWorkbook.SendMail Recipients:="youremaill@address", Subject:="?????????????? ", ReturnReceipt:=True
End Sub
is there anyway of modifying this one so that some text can be in the main body of the email and also making reference to the value in cell d53.
should I scrap this one completely and continue with the template you kindly gave me.
Regards Rob