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: Excel - Auto E-mail notifications

Discussion in 'Business Applications' started by RichTea88, Jan 17, 2013.

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

    RichTea88 Thread Starter

    Joined:
    Jan 17, 2013
    Messages:
    6
    Firstly, Apologies, I know that this particular topic has been brought up countless times before. I'm genuinely not doing this out of laziness, but instead just my lack of ability to use VB.

    I'm looking to send automatic e-mails out when the worksheet is opened. This will be opened daily using a windows shedule manager
    The macro would find what documents are due for submission that day then use the other data from the row to compile an e-mail and send it to the document originator.
    It would then leave a check in the sent reminder box for verification.

    I've attached a worksheet below, it already has a basic macro embedded in it which if you highlight the row and click the send e-mail should show you how I was planing on compiling my e-mail.

    Anyones help on this would be great, would love to impress my boss when it comes to my meeting on monday!
     

    Attached Files:

  2. RichTea88

    RichTea88 Thread Starter

    Joined:
    Jan 17, 2013
    Messages:
    6
    Anyone able to help with this?

    Even if you could point me in the right direction.

    I'd just steal the code from some of the similar threads but I can't figure out the coding and what stuff to change, believe me I've tried.

    If anyone see's this and could reccomend someone who's good with it I'd really appreciate it :)

    Thanks
     
  3. RichTea88

    RichTea88 Thread Starter

    Joined:
    Jan 17, 2013
    Messages:
    6
    I'll try this one more time,

    If anyone could help with the above. Noticed a few folk are great at this kinda deal, Rollin_Again for example.

    If anyone can advise me, please do. Even if it's just showing me how to change the details in a related macro to suit my needs.

    Thanks.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    Hi,
    Welcome to the forum.
    I read your "cry for help" and have some additional questions.
    You mention that you will open the file using a scheduler, this means that what you really need is that to send all the messages to those rows of data where the due date is either = date or past and the remined snt is N, correct?

    Using a macro will always need a user intervention to accept automatic mailing unless you sue a program like Click-Yes that will 'click the yes button for you' you must look this up (seach Google for ClickYes)

    The button Send E-mail works for the selected row only, you could edit it with a loop like
    Code:
    Sub RunEmailCheck()
    dim xRow as Long
    For xRow = 2 to Range("A" & rows.count).End(XlUp).row
         if Range("I" & xrow).value <= DATE and Range("J" & xrow).Value = "N" Then 
            Range("A" & xrow).select
            SendEMail
            Range("J" & xrow).Value = "Y"
        end if
    Next xrow
    End Sub
    
    The above sample was typed directly into the post so check for typing errors but this would be the main idea.

    The macro that contains this loop could be called when the excel file is opened in w Worksheet_Open event in ThisWorkbook

    Hope this helps you on the way :)
     
  5. RichTea88

    RichTea88 Thread Starter

    Joined:
    Jan 17, 2013
    Messages:
    6
    Thanks for replying KeeBellah!

    I should actually mark this as solved somehow.

    Here's what code I used for it incase anyone else comes across the same problem!

    Code:
    Sub SendEMail()
        Dim Email As String, Subj As String
        Dim Msg As String, URL As String
        Dim DocT As String
        Dim LastRow As Long, NextRow As Long, RowNo As Long
        Dim wsEmail As Worksheet
        
        Set wsEmail = ThisWorkbook.Sheets("PPU Document Register")
        
        With wsEmail
            LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
            
            For RowNo = 2 To LastRow
                'Change "Date - 7" to suit your timescale
                If .Cells(RowNo, "J") = "" And .Cells(RowNo, "I") <= Date + 1 Then
                    Email = .Cells(RowNo, "O")
                    DocT = .Cells(RowNo, "F")
                    Subj = "Automated E-mail - Document Due " & .Cells(RowNo, "I")
                
                    Msg = ""
                    'Change "Date + 7" to suit your timescale
                    Msg = "Good Day " & .Cells(RowNo, "E") & "," & vbCrLf & vbCrLf _
                            & "This is an automated e-mail to let you know that your document" & vbCrLf _
                            & .Cells(RowNo, "C") & " - " & DocT & vbCrLf _
                            & "is due on " & Cells(RowNo, "I") & "." & vbCrLf & vbCrLf _
                            & "Many Thanks, " & vbCrLf & vbCrLf & "AutoMech"
                    
                    'Replace spaces with %20 (hex)
                    Subj = Replace(Subj, " ", "%20")
                    Msg = Replace(Msg, " ", "%20")
                    
                    'Replace carriage returns with %0D%0A (hex)
                    Msg = Replace(Msg, vbCrLf, "%0D%0A")
                    
                    'Create the URL
                    URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
                    
                    'Execute the URL (start the email client)
                    ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
                    
                    'Enter Date sent
                    .Cells(RowNo, "J") = Date
                End If
            Next
        End With
    End Sub

    Many Thanks for responding though :) as a sign of my appreciation.

    KeeBellah, Meet KalleBaah - http://www.youtube.com/watch?v=oAmwunOk-ts :)

    Enjoy
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    Thanks for the show of appreciation, just for the record, Keebellah is my surname spet backwards, Hallebeek :)
    Music sounds great (y) a little Reggae.
    You part of the band?


    You code changing the J / N to the actual date is better and easier to monitor.

    Did you find the ClickYes program?
    Here's the link (and its free !!!) http://www.contextmagic.com/express-clickyes/

    Works a charm, a little vba and admin rights on you system are required.

    If you need help just holler!
     
  7. RichTea88

    RichTea88 Thread Starter

    Joined:
    Jan 17, 2013
    Messages:
    6
    Haha, Ah I see!

    No, Not part of that band, your username just reminded me of them :p hadn't listened to them in years!

    Part of this band though, a bit different to Kallebaah :p http://www.youtube.com/watch?v=LIKvHVqZOIg

    Thanks for the link to that Clickyes program, I did wonder how other people were getting around that!

    Cheers for the advice! I'm sure I'll have more questions in the future, continually trying to up my VB knowledge!
     
  8. RichTea88

    RichTea88 Thread Starter

    Joined:
    Jan 17, 2013
    Messages:
    6
    Actually, I couldnt figure out the stuff to change, so a very nice gentleman over at excelforum wrote it for me.
     
  9. 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...
Thread Status:
Not open for further replies.

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

  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