Help getting excel to send emails on a date

[ryan james hardy]

Hi,
I need some help getting excel to send emails to a list of people dependent on the dates in another column. Preferably this would happen automatically but if i have to open the excel file and click a button that would be ok to. Bascially the macros would check column C for dates, and if the date is 3 days away then send an email to the email address in column B. Ideally the email would be automatically populated with a standard response or the macros could get the email subject/msg from cell data.
thanks in advance,
Ryan

 

[daverunt]

Hi,

I did something similar here so I have just tweaked that for you to work with.
The cell is coloured when a mail is sent so it doesn't keep sending mails when you open the file.

You can put it in the ThisWorkbook of the project so it fires when you open the workbook.
This could be done with Scheduled tasks on a daily basis.

There's no compensation for weekends getting in the way. It will work if the mail due date is 3 days or less away.

http://www.mrexcel.com/forum/excel-questions/641177-send-email-based-date.html

Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
 
Private Sub Workbook_Open()
      
Set Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
      
For Each cell In Rng
If cell.Value <> "" Then

'Work out date
    cell.Activate
    Dte = cell.Value
    
   MailDte = DateAdd("d", -3, Dte)
    If Date >= MailDte And ActiveCell.Interior.ColorIndex = xlNone Then
        mail = True
    If mail = True Then
        ActiveCell.Interior.ColorIndex = 36
    End If
'Subject string
    EmailSubject = "Put the subject here"
    EmailSendTo = cell.Offset(0, -1) ' Column B
    MailBody =  "Puit your mail body here or get it from a cell value"
 
'Send Mail
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            '.bcc
            .body = MailBody
            .Display
            '.send
        End With
 
        Set OutMail = Nothing
        Set OutApp = Nothing
 End If
    End If
Next
'Put in so colour changes saved, mail won't be sent again on open/run
ActiveWorkbook.Close SaveChanges:=True
End Sub

 

[ryan james hardy]

Hi,

I did something similar here so I have just tweaked that for you to work with.
The cell is coloured when a mail is sent so it doesn't keep sending mails when you open the file.

You can put it in the ThisWorkbook of the project so it fires when you open the workbook.
This could be done with Scheduled tasks on a daily basis.

There's no compensation for weekends getting in the way. It will work if the mail due date is 3 days or less away.

http://www.mrexcel.com/forum/excel-questions/641177-send-email-based-date.html

Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
 
Private Sub Workbook_Open()
      
Set Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
      
For Each cell In Rng
If cell.Value <> "" Then

'Work out date
    cell.Activate
    Dte = cell.Value
    
   MailDte = DateAdd("d", -3, Dte)
    If Date >= MailDte And ActiveCell.Interior.ColorIndex = xlNone Then
        mail = True
    If mail = True Then
        ActiveCell.Interior.ColorIndex = 36
    End If
'Subject string
    EmailSubject = "Put the subject here"
    EmailSendTo = cell.Offset(0, -1) ' Column B
    MailBody =  "Puit your mail body here or get it from a cell value"
 
'Send Mail
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            '.bcc
            .body = MailBody
            .Display
            '.send
        End With
 
        Set OutMail = Nothing
        Set OutApp = Nothing
 End If
    End If
Next
'Put in so colour changes saved, mail won't be sent again on open/run
ActiveWorkbook.Close SaveChanges:=True
End Sub

Cool, thanks ill give it a go. its been a while since i played around with VBA buttons but i should be able to get it working.

 

[ryan james hardy]

Hi,

I did something similar here so I have just tweaked that for you to work with.
The cell is coloured when a mail is sent so it doesn't keep sending mails when you open the file.

You can put it in the ThisWorkbook of the project so it fires when you open the workbook.
This could be done with Scheduled tasks on a daily basis.

There's no compensation for weekends getting in the way. It will work if the mail due date is 3 days or less away.

http://www.mrexcel.com/forum/excel-questions/641177-send-email-based-date.html

Dim OutApp As Object
Dim OutMail As Object
Dim EmailSubject As String
Dim EmailSendTo As String
Dim MailBody As String
 
Private Sub Workbook_Open()
      
Set Rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
      
For Each cell In Rng
If cell.Value <> "" Then

'Work out date
    cell.Activate
    Dte = cell.Value
    
   MailDte = DateAdd("d", -3, Dte)
    If Date >= MailDte And ActiveCell.Interior.ColorIndex = xlNone Then
        mail = True
    If mail = True Then
        ActiveCell.Interior.ColorIndex = 36
    End If
'Subject string
    EmailSubject = "Put the subject here"
    EmailSendTo = cell.Offset(0, -1) ' Column B
    MailBody =  "Puit your mail body here or get it from a cell value"
 
'Send Mail
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(o)
        With OutMail
            .Subject = EmailSubject
            .To = EmailSendTo
            '.bcc
            .body = MailBody
            .Display
            '.send
        End With
 
        Set OutMail = Nothing
        Set OutApp = Nothing
 End If
    End If
Next
'Put in so colour changes saved, mail won't be sent again on open/run
ActiveWorkbook.Close SaveChanges:=True
End Sub

Hi,
thanks for trying but it says there is an error with the line that says "MailDte = DateAdd("d", -3, Dte)". Any idea what might be causing this? I went to create macro etc and when i open the file it trys to run the macro (saying there is an error at line 13) but when i go into macros it isn't showing under "this workbook" or "all open work books"

 

[ryan james hardy]

Hi,
thanks for trying but it says there is an error with the line that says "MailDte = DateAdd("d", -3, Dte)". Any idea what might be causing this? I went to create macro etc and when i open the file it trys to run the macro (saying there is an error at line 13) but when i go into macros it isn't showing under "this workbook" or "all open work books"

I found out the error is because i had a header up the top that said date. Is there anyway around this? Also, is there a way to edit the file because at the moment it closes automatically after opening the email program. Currently i have to click send for the emails to go but i was wondering if there is a way to have outlook send the emails automatically. Also, is it possible to have a unique email message/subject for each row?
Thanks in advance, its been a great help so far by the way.

 

[daverunt]

Hi,

If you will be closing the file yourself just have it save at the end of the macro.

ActiveWorkbook.Save instead of ActiveWorkbook.Close SaveChanges:=True

Comment out Display and un-comment Send to send without displaying the mail first.


If the message you want to add is in a cell on the same row then you can use the Offset.
The reference cell is the date cell in Column C on each row in turn.
You can see that the EMailSendTo uses:
cell.Offset(0, -1) ' Column B
to get the required address in column B

A minus means you are moving left of the reference cell. (0, 2) would mean 2 columns to the right in the same row i.e column E.

Same for the subject.

I'm not sure about the Date header problem I would have to see the code.

 

[ryan james hardy]

Hi,

If you will be closing the file yourself just have it save at the end of the macro.

ActiveWorkbook.Save instead of ActiveWorkbook.Close SaveChanges:=True

Comment out Display and un-comment Send to send without displaying the mail first.


If the message you want to add is in a cell on the same row then you can use the Offset.
The reference cell is the date cell in Column C on each row in turn.
You can see that the EMailSendTo uses:
cell.Offset(0, -1) ' Column B
to get the required address in column B

A minus means you are moving left of the reference cell. (0, 2) would mean 2 columns to the right in the same row i.e column E.

Same for the subject.

I'm not sure about the Date header problem I would have to see the code.

Thanks for your help. It basically works now, except for the date header thing. I also found code like this combines cell data with a text string. MailBody = "This is a 60 day reminder message to advise that a new license is required for the following crew member as per the subject header. " & "" & cell.Offset(0, -2). How did you learn VBA code and is there a source you'd recommend i check out.
Thanks for the help.
Ryan

 

[daverunt]

Hi,

You can post your code if you want the date problem looking at.

I have purchased books on VBA but in all honesty rarely use them. I find there isn't anything you can't find on the internet about VBA and in particular this site.
I personally find the quickest way to learn VBA is to find a use for it at work or at home. If I have a project I am more likely to stick with it than just sitting there writing code from a book for no apparent reason. Some people can do it that way. Come to the site when you hit a hurdle, which you have already done. Also take some of the questions on here and try and solve them yourself. Compare your result with the posted answer. Eventually you will want to respond. Don't be afraid of making an error, you can learn from errors too.

 

[JimH]

This is exactly what I have been looking for too.

If I want to restrict the range from C10 to C109, how would I do that? Could I just modify the Set Rng line?

 

[daverunt]

Hi Yes,

Set Rng=Range(C10:C109).Value