1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Reminder date in Excel sends an e-mail

Discussion in 'Business Applications' started by rhartlieb, May 10, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. rhartlieb

    rhartlieb Thread Starter

    Joined:
    May 9, 2012
    Messages:
    10
    Hello.
    I have been asked :eek: 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 :eek:
    I’m using Windows 7 32bits, Office 2010 with Outlook as mailing system.

    Many thanks in advance.
    Robert
     
  2. Sponsor

  3. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    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.
     
  4. halbalooshi

    halbalooshi

    Joined:
    May 14, 2012
    Messages:
    1
    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
     
  5. rhartlieb

    rhartlieb Thread Starter

    Joined:
    May 9, 2012
    Messages:
    10
    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.
     
  6. rhartlieb

    rhartlieb Thread Starter

    Joined:
    May 9, 2012
    Messages:
    10
    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...?!
     
  7. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    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.
     

    Attached Files:

  8. rhartlieb

    rhartlieb Thread Starter

    Joined:
    May 9, 2012
    Messages:
    10
    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.
     
  9. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    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.
     
  10. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    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 = "[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
    
     
  11. rhartlieb

    rhartlieb Thread Starter

    Joined:
    May 9, 2012
    Messages:
    10
    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!
     
  12. rhartlieb

    rhartlieb Thread Starter

    Joined:
    May 9, 2012
    Messages:
    10
  13. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    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
    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.
     
  14. rhartlieb

    rhartlieb Thread Starter

    Joined:
    May 9, 2012
    Messages:
    10
    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 ?
     
  15. scotty718

    scotty718

    Joined:
    Nov 19, 2010
    Messages:
    185
    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.
     
  16. rhartlieb

    rhartlieb Thread Starter

    Joined:
    May 9, 2012
    Messages:
    10
    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
     
  17. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1052651