PDA

View Full Version : Send email based on a date



imagiine
06-25-2013, 04:00 AM
Hello,

I hope someone can help me with the following.

XML file contains about 17 tabs, page 17 is the setup page this contains a data base of names, and email adresses for each person.

Now in page 1 for example, Im looking to achieve the following:

Send an email to the person, saying he needs to take actions because the date for his task is within 21 days of a certain date (this date is in a certain cell, lets just say row H)

I already found this code, which can probably be adjusted:
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 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 = "TEST" ' Compose the message Msg = "" Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf Msg = Msg & "TEST just a test " Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf Msg = Msg & "myname" & vbCrLf Msg = Msg & "test" ' Replace spaces with %20 (hex) Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") ' Replace carriage returns with %0D%0A (hex) Msg = Application.WorksheetFunction.Substitute(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 ' Wait two seconds before sending keystrokes Application.Wait (Now + TimeValue("0:00:02")) Application.SendKeys "%s" Next r End Sub

Now the problem is:
' Get the email address Email = Cells(r, 2) Since this information is on the setup page, is there any way I can tell the macro to look into a different sheet (sheet 17 in this case, and then row H) ?

Also it should only email the persons that are exactly 21 days from the cell I mentioned before.


I guess this is pretty advanced, but if someone could help I would greatly appreciate this! :help

Thanks!