# Solved: Reminder date in Excel sends an e-mail



## rhartlieb (May 9, 2012)

Hello. 
I have been asked  to send warning e-mails to a short list of adresses from an Excel file where reminder dates correspond to the actual date, and I have no clue how to do this with Macros. Iv tryed to understand how some examples of macros dealing with Excel and e-mails with no success as I still have a lot to learn

Here is my problem : I have a spreadshhet containing company names (col.A) and contract renewal dates (col.D). there are about 80 companies listed. When the actual date corresponds to the reminder date an e-mail containing the company name, a fixed blabla and the reminder date should be send to 3 known e-mail adresses.
Could this be done with an Excel Macro 
Im using Windows 7 32bits, Office 2010 with Outlook as mailing system.

Many thanks in advance.
Robert


----------



## scotty718 (Nov 19, 2010)

Excel VBA has a Workbook.SendMail function that is pretty basic that you can use to send a mail to recipients with a subject. I don't think you can fill any text into the body, but you can place all that string into the subject. Alternatively, you can make the e-mail more robust by interacting with Outlook itself through VBA. 

The only question I have is what triggers the e-mail. Does it happen as soon as the workbook is open? Is there a button push you want to check the dates... Something has to trigger the event that sends the mail.


----------



## halbalooshi (May 14, 2012)

try this code
it's a vbs not vba so paste it on notepad and save it with the extinction .VBS
after you run it, it will check the excel file and send an email if the date (colum L:L here) is within the next 7 days (u can change it)
play with the red text till you get wat you want

Dim objExcel 
Dim objOutlook 
Dim objMail 
Dim objWB 
Dim objWS 
Dim vCell

Set objExcel = CreateObject("Excel.Application")
Set objOutlook = CreateObject("Outlook.Application")

objExcel.DisplayAlerts = False
objExcel.Workbooks.Open ("Q:\Test2.xls")
Set objWB = objExcel.Activeworkbook
Set objWS = objWB.ActiveSheet
For Each vCell In objWS.Range("L1:L" & objWS.Cells(objWS.Rows.Count, "L").End(-4162).Row).Cells

If (vCell) <= 7 AND (vCell) >= 0 Then
If vCell.Offset(0, 1).Value <> "YES" Then
Set objMail = objOutlook.CreateItem(olMailItem)

objMail.To = "[email protected]"
objMail.Cc = "[email protected]"
objMail.Subject = vCell.offset(0, -4).Value & " Email Alert"

objMail.Body = " - " & vCell.offset(0, -8).Value & vbCrLf & _
" bla" & vCell.offset(0, -4).Value & vbCrLf & _
" - " & vCell.offset(0, -3	).Value & vbCrLf & _
" - " & vCell.offset(0, -5).Value & vbCrLf & _
" bla- " & vCell.offset(0, -6).Value & vbCrLf & _
" - " & vCell.offset(0, -11).Value & vbCrLf & _
" " & (vCell)

objMail.Send
vCell.Offset(0, 1).Value = "YES"
End If
End If
Next

objWB.Save
objWB.Close
objExcel.Quit

Set objExcel = Nothing
Set objWB = Nothing
Set objWS = Nothing
Set objMail = Nothing
Set objOutlook = Nothing

note: i only tried it with excel 2007 and windows xp s3 so can't be sure if it will work with u


----------



## rhartlieb (May 9, 2012)

Hello Scotty718
The only question I have is what triggers the e-mail. Does it happen as soon as the workbook is open? Is there a button push you want to check the dates... Something has to trigger the event that sends the mail.
Yes, I was asking myself this question... I think that an explicit button would be the best as the date reminder alarm could be trigged by other (selected) users...
Many thanks in advance.


----------



## rhartlieb (May 9, 2012)

Hello Halbalooshi,
try this code
"it's a vbs not vba so paste it on notepad and save it with the extinction .VBS
after you run it, it will check the excel file and send an email if the date (colum L:L here) is within the next 7 days (u can change it)
play with the red text till you get wat you want"...
Many thanks for your routine, Splendid!
I made the needed change, but I had an Unknown execution error VBScript (code 800A03EC) on line 23 Caract.1...?!


----------



## scotty718 (Nov 19, 2010)

Excel solution is attached as a basic example, but should be about all you need. Since the VBScript could be useful in the fact that it can sit on someone's desktop and they never have to see or interact with the Excel file, I will fix that code up for you as well.


----------



## rhartlieb (May 9, 2012)

Great Scotty718,
What is VBS ? It looks similar as VBA... Where can I find informations or help about it ? 
Your help is very much appreciated.


----------



## scotty718 (Nov 19, 2010)

VBS is Visual Basic Scripting. Visual Basic was the original language of Visual Basic for Application (VBA) - which primarily exists as a programming tool inside Microsoft Office Products. As far as where can you find information about it... Google Searches! There is a plethora of information as the language is very old and established.

With all due respect to halbalooshi and his helpfulness, I think he basically posted some code he used from another project without editing it down to be more clear for how it might apply to yours specifically, which hurts you as a novice, because you may not really understand how to alert or what is even happening. That is why your code broke. I am working now to alter and make it a little easier for to follow and hopefully adjust.


----------



## scotty718 (Nov 19, 2010)

Here is vbScript. Same directions to get going as other file. Save in Notepad as a .vbs file (make sure file type is set to All Files). Then run.

A few notes:


Right now the code is set up to just display the e-mail. This way you can test without sending


This code is set against the Excel file I attached previously. You will need to adjust to your file location, file name, sheet name, range references, etc.

Script below:


```
Dim objExcel, objOutlook 
Dim objMail, objWB, objWS, vCell, objRng

Set objExcel = CreateObject("Excel.Application")
Set objOutlook = CreateObject("Outlook.Application")

objExcel.DisplayAlerts = False
objExcel.Workbooks.Open ("P:\VBA\Help\SendMail.xlsm")

Set objWB = objExcel.Activeworkbook
Set objWS = objWB.ActiveSheet
Set objRng = objWS.Range("B1:B" & objWS.Cells(objWS.Rows.Count,"B").End(-4162).Row).Cells
                                       '-4162 is equivalent to using down arrow to find last cell in range

For Each vCell In objRng

	If (vCell) <= date Then

		Set objMail = objOutlook.CreateItem(olMailItem)
		
		With objMail
			.To = "[email protected]; [email protected]; [email protected]"
			.Subject = vCell.offset(,-1).Value & " Email Alert"
			.Body = "This is the body of my e-mail"
			.Display 
			'.Send 'remove apostrophe in front to actually send mail
		End With

	End If

Next


objWB.Save
objWB.Close
objExcel.Quit

Set objExcel = Nothing
Set objWB = Nothing
Set objWS = Nothing
Set objMail = Nothing
Set objOutlook = Nothing
```


----------



## rhartlieb (May 9, 2012)

Dear Scotty718,
This is absolutely great, I would never think I could get such an efficient and fast help !
May I still ask you if you could explain to me how things works in this VBA sentence ?
Set rngWorking = Intersect(.UsedRange, .UsedRange.Offset(1), .Columns(2).EntireColumn)
(As I would like to become more knowledgeable in VBA ;-)
Again, many many thanks!


----------



## rhartlieb (May 9, 2012)

Hello Again,
I tryed to mail to several mail addresses, but it gives me an error 1004 when it reads the second email address:
SendMail "[email protected];[email protected];[email protected]", strSubj
Is this a problem with Outlook 2010?


----------



## scotty718 (Nov 19, 2010)

Yes, I haven't used SendMail in a looooong time and now I realize you can only use 1 e-mail address.

There are several options you have, which I won't re-write, but rather I will point you here - http://www.ozgrid.com/forum/showthread.php?t=81023

The other option is to build a more robust e-mail engine by incorporating code that talks directly to outlook. To do that, you can mix in the code from the vbScript that deals with sending the e-mail. Make sure you have a Reference Set to Microsoft Outlook in your VBE, under Tools, References.

As far as your first question about

```
Set rngWorking = Intersect(.UsedRange, .UsedRange.Offset(1), .Columns(2).EntireColumn)
```
Think of the Intersect method as a Venn Diagram. In this specific instance we are set a range variable to the intersection of the entire used range of the sheet (the range that encompasses the upper left most cell with data / formatting to the lower right most), the used range offset by 1 row (to ignore header columns) and column B (2nd column) in it's entirety. So, in essence, you are ending up with Range("B2:B(whatever the last used row is)"). It may seem like a long way to write to it to the novice, but its very efficient as it doesn't require code change if the values in your sheet change.


----------



## rhartlieb (May 9, 2012)

You'r great Scotty718 , cristal clear! Many thanks !
I used a turn-around with the security limitation of outlook by creating a group adress in outlook and this works 
I still have a last smal problem is that on some lines there is no date at all and the VBA routine detects it as a zero so logically as lower as the actual date it is compared to, so it sends a non wanted warning mail. I tryed a turn around by setting an empty date cell as year 3000, but this makes it a bit messy...
Would you have a hint ?


----------



## scotty718 (Nov 19, 2010)

Glad you are thinking of ways to solve your problems. I do have a less messier hint and its through the code. I will tell you what to do, and you can figure out how to implement. Set a condition that skips the cell if its a blank. 

Search around for the code to write that. If you get stuck, hit me back.


----------



## rhartlieb (May 9, 2012)

Hello Scotty718,
Voilà : If cel <= Date And cel > 0 Then
Again, many many thanks for your help and advise. I bought myself a guide for Excel and VBA and I will now study it... My brain is maybe getting older but should be able to cope ;-)
All the best,
Robert


----------



## scotty718 (Nov 19, 2010)

Great stuff. The book will be helpful to get a foundation, but I've found google and forums like these to be the most valuable resource to figure my programming problems out.


----------

