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.

Email automatically sending to user when cell is at a certain Date Excel 2003

Discussion in 'Business Applications' started by iswear10, Dec 13, 2012.

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

    iswear10 Thread Starter

    Joined:
    Dec 13, 2012
    Messages:
    7
    Hi All!

    I am having major difficulty figuring out excel. I am using a spreadsheet and want excel to automatically send an email to the user in that row when a contract is expiring. Within the row I have the specified user's email, the end date of the contract, and when the reminder email should go out. I have tried playing around with Macros and VBA coding, but I have no idea what I am doing. I am using excel 2003. Any help would be greatly appreciated!! I am using Outlook as my email. Have questions please let me know!

    -J
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Welcome to TSG iswear. Hope you find some help and take the time to help others when the opportunity presents itself.

    If you search this site on "excel email' there are many downloadable examples of what your requesting. Find one of those and when you get to question post your specific issues. You'll have to be willing to 'play around' with macro and vba to succeed.

    Once you get going there's several folks on TSG that can keep you moving.
     
  3. iswear10

    iswear10 Thread Starter

    Joined:
    Dec 13, 2012
    Messages:
    7
    Hi draceplace,

    I have looked at other forums and have basically found what I am looking for. However, I do did some help with the coding. I want to include one more cell to be included in the email. I have tried to write some code but keep having issues. I want to include cell "V'" the contract number. I know that in the coding "V" is attached to something else, but I do not know how to change this around. Attached is a copy of the sample spreadsheet I'm working with. I am running on Windows XP and using Microsoft Excel 2003. Please let me know if you have any questions. Thanks!!
     

    Attached Files:

  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Hey good work iswear! I (attachment) made "V" column\cell a parameter that's passed to the email routine then added it to your subject and body. Also change the hardcoded date in in A1 to =Now() so it stays current.
     

    Attached Files:

  5. iswear10

    iswear10 Thread Starter

    Joined:
    Dec 13, 2012
    Messages:
    7
    Thank you! Thank you! That worked great. What would recommend that once the email is sent, it wont send again? (IE opening it the following day... and having the same email send again?)
     
  6. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I would make a column for "sent date" and update that column with Date value.
    After the call to the Function
    Cells(xRow, "AB").Value = Date

    Then make a check of that column with IF insided the loop.
     
  7. iswear10

    iswear10 Thread Starter

    Joined:
    Dec 13, 2012
    Messages:
    7
    Eeekks! That was just greek to me. I am trying to work on my inner nerd and still learning a lot about excel. I updated my spreadsheet of designating Column "AB" to date sent.... not sure how to enter in that information you provided. I really do appreciate all the help! You're a Star! (y)
     

    Attached Files:

  8. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Do you know how to view the macro?
     
  9. iswear10

    iswear10 Thread Starter

    Joined:
    Dec 13, 2012
    Messages:
    7
    By going to tools>marcro>macro (?)

    That's how I run them

    I view the code by going to tools>macro> Visual Basic Editor
     
  10. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Make the macro look like this.
    If Cells(xRow, xCol).Value <> "" Then
    If Cells(xRow, xCol).Value <= tDate Then
    ' MsgBox xRow & " - " & xCol & vbCrLf & Cells(xRow, 2).Value & Chr(10) & Cells(xRow, xCol + 1).Value
    Call MailPaymentNotice(Cells(xRow, "V").Value, Cells(xRow, "AA").Value, Cells(xRow, "Z").Value, Cells(xRow, xCol).Value, Cells(xRow, xCol + 1).Value)
    Cells(xRow, "AB").Value = Date
    Exit For
    End If
    End If

    Then add:
    If Cells(xRow, "AB").Value = "" Then

    where the other IFs are and add an End IF after the two thats already there.
     
  11. iswear10

    iswear10 Thread Starter

    Joined:
    Dec 13, 2012
    Messages:
    7
    Ahhh Fantastic!!! Thank you very much for your help!!
     
  12. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Glad to help, Please mark the thread as "Solved" its a button at the top.
     
  13. iswear10

    iswear10 Thread Starter

    Joined:
    Dec 13, 2012
    Messages:
    7
    Hi Dan,

    I just need help with one more thing. For Column "V', I would like the text that reflects in the email to be a hyperlink to the document. Is this going to be elaborate coding? If so, please help! Hope you had a great new years!
     
  14. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I know the first step would be to change the body to HTML like this
    .HTMLBody = "<html><Body>" & eBody & "<html><Body>"

    I'm not sure how to make the hyperlink display. Maybe someone will help us. Having a busy day!
     
  15. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Here is the code we are working with from Practice spreadsheet.
    Function MailPaymentNotice(ContractNo As String, mName As String, emailTO As String, pDate As Date, pAmmount As Double)
    Dim eSubject As String, eBody As String
    Dim App As Object, Itm As Object

    eSubject = "Reminder: Contract " & ContractNo & " Expiring"
    eBody = mName & ";" & vbCrLf & vbCrLf
    eBody = eBody & "Contract: " & ContractNo & " will be expiring in the next 90 days as of " & Format(pDate, "mm/dd/yyyy;@") & ". Please refer to the document retention tool. " & vbCrLf & vbCrLf
    eBody = eBody & "Thank you." & vbCrLf & vbCrLf

    Set App = CreateObject("Outlook.Application")
    Set Itm = App.CreateItem(0)
    With Itm
    .Subject = eSubject
    .To = emailTO
    .CC = ""
    .BCC = ""
    .HTMLBody = "<html><Body>" & eBody & "<html><Body>"
    ' .Attachments.Add (fullpath and filename for attachtment) ' Must be complete path
    .Display ' This property is used when you want
    ' the user to see email and manually send. Then
    ' comment out rest of code except &#8220;End With&#8221; statement
    ' and "End Sub" statement.
    ' .send
    End With
    Set App = Nothing
    Set Itm = Nothing
    End Function
     
  16. 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/1080803

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice