Solved

Generate emails from Excel on certain dates

Posted on 2009-03-31
9
180 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now