Solved sending automated emails from excel

September 1, 2013 at 19:10:31
Specs: Windows 7

I have a few worksheets with expiry dates, I was wondering if there was a way to set up an email reminder when due dates are approaching - and then again when due dates have passed.
all of my due dates in are in column D, but not every row, they skip rows if that means anything. I just need the emails to come to me, not to everyone these dates belong to.

(I've already read these, but they didn't help me -

Thanks very much for any further help

See More: sending automated emails from excel

Report •

September 2, 2013 at 07:04:45
✔ Best Answer
I have something similar that i use at work, but i have since re created this in VB6 and therefore cannot remember exactly how i did this in excel, but saying that here is something i would probably do.

1) Your due dates are in column D
2) Use a free column for todays date '=Today()'
3) use another column to calculate the days between today and you due date for example

1	D	                  E	                  F
2	30/09/2013	02/09/2013	28

in this example D is my due date, E is todays date, and F is the difference in days between the two, so i would make macro to go through all the records and then send an email when F is for example 10 days. If you wanted to calcualte the dates difference dynamically you can use the DateDiff or even DateAdd function in VBA. You can then use the below code to generate an email which will automatically be sent.

Sub Mail_Workbook_1()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hello World!"
        .Attachments.Add ActiveWorkbook.FullName
        ' You can add other files by uncommenting the following line.
        '.Attachments.Add ("C:\test.txt")
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Report •
Related Solutions

Ask Question