Auto Emails through VBA on Specific Date

Hi Guys,

I have searched the forum and I couldn't find any specific/close topics.
http://www.excelforum.com/excel-prog...-in-excel.html
http://www.excelforum.com/excel-prog...ing-dates.html
http://www.excelforum.com/excel-prog...rom-excel.html
http://www.excelforum.com/excel-prog...tain-date.html


Basically, I have a file which has column of deadlines. I need Excel to auto send emails to me with the data corresponding to that deadline.
I have attached sample data. The Red Column are the deadlines, the data in the corresponding yellow cells are what I need emailing to me.

Date of email to be sent: 7 days before deadline [column A]
Time for emails to be sent: 09:30am
To: Me@abc.com (testing purposes)
Email Subject: "Product Deadline Approaching: [Date of deadline]"
Body of email:

"The below products are expiring

Deadline: [DATE - Cell A4]
Cycle:[DATE - Cell B4]

Product Deadlines
All Products
T&C: [DATE - Cell C4]
Ideal: [DATE - Cell D4]
Late with agreement: [DATE - Cell E4]

Primary Products
All Products
Product A: [DATE - Cell G4]

Primary Products
Other Products
Product B: [DATE - Cell H4]
Product C: [DATE - Cell I4]
Product D: [DATE - Cell J4]

Manufacturing Date
Product A: [DATE - Cell L4]
Product B [DATE - Cell M4]
Product C [DATE - Cell N4]

Thanks"

let me know if you need more info

Thank you for your help :)


Tai

This code can be used to delay send an email:

Option Explicit

Sub Send_Email_Excel_Attachment_Late_Binding()

    Dim outlookApp As Object
    Dim outlookMail As Object
    
    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)
    
    With outlookMail
        .To = "notme@yahoo.com"
        .CC = ""
        .BCC = ""
        .Subject = "How to send email from Excel with Workbook as attachment using VBA"
        .BodyFormat = 2
        .HTMLBody = "Hi,<p>I 'm sending this message from Excel using VBA.<p>Please find <strong>a sample Excel workbook</strong> attached. You should be getting this just after 10 am on the 27th."
        .Attachments.Add ActiveWorkbook.FullName
        '.DeferredDeliveryTime = "11/27/2016 10:00:00 AM"  '<-- uncomment this line and reference cells Col A
        '.Importance = 2
        .ReadReceiptRequested = True
        .Send
    End With
    
    Set outlookMail = Nothing
    Set outlookApp = Nothing
MsgBox "Your message was sent", vbExclamation & vbOKOnly, "Email Sent"
End Sub

But it is a manual approach.

A better approach might be to establish a 'count down' scenario based on the DATE. When the deadline date is reached, the macro auto-calls your email macro which draws from the list of criteria
based in your table beginning with Column A. When you start your program each morning, the macro compares the current date with the list and sends the appropriate emails.

Re: "countdown"

Option Explicit
Sub FindMethod_SearchDate()

'Search for a date within a range
'user enters the date he wants to find in Range ("A")
'user wants to find the date in search Range ("A1:A30")
 
Dim rngFound As Range, rngSearch As Range, rngLast As Range
Dim strDate As String
 
'search range to find the date:
Set rngSearch = ActiveSheet.Range("A1:A30")
 
Set rngLast = rngSearch.Cells(rngSearch.Cells.Count)
 
'set variable for current date
strDate = Date
 
'CDate converts a number or text string to a Date data type. CDate(40200) returns "1/22/2010"; CDate("October 15, 2009") returns "10/15/2009"; CDate("2:25:15 PM") returns "2:25:15 PM"; CDate("hello") returns an error.
Set rngFound = rngSearch.Find(What:=CDate(strDate), After:=rngLast, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 
    If Not rngFound Is Nothing Then
        'return range address ($A$5 - in this example) if date is found:
        MsgBox "Sending emails. "
        '<-- place call to your email macro here.
    Else
        'if date is not found in search range:
        MsgBox "Nothing to email. "
        Exit Sub
        End If

End Sub

Re: Which email to send based on date ... look at this:

http://stackoverflow.com/questions/3...-based-on-date

	
Here's what I think could be a solid start. You'll obviously have to resolve what email address the message should be sent to and how to format the body and whatnot.

The range given to r was based on the sample data you provided, which occupied A2-A4, but change this to whatever is correct.

Option Explicit

Sub email()

    Dim r As Range
    Dim cell As Range

    Set r = Range("A2:A4")

    For Each cell In r

        If cell.Value = Date Then

            Dim Email_Subject, Email_Send_From, Email_Send_To, _
            Email_Cc, Email_Bcc, Email_Body As String
            Dim Mail_Object, Mail_Single As Variant

            Email_Subject = "subject"
            Email_Send_From = "bob@bob.com"
            Email_Send_To = "bob@bob.com"
            Email_Cc = "bob@bob.com"
            Email_Bcc = "bob@bob.com"
            Email_Body = "body"

            On Error GoTo debugs
            Set Mail_Object = CreateObject("Outlook.Application")
            Set Mail_Single = Mail_Object.CreateItem(0)
            With Mail_Single
            .Subject = Email_Subject
            .To = Email_Send_To
            .cc = Email_Cc
            .BCC = Email_Bcc
            .Body = Email_Body
            .send
            End With

        End If

    Next


    Exit Sub

debugs:
        If Err.Description <> "" Then MsgBox Err.Description
End Sub

Hi Logit,

Thanks for the attachment, i understand. Although, I am still confused about the body and subject of the email. The reason I want it to generate the email is because the sample data contains 5 rows, and my data is annual so having an email with the body that matches the date would be great help.

Body of email:

"The below products are expiring

Deadline: [Value in Cell A4]
Cycle:[Value in Cell B4]

Product Deadlines
All Products
T&C: [Value in Cell C4]
Ideal: [Value in Cell D4]
Late with agreement: [Value in Cell E4]

Primary Products
All Products
Product A: [Value in Cell G4]

Primary Products
Other Products
Product B: [Value in Cell H4]
Product C: [Value in Cell I4]
Product D: [Value in Cell J4]

Manufacturing Date
Product A: [Value in Cell L4]
Product B [Value in Cell M4]
Product C [Value in Cell N4]

Thanks"

The above email is based on if today's date matched A4

Thank you so much for your time.

Tai

Review these resources. The process can be used for any of the email entries (TO, FROM, BODY, CC, BCC, etc).

http://stackoverflow.com/questions/2...r-selected-row

http://stackoverflow.com/questions/2...excel-contents

http://www.rondebruin.nl/win/s1/outlook/bmail5.htm



Look at the TIPS section in this resource:
http://www.rondebruin.nl/win/s1/outlook/bmail2.htm




I searched for the above URLs using this: VBA Email body reference cell row content


Take your time, study the resources. Everything you need is there. When you have something put together and need assistance come on back for more answers.

Best

Hi Logit,

This is great, thanks for all your time and help.
it's making sense. I will get back once I have generated a viable code

Thanks
Tai

Hi Logit,

Thank you so much for your help. I tweaked it a little but it fully works :D

I also added the below so I can select a range of email addresses rather than having a fixed person.

Really appreciate your help and supportive comments. I am amateur to be honest, I just read the sites you gave and put the pieces together

Thanks

Dim mystore As String
Dim rng As Range, fullrng As Range
Set fullrng = Selection

        For Each rng In fullrng
                mystore = mystore & ";" & rng.Value
                
        Next

.To = mystore

Keep up the great work ! Nicely done.

If you haven't already, please mark this thread as solved. Up top, right side, THREAD TOOLS

Thanks Logit
Speak soon

Tai