Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Automatically sending email based on Excel values

    I have used this macro from elsewhere on this site to generate the email but the problem I have is when the calculation is done between the two dates it generates a lot of zero and this creates uneccessary emails. One of the fields does not have a date entered until later on and I would like to run this macro weekly

    I amended this line If Cells(r, 4) < 30 Then
    to less then 12.

    Is there anyway I can adjust it so it only send out to say greater than zero and less than 20?

    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

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    How about this?
    If Cells(r, 4) < 30 and Cells(r, 4) > 0 Then

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Hi Paul,

    Thanks for that it worked a treat though had to change it to >= for some reason.

    One last question

    Your license will expire on " & Cells(r, 3) & "."

    Is there anyway after R3 I can set it pull in another cell value?

    I did try after the Your license will expire on " & Cells(r, 3) & Cells(r, 10) & "." but it didn't work

    I would like to achieve if possiable is

    "your licence expires "cell value" "and you need to submit the application by" "cell value"
    Last edited by madhatter; 2015-12-17 at 04:46.

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    I can't see why you'd need >= as that should get zero values as well. Maybe it's an evaluation order thing, try brackets.
    If (Cells(r, 4) < 30 and Cells(r, 4) > 0) Then

    "Your license will expire on " & Cells(r, 3) & Cells(r, 10) & "." should work.
    Try this to see if it's the cell contents.
    "Your license will expire on " & Cells(r, 10) & "."

    cheers, Paul

Posting Permissions

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