Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Add fuctionality to auto email code

0

Hi

I require help in improving the fuctionality of my spreadsheet, I would like to incorporate a constant loop in the code below, so once email has been sent from a row, that row falls out of the loop until the next day.

Dim xRg As Range
'Update by Extendoffice 2018/3/7
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Cells.Count > 1 Then Exit Sub
  Set xRg = Intersect(Range("$F$4:$F$440"), Target)
    If xRg Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) And Target.Value < 0 Then
        Call Mail_small_Text_Outlook
    End If
End Sub
Sub Mail_small_Text_Outlook()
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Check Delivery Schedule" & vbNewLine & vbNewLine & _
              "Delivery Due" & vbNewLine & _
              ""
    On Error Resume Next
    With xOutMail
        .To = "soabys.net@hotmail.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "Deliveries"
        .Body = xMailBody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Answer
Discuss

Discussion

The purpose the email address in the code is to test the marco works. looking at the code, I'm asking it to exit sub once testing is complete, which I don't want it to do. I'm not yet versed enough in VBA to create a constant loop, for it to look at every row and carry out this task (if column F is <= 0 if that statement is true, send an email and then exclude that row unitl the next day for testing, until the task is completed) This would enable me to keep the program running and me up to date with deliveries without any input. Do you have any suggestions on books that will lead me straight to this answer? My own thoughts on this is to create another sub, so once it has tested the first sequence, it drops into a looped sub until the next day, would this be the right way to go?
Beat101 (rep: 2) Aug 22, '18 at 3:35 pm
Please edit your Answer to include all required information, instead of posting more information in a comment. Also, please surround the Macro with CODE tags - you see these tags when you click the CODE button in the Editor; just copy/paste the macro between these tags.
don (rep: 1989) Aug 22, '18 at 4:52 pm
Add to Discussion

Answers

0

Your problem isn't related to code. Therefore code can't provide a solution. Instead, your problem has its roots in worksheet design and, ultimately, logic. That is where a solution must be looked for. Coding can only start when the logic is complete and flawless and the worksheet design was adapted to accommodate it.

It seems that you envision a program which loops through all rows in your worksheet and sends emails to eligible addresses. For this the worksheet must meet two requirements.

  1. There must be an email address in each row.
  2. There must be a criterium by which to decide which row is "eligible".

It seems that you envision an email to be sent once daily. Therefore the eligibility criterion might be a date. A column must be added for that. It could be hidden. When an email is sent the current date and time is entered in that column by the macro that sends the email. And when the macro scans the list again for eligible rows it will skip those where the date is less than 24 hours in the past. Now, if that is your idea to implement then that is the question you might ask.

However, there is an even more basic gap in your logic. It seems that you envision that the code would scan the sheet continuously, sending emails when an eligible row is encountered. Don't you ever switch off your computer? Why do you need to scan continuously if you want to send emails only once in 24 hours?

At the very least, you might envision that a scan is performed at 10 am every morning and emails sent to qualifying recipients. But why so complicated? You might think of sending emails when you load the workbook. Or you might want a button: so you load Excel, open the workbook, check entries and update them, and then press the button to send emails to qualifying recipients. This too, has nothing to do with coding. The question is how you want to design your workflow.

As you see, your "question" breaks down into two areas. One is to establish a loop, as you call it, which scans all rows and sends emails to everyone. This loop doesn't exist today and it can't be created until you decide how it is to be used.

The second area would establish qualifying recipients. For this purpose the criterium column (I have suggested a date) is missing in your worksheet but, even more importantly, you are extremely vague in your definition of the rule by which eligibilty could be decided. "once an email has been sent" is vastly different from checking if one was sent within the last 24 hours. Definition and determination are decisive factors. Once you program "24 hours" you also need to fix a specific time of day when the scan is made. This topic is unrelated to programming and entirely dependent on how you wish to work.

My advice is to start at the beginning. First decide how you want to work. Then design your worksheet so that it can support your vision of the work. Make sure that the worksheet can function manually. And only then automate the process - one small step at a time.

Discuss

Discussion

I appreciate the time you have taken to go through this topic with me and, explain where I'm going wrong with my thinking and logic. very helpfull 
Beat101 (rep: 2) Aug 23, '18 at 2:39 pm
Hello Beat, If my answer was helpful please mark the question as "Accepted" and move on to your next question. Although I really tried to be helpful I also realise that you didn't come much closer to your actual goal. Now that you should be on a sustainable path your next question should mark the progress that eluded you this time.
Variatus (rep: 4889) Aug 23, '18 at 8:01 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login