Solved: Excel - Auto E-mail notifications

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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!
 

Attachments

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
 

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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 :)
 

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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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!
 

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!
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top