Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Generate emails from Excel on certain dates

Posted on 2009-03-31
9
Medium Priority
?
190 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
Often, the users face difficulty in accessing Outlook 2016 PST files on Windows 10 computer. One of the reasons behind it is the improper functioning of MS Outlook when the user tries to open it. MS Outlook suddenly stops working, or it will not op…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses

571 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