Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically sending email based on Excel values:

    I'm working on a solution for my work, who is trying to track the licensing of medical professionals and have a fool-proof, centralized system for tracking these people from multiple sites. I've already designed an Excel spreadsheet that shows the individuals and calculates a "X days until renewal" value. I'm trying to write a script that will automatically pull these values and email the proper parties when they get within 60 days of their renewal. Can anyone help? I've played with various VBA scripts that I've seen online, but I'm not having the results I want.

    Let me know if more info is needed. Thanks!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically sending email based on Excel values:

    Welcome to Woody's Lounge!

    Could you attach a small sample workbook with dummy data to a reply? That would give us an idea what the data look like.

  3. #3
    New Lounger
    Join Date
    Dec 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically sending email based on Excel val

    Sure! Here's a real basic layout of what I'm doing. Not complex in the least. When the data hits certain parameters (here, I've set it to <30 days), conditional formatting will kick in to draw attention to those rows. However, I also want to write a script for Outlook that will check this Excel spreadsheet and email the people in column A regarding the number of days left until the licenses need renewal.

    Any help would be appreciated greatly. Thanks!
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically sending email based on Excel val

    Here is a rather simple solution:

    Sub SendMail()
    Dim r As Long
    Dim m As Long
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 4 To m
    If Cells(r, 4) < 30 Then
    ActiveWorkbook.FollowHyperlink "mailto:" & Cells(r, 1) & _
    "?Subject=License about to expire&Body=Dear " & _
    Cells(r, 2) & ", your license will expire on " & _
    Cells(r, 3) & "."
    End If
    Next r
    End Sub

    It should work with any version of Excel and any e-mail client. See the attached version of the sample workbook. I have placed a button on the worksheet (from the Forms toolbar) that will call the macro.

    If your needs are more complex, you could automate Outlook instead.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Dec 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically sending email based on Excel val

    Thanks, but is there a more automated way to do this? I basically want a macro in Outlook to check the spreadsheet without a human having to open Excel, and automatically email the people involved. Is this possible?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically sending email based on Excel val

    I'm not sure that running code from Outlook is a good idea. Someone will have to run the macro anyway, unless you create some kind of scheduled task that runs at specified times.

    Here is a version (still to be run from the workbook) that will send the e-mails through Outlook. You'll run into Outlook's security guard, there are several ways to handle this, see <post:=488,173>post 488,173</post:>.

    Sub SendMail()
    Dim r As Long
    Dim m As Long
    Dim objOL As Object
    Dim objMsg As Object
    Dim blnStart As Boolean

    On Error Resume Next

    Set objOL = GetObject(, "Outlook.Application")
    If objOL Is Nothing Then
    Set objOL = CreateObject("Outlook.Application")
    If objOL Is Nothing Then
    MsgBox "Can't start Outlook", vbExclamation
    Exit Sub
    End If
    blnStart = True
    End If

    On Error GoTo ErrHandler

    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 4 To m
    If Cells(r, 4) < 30 Then
    Set objMsg = objOL.CreateItem(0)
    With objMsg
    .Recipients.Add Cells(r, 1)
    .Subject = "License about to expire"
    .Body = "Dear " & _
    Cells(r, 2) & "," & vbCrLf & vbCrLf & _
    "Your license will expire on " & Cells(r, 3) & "."
    .Send ' use .Display while testing.
    End With
    End If
    Next r

    ExitHandler:
    On Error Resume Next
    Set objMsg = Nothing
    If blnStart Then
    objOL.Quit
    End If
    Set objOL = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  7. #7
    New Lounger
    Join Date
    Dec 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically sending email based on Excel val

    Great, as an autorun that works perfectly. My only problem at this point then is that I'm looking for a way to lock this macro so it only works when certain people open the file, and I also don't want it to email people about the same row over and over again. Any ideas there? Thanks!

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically sending email based on Excel val

    You could look at Environ("username"), this is the user's login name.
    Or you could ask for a password at the beginning of the macro.

    To prevent sending out duplicate mails, you can use an extra column in which the macro enters TRUE if an e-mail was sent. If the column already contains TRUE, the row will be skipped:

    ...
    For r = 4 To m
    If Cells(r, 4) < 30 And Not Cells(r, 5) = True Then
    Set objMsg = objOL.CreateItem(0)
    With objMsg
    .Recipients.Add Cells(r, 1)
    .Subject = "License about to expire"
    .Body = "Dear " & _
    Cells(r, 2) & "," & vbCrLf & vbCrLf & _
    "Your license will expire on " & Cells(r, 3) & "."
    .Send ' use .Display while testing.
    Cells(r, 5) = True
    End With
    End If
    Next r
    ...

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Automatically sending email based on Excel val

    <P ID="edit" class=small>(Edited by JohnBF on 20-Dec-07 10:17. )</P>On your second question, I would set column E to record the date that the message is sent. Change the code core as follows:

    Using the simple Excel based version:

    If Cells(r, 4) < 30 And (Cells(r, 5) = 0 Or DateDiff("d", Cells(r, 5), Date) > 330) Then
    ActiveWorkbook.FollowHyperlink "mailto:" & Cells(r, 1) & _
    "?Subject=License about to expire&Body=Dear " & _
    Cells(r, 2) & ", your license will expire on " & _
    Cells(r, 3) & "."
    Cells(r, 5) = Date
    End If

    In the Outlook-called version, change these lines (retaining Hans "With ..." through "End With" Code):

    For r = 4 To m
    If Cells(r, 4) < 30 And (Cells(r, 5) = 0 Or DateDiff("d", Cells(r, 5), Date) > 330) Then
    Set objMsg = objOL.CreateItem(0)
    ...
    End With
    Cells(r, 5) = Date
    End If
    Next r

    The additional "If" logic checks if either no reminder message was ever sent, or the last reminder message sent was over 330 days ago. Format Column E as date.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Automatically sending email based on Excel val

    Further to Hans comments on restricting the use of the macro, see <!post=This Excel Thread,612084>This Excel Thread<!/post>.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •