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 Macros. Iv 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 Im using Windows 7 32bits, Office 2010 with Outlook as mailing system.
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.
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)
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.
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...?!
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.
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.
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:
Code:
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 = "1@e.com; 2@e.com; 3@e.com"
.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
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!
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 "email1@email.com;email2@email.com;email3@email.com", strSubj
Is this a problem with Outlook 2010?
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
Code:
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.
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 ?
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.
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
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.
Status
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!