Generate emails from Excel on certain dates

Hello,
I keep a list of transactions in Excel which includes info on the transaction, i.e. item, price, cutomer name, etc. and the customer's email address. I'd like to generate a standard follow-up email to the customer say two weeks after the transaction. Is there a way I can do that from Excel?
bpfsrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
DaveConnect With a Mentor Commented:
Pls see attached file
This code on opening:
  1. Runs the macro 'TranSEmail'
  2. The code emails the recipients in column R if there is not a corresponding "yes" in Column S, and if the date in column A is more than two weeks old
  3. The code then places a "yes" in column S to avoid a repeat email
Pls change:
  • The message in the Body
  • change the .Display to .Send if you are happy to send the mails without seeing them. The current code does a Display so that you can review each email
Cheers
Dave


' ThisWorkbook module
Private Sub Workbook_Open()
    Call TranSEmail
End Sub
 
 
' normal module
 
Option Explicit
 
Sub TranSEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Cel As Range
 
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
 
    For Each Cel In Columns("R").Cells.SpecialCells(xlCellTypeConstants)
        If Now() - Cells(Cel.Row, "A") > 14 And LCase(Cells(Cel.Row, "S").Value) <> "yes" Then
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = Cel.Value
                .Subject = "Books!"
                .Body = "Dear " & Cells(Cel.Row, "C").Value _
                      & vbNewLine & vbNewLine & _
                        "Thank you for buying " & Cells(Cel.Row, "BV").Value _
                        & vbNewLine & vbNewLine & "Regards, " & vbNewLine & " Me"
                .Display
            End With
            Set OutMail = Nothing
             Cells(Cel.Row, "S").Value = "yes"
        End If
    Next Cel
    Application.ScreenUpdating = True
End Sub

Open in new window

Sample-workbook---djb.xls
0
 
DaveCommented:
Sure, it's straightforward to send email from Excel, see http://www.rondebruin.nl/sendmail.htm
In your cas, we need to indentify how to trigger the email, probably on Workbook open checking against dates, and using a market to esnure that the email is only sent once
Can you pls upload  a sample workbook
Cheers
Dave
0
 
bpfsrAuthor Commented:
Sure thing, attached here...

Thank you
Sample-workbook.xlsx
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
DaveCommented:
So in terms of process
  • You entere a transaction date in column A
  • When the file is opened it should send an email to the person in column R if the current date exeeds the column A date by two weeks (and if the person has not been emailed before)
Cheers
 
Dave
0
 
bpfsrAuthor Commented:
That's great, thank you. Two questions; can I use this macro in Excel 2007 as is and will it automatically send the emails at the beginning of the day each day or do I need to launch the macro?
0
 
DaveCommented:
The macro will run fine in Excel 2007
The macro will launch automatically when you open the file. Would you open it each day?
Cheers
Dave
 
0
 
bpfsrAuthor Commented:
yes, I will shut it down at the end of the day 9 out of 10 days...
0
 
bpfsrAuthor Commented:
Great work, super fast
0
 
DaveCommented:
Thx for the grade :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.