Solved

Generate emails from Excel on certain dates

Posted on 2009-03-31
9
185 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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Changing a few Outlook Options can help keep you organized!
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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