Sending email via Excel VBA code based on date in cell.

[DanR1245]

Hi everyone-

I have an excel workbook that has dates entered in cells. What I want to do is write some VBA code to automatically send an email 2 weeks after a date is entered in the final cell. I have all the code written to send the email based on the user clicking a button, but I am not sure if it is possible to do the automatic email based on a value being entered in the cell.

Thanks much.

 

[Excelboy_01]

Hello People
I have a rather different but related problem and I was wondering if anyone of you could help me out. Basically, I have an excel worksheet with different information about different individuals. It also have a column (say Column Y ), which contains their respective emails addresses.

I would like to be able sort out (usings say AutoFilter) a group of people and send them an email (preferable personalized email) without going through copying/pasting individual email addresses from excel to outlook. Does anyone have an I dea how I can do this?

Thanks for your time and help in advance

 

[zoz_128]

Hello experts,

i want to send the e-mail from excel immediatly on certain date "on weekly basis" without clicking macro button to execute the commans.

below is the code ... please help me !!!!
_______________________________________________________
Sub Mail_ActiveSheet()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String
' Build the string that you want to add.
StrBody = "Dear All," & vbNewLine & _
"Please find the attached IBS Implementation weekly Summary report for AUH & AAN" & vbNewLine & _
"BR" & vbNewLine & _
"Zeyad Essawy"


With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
' Next, copy the sheet to a new workbook.
' You can also use the following line, instead of using the ActiveSheet object,
' if you know the name of the sheet you want to mail :
' Sheets("Sheet5").Copy
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
' Determine the Excel version, and file extension and format.
With Destwb
If Val(Application.Version) < 12 Then
' For Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
' For Excel 2007-2010, exit the subroutine if you answer
' NO in the security dialog that is displayed when you copy
' a sheet from an .xlsm file with macros disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "You answered NO in the security dialog."
Exit Sub
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End If
End With
' You can use the following statements to change all cells in the
' worksheet to values.
' With Destwb.Sheets(1).UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False
' Save the new workbook, mail, and then delete it.
TempFilePath = Environ$("temp") & "\"
TempFileName = "IBS Implementation Weekly Summary Report" & " as of " _
& Format(Now, "dd-mmm-yy")

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
' Change the mail address and subject in the macro before
' running the procedure.
With OutMail
.To = "zeyad.essawy@hotmail.com"
.CC = ""
.BCC = ""
.Subject = "Updated IBS Implementation weekly report for AUH & AAN"
.Body = StrBody






.Attachments.Add Destwb.FullName
' You can add other files by uncommenting the following statement.
'.Attachments.Add ("C:\test.txt")
' In place of the following statement, you can use ".Display" to
' display the mail.
.Send
End With
On Error GoTo 0
.Close SaveChanges:=False
End With
' Delete the file after sending.
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[Biz]

I think you need modify code below using application on time to automatically run.

Sub RunEverySec()
Call RunSec
Calculate
End Sub
 
Sub RunSec()
nTime = Now + TimeValue("00:00:01")
Application.OnTime nTime, "RunSec"
End Sub
Sub StopIt()
Application.OnTime nTime, "RunSec", , False
End Sub

Hope this helps.

Biz

 

[excel explorer]

Hi Everyone,

I tried the piece of code attached in this thread and found that the email sits in my outbox if the condition of deferred time is not met.

Here is my scenario -
I am currently using the same logic to check the complete list of dates in a particular column and if its going to be overdue in another 2 days, an automatic email has to be sent. So, when I run the macro, for those date cells which do not satisfy the condition, the emails sit in my outbox and the rest gets delivered. But my question is how is the macro executed automatically? Is there any event to execute the macro automatically on daily basis?
Please throw some light on this!

Thanks for your support!

 

[excel explorer]

Hi Everyone,

I tried the piece of code attached in this thread and found that the email sits in my outbox if the condition of deferred time is not met.

Here is my scenario -
I am currently using the same logic to check the complete list of dates in a particular column and if its going to be overdue in another 2 days, an automatic email has to be sent. So, when I run the macro, for those date cells which do not satisfy the condition, the emails sit in my outbox and the rest gets delivered. But my question is how is the macro executed automatically? Is there any event to execute the macro automatically on daily basis?
Please throw some light on this!

Thanks for your support!

 

[Jason Blendowski]

What code(S) do i need to enter into which programs (Excel or Outlook) in order to have the following desired action:

If a cell (B3) value > number (13), an email message would be sent to one or more people with information from the worksheet (not the entire workbook or even the entire worksheet) within a range of cells in that cell's row(B3:B9) along with a predetermined message that would

I know that this may be confusing. If noone can understand what I am asking, then i will put it in a better context. Please, anyone help!!

 

[Zionsoft]

Damon 12yrs later your solution is still supporting people. Kudos!

Please i have similar problem, I need the VBA code to check the date on each column, if less than 90days from current day send mail to the corresponding email address with a range of rows.

It actuall a training matrix. containing NAMES, EMAIL ADDRESS, SEVERAL TRAINING ATTENDED AND THEIR EXPIRY DATE. I need on the click of a button check for any expiry date < 90DAYS FROM CURRENT DATE and send email to the personnel using the email from the sheet and microsoft outlook.

thanks for usual prompt support

 

[pedrinhu]

Hi,
I'm new here and I discovery VBA 2 weeks ago so please sorry if I ask something pathetic.
I've got some codes from blogs and forum and tried to put together as I needed. Nothing seems to work, it is obviously my lack of experience.
I'll explain my "little" problem.

I've got a list with equipment calibrated and on my Excel file I've got a column with expire date so I'd like when that date is due for calibration again if it could send an email remind me to do it.

Is that clear?
If someone could help me with that it would be so great!
If it is not clear I can send the file to be more understandable!

thanks a lot!

I know this post is now over 7 years old, but I see that it is still getting a lot of views, so for the benefit of those that still wonder how to do this, first create your email in Outlook (lots of postings on this site explaining how to do that), then before doing the Send, do

MailItem.DeferredDeliveryTime = Range("B4") + 14

to have it automatically send the email in two weeks after the date in cell B4. In this case your MailItem would be the name of the object variable you assigned the new email MailItem object to.

Damon