Solved

Generate emails from Excel on certain dates

Posted on 2009-03-31
9
184 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Create Professional Looking Email Signatures

Create "Professional HTML Email Signatures" with ease.
7 Day Money Back Guarantee if not 100% Satisfied.
Affordable - Try it out for 7 Days Totally Risk Free.
Installers provided for over 45 Email clients.
Both Windows & MAC Supported.
Highly Recommended!

 
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 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