1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Emailing from Excel Based off Cell Value

Discussion in 'Business Applications' started by nshoemake, Aug 5, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. nshoemake

    nshoemake Thread Starter

    Joined:
    Aug 5, 2010
    Messages:
    10
    Hey all:

    I'm have really no experience with coding, so I'm out of my depth trying to do this myself. Hopefully someone can help, so here goes!

    Essentially, my excel sheet keeps track of dates of when files need to be renewed. They need to be renewed every 60 days. So I have an "IF" formula that tells me if it's time for renewal.

    What do I need to do to get an email notification whenever a file comes up for renewal? I've attached a sample sheet.

    Best,
    Nick

    PS.
    Using Excel/Outlook 2010
    ClickYes Pro is installed (don't know if it will be necessary or not)
     

    Attached Files:

  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    the click Yes program will be required to by pass security, but are you expecting this to happen from a closed book? I don't think that will be possible.

    What you will likely need is the Excel file to load in theXLstart folder and have an auto execute macro that will check the dates.... please provide more details
     
  3. nshoemake

    nshoemake Thread Starter

    Joined:
    Aug 5, 2010
    Messages:
    10
    Thanks for the reply. I wasn't expecting to do it from a closed book. I really have no idea how to do it though. If you let me know what needs to be done I'll do it.

    Any help would be especially appreciated. Thanks again.

    What details do you need?
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    I guess I don't understand why you need an email notification if you are looking at the spreadsheet? That's why I figured it was a closed book, with the "Current" date being the variable that affects when the date becomes due. You only see it if you open it, so if you forget to open then you don't know, but if a macro ran when ever your computer logs on, or via the windows task scheduler, then it would do it's check and send the email.

    The send code I includes sending the Excel file, so by doing that I could have it filter to the items that are due and when you open it you see them right away... or if the file is large copy the due items to a new file, ... but still have to attach a file.



    this is the code that will send an excel file


    Sub sendobj()


    Dim SubJ As String



    Dim Email_1 As String


    Email_1 = "Put your email here"

    SubJ = "Subject Goes Here"

    ActiveWorkbook.SendMail Email_1, SubJ, ReturnReceipt:=True



    End Sub
     
  5. nshoemake

    nshoemake Thread Starter

    Joined:
    Aug 5, 2010
    Messages:
    10
    So what I did was created a new task for my windows task scheduler, and it works fine. It's set to open the file once daily in the morning. I copied the code you sent and put it onto a macro-enabled excel sheet.

    When I saved it, and ran the task (with outlook open), the excel file opened, but no email was sent.

    Also, I think it'd be awesome if it could filter just the lines of files that need to be renewed and send that. If I'm right it saves them into a new workbook right? Could it automatically delete that workbook after it's sent?

    Ziggy you're awesome so far! Thanks for your help!!
     
  6. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    your right, I forgot a couple of things.


    You need to put the code in "This workbook"

    and should be the workbook open event.....

    *** also turn off your macro security


    Code:
    Private Sub Workbook_Open()
    
    Application.DisplayAlerts = False
    
    
    
    Dim SubJ As String
    
    
    
    Dim Email_1 As String
    
    
    Email_1 = "Put your email here"
    
    SubJ = "Subject Goes Here"
    
    ActiveWorkbook.SendMail Email_1, SubJ, ReturnReceipt:=True
    
    
    
    
    Application.DisplayAlerts = True
    
    End Sub
     
  7. nshoemake

    nshoemake Thread Starter

    Joined:
    Aug 5, 2010
    Messages:
    10
    Ziggy it works perfectly. So thanks a mil!!

    But...

    If I am correct it will simply send the workbook once a day (because the task is set to execute once daily).

    What I was hoping to get it to do is only send if there is a file that needs renewal. Is there some way to do that?
     
  8. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    correct, so what we need to do now is put in a condition to check if anything is overdue. so give me a little while I'll put in a loop that check the column.

    Then if it finds an overdue it will send, if not it will still run but not send... oh I forgot to add a close command, so I'll add that in
     
  9. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    fyi, unless there is a reason I don't understand.... this....

    =DATE(YEAR(C2),MONTH(C2),DAY(C2)+60)

    can simply be =C2+60
     
  10. nshoemake

    nshoemake Thread Starter

    Joined:
    Aug 5, 2010
    Messages:
    10
    Hahaha. Oops. Somethings are easier than you think I suppose.

    Thanks again Ziggy!
     
  11. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    alright, this should do it..... (don't forget to edit the email address... you could actually pull it from a cell on the sheet)


    Code:
    Private Sub Workbook_Open()
    
    Dim endrow As Integer
    
    Dim RowC As Integer 'row Counter
    Dim wb As Workbook
    Dim ws As Worksheet
    
    
    Dim SubJ As String
    Dim Email_1 As String
    Dim SendChk As String
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    
    Application.DisplayAlerts = False
    
    ' Columns D & E will update everytime
    
    'find last row used based on 3rd column
    endrow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
    
            For RowC = 2 To endrow
            
            'ads 60 days to date from column 4 and puts in column 5
            Cells(RowC, 4) = Cells(RowC, 3) + 60
            
                            If Now() >= Cells(RowC, 4) Then
                            
                                Cells(RowC, 5) = "Needs Attention"
                                'when it finds an overdue it will set the variable
                                'which will be used to determine if it will send below
                               SendChk = "Sendme"
                               
                               Else
                               ' clears the cell in column 5
                                Cells(RowC, 5) = ""
                               
                            
                            End If
            
            Next RowC
    
    
            If SendChk = "Sendme" Then
            
            
                    Email_1 = "your email"
                    
                    SubJ = "Subject Goes Here"
                    
                    ActiveWorkbook.SendMail Email_1, SubJ, ReturnReceipt:=True
            
            
            End If
    
    wb.Save
    Application.Quit
    
    Application.DisplayAlerts = True
    
    
    End Sub
     

    Attached Files:

  12. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    just a note, I used the 3rd column to check for last row. you may have a more consistent column ( like client)

    'find last row used based on 3rd column
    endrow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row



    so client being column 1 change to ......

    endrow = ws.Cells(ws.Rows.Count,1).End(xlUp).Row
     
  13. nshoemake

    nshoemake Thread Starter

    Joined:
    Aug 5, 2010
    Messages:
    10
    That's fantastic!

    I got it to work except...

    Whenever I try to open the file to edit it, it will open, close, send email. I can't edit it. Is there a workaround for this?

    Hahaha... it's working too well.
     
  14. nshoemake

    nshoemake Thread Starter

    Joined:
    Aug 5, 2010
    Messages:
    10
    Also when the file is sent it sends the whole file. Is there a way to get it to open a new workbook, save the rows in there that need to be renewed (excluding the ones that don't), send it, and delete it? Maybe this part is too much to ask. If it is don't worry about it! Thanks!
     
  15. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    turn off your "Click Yes" program... so you can click "NO" :)

    then debug

    I can make it go to a new book only for the expired ones.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Emailing Excel
  1. thebam
    Replies:
    4
    Views:
    162
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/940910

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice