Solved

Generate emails from Excel on certain dates

Posted on 2009-03-31
9
182 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:bpfsr
  • 5
  • 4
9 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24034231
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
 

Author Comment

by:bpfsr
ID: 24034312
Sure thing, attached here...

Thank you
Sample-workbook.xlsx
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24034908
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 24034955
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
 

Author Comment

by:bpfsr
ID: 24037809
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
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24037892
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
 

Author Comment

by:bpfsr
ID: 24038068
yes, I will shut it down at the end of the day 9 out of 10 days...
0
 

Author Closing Comment

by:bpfsr
ID: 31565088
Great work, super fast
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24044675
Thx for the grade :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question