Hello,
I am using excel 2007 and I want to create a macro that runs automatically when the work book is loaded. the macro must check the value in cell (d53) and if that value is less than 0 then the macro needs to send an email notifying the recepiant of the value in (d53). once its done that I need it to go to sheet 2 in the workbook and carry out the same function but sending an email to a different receipiant but again sending the value in sheet 2 (d53) to the new recipiant. On top of this I need to be able to save this workbook so that users of older versions of excel can still use it. Can anyone help PPPLLLEEEAAASSSEEE
Thanks for your reply.
I can get it to auto open and have performed certain tasks with auto open and then save and close but I dont know the code for the if function. I have the lines of code for the email but its the if function that I cant understand. any ideas.
thanks
sub auto_open()
Dim vSheet1_D53 as variant
Dim vSheet2_D53 as variant
vSheet1_D53 = (0 + sheets("Sheet1").Range("d53"))
vSheet2_D53 = (0 + sheets("Sheet2").Range("d53"))
if (vSheet1_D53 < 0) then call sendEmail(1)
if (vSheet2_D53 < 0) then call sendEmail(2)
end sub
Sub sendEmail(emailType as integer)
If (emailType = 1) then
sendTo = "Person1@earth.com"
elseif (emailType = 2) then
sendTo = "Person2@earth.com"
else
exit sub
end if
<<... you email code ..>>
End sub
in case d53 has the email address then you can in auto open change d53 assignment as
Hi and thanks you so much, you are a genius.
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?
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.
Rob my code was just a template to give you idea how you can approach it. Since I have no idea how you have coded etc, it is hard to tell what the error is. What error message you get
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()
Hi
I have moved on and I can now get the workbook to open automatically using the scheduled tasks in system tools. when the workbook opens up it does send an email giving me the exact inforation that I want it to. it works perfect. this is the code I adapted.
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Private Sub Workbook_Open()
Call ThisWorkbook.SendEMail
End Sub
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 2 To 4 'data in rows 2-4
' Get the email address
Email = Cells(r, 2)
' Message subject
Subj = "Your Annual Bonus"
' Compose the message
Msg = ""
Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
Msg = Msg & "I am pleased to inform you that your annual bonus is "
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"
Next r
End Sub
what I need it to do now is then run the same again but on sheet 2 (which is named "niel") and then automatically save and close.
could you help me with this last little bit. I could probably do it myself but it would take me another 2 weeks
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
where about should I paste this code?
and also do you think there is way of only running the save and close part of the macro if the workbook is not modified for 1hr because in the day the users will all be opening the workbook to change certain values and if the save and close part runs straight away it will just close as soon as it opens. what do you think.
This is a function or a sub routine that you mentioned earlier
Private Sub Workbook_Open()
Call ThisWorkbook.SendEMail
End Sub
This routine is executed when the work book is open. It will send the emails out based on how the call is being made in SendMail routine.
I just modified that routines and included additional lines of code to process same email for all the sheets that you would indicate in this line toProcessSheets = Array("Sheet1", "niel")
So if the book is open and modified multiple times, it may send emails out (under right conditions).
It looks like saving the book and sending email are two different things. I thought that's what you wanted, but I guess I misunderstood you. Well I think it is possible to save and close the file, if not modified for an hour. right from the top of my head I think that you would need to use the event
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub
there needs to be a timer that sets for on hour or resets etc. Just need a little involved coding I guess.
6165 users have said thank you to us this month
- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016
-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?
- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016
753 > rob -activeworkbook.Save ' this will save the activeworkbook
application.Quit ' to close out excel completely
- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016
-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.
- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016
753 > rob -- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016
-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