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.

Outlook Email from Excel based on date in Cell

Discussion in 'Business Applications' started by JNoji, May 11, 2018.

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

    JNoji Thread Starter

    Joined:
    May 11, 2018
    Messages:
    5
    Hello,

    I have seen posts similar to my requests but have been unable to put together a code to do everything I need it to do as I am very new to VBA coding.

    I am looking for a VBA code that will generate an email when the date in a cell of a column is 7 days or less from the current date. The email will be sent to a recipient listed in another cell column and cc my coworker. I also need a code to change the data in another cell to "Approve, Email Sent" when the same cell date is between 7 and 1 days from the current date and "Expired, Email Sent" when the date equals the current date so that an email will only send if this cell does not equal "Approved, Email Sent" or "Expired, Email Sent" so that emails are not repeatedly sent every time the spreadsheet is opened. I plan on running task scheduler to open the spreadsheet every Monday so the code will run weekly at minimum.

    Any help is greatly appreciated, thank you!
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi, welcome to the forum.
    Do you have VBA knwoledge?

    This module will take care of the mailing part (using Outlook)

    All you need to do is write the code for your selection (which you explained, so it shouldn't be that difficult) and then pass it to the Mailing module

    Code:
    Option Explicit
    
    Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
    Dim eSubject As String, Sendto As String, CCto As String, EBody As String
    Dim app As Object, Itm As Variant
    Set app = CreateObject("Outlook.Application")
    Set Itm = app.CreateItem(0)
    
    With Itm
        .Subject = mSubject
        .To = Sendto
        If Not IsMissing(CCto) Then .CC = CCto
        .Body = mMessage
    '     .Attachments.Add (NewFileName1) ' Must be complete path
        .Display ' This property is used when you want
    '    .send    ' This property sends the mail directly
    '    .Save   ' this property issued to save it as Draft for later processing, 
    End With
    Set app = Nothing
    Set Itm = Nothing
    End Function
    
     
  3. JNoji

    JNoji Thread Starter

    Joined:
    May 11, 2018
    Messages:
    5
    Hello Keebellah, I appreciate the response. I am very new to VBA and I am struggling putting together the code to perform the functions I explained. This is my first attempt at VBA coding.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    You've chosen a not too easy way .
    VBA is nothing more than logical thinking with a very basic and necessary requirement, well, at least two basic requirements, namely Time (very much of it) and Imagination (as much as possible)
    Apart from that it's no more than a series of actions that are executed one after the other with conditions and calculations.
    If you write down the actions you want done in words like you would a book then all you need to do after that is 'translate' it into (VBA) code respecting the syntax and you can go a long way with that.

    Can you attach a file with the columns the way you decided with some dummy data in the cells.
    I'll see if I can help you on the way. A ready-made solution is great and I will try and provide one but trying it yourself will make you less dependent of others and you can modify it to your liking and changing requirements
    Believe me, once you've started you will think up more possibilities :)
     
  5. JNoji

    JNoji Thread Starter

    Joined:
    May 11, 2018
    Messages:
    5
    Ok I have attached a sample file, so I want the email to be generated if the "approval expiration" date in column F is approaching in the next 7 days or less. Also if it is approaching in the 7 days or less I want to change the "Approval Status" in column C to "expiring, survey sent".

    I appreciate the help, I just discovered VBA and can imagine all the possibilities once I am adequate at coding.

    Thanks again!
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Are you using Outlook?
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    And how do you want to trigger it, not automatically I guess
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Okay try this one, I placed a button to trigger it.
    It will only Display the mail message not send it you can change that in the mail code
    If you want to trigger Excel using the scheduler then invoke the macro SENDMAIL_Click, that way it is only triggered through the scheduler and not when you open the file to correct or check
    You will have to pass the subject line, maybe some message and the coworker address

    I'm off to bed here.
     

    Attached Files:

  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    And, have you tried it?
     
  10. JNoji

    JNoji Thread Starter

    Joined:
    May 11, 2018
    Messages:
    5
    I just tried it, looks like it runs perfect to what I need it to do. I just need to test it with the actual file. I wanted it to trigger when the workbook is opened which was why I wanted it to change the "Approval Status" cell to "Approved, email sent" so we can use that data as a trigger to not generate an email, and the same with "Expired, email sent"

    I appreciate the help, you have been amazing!
     
  11. JNoji

    JNoji Thread Starter

    Joined:
    May 11, 2018
    Messages:
    5
    I was going to use the task scheduler to open the workbook every monday to trigger the macro. I was also hoping to add an attachment to the email if possible.
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    You can include the attachment too, see the mail macro and add a parameter for the attachment, the filename passed to the macro must be the fullname, path and filename

    The Bold red lines are new and you will have to add these to the existing macro named MailData

    I see they do not show up as Bold Red in the code section
    I have highlighted here what you need to add to the vba code (or copy the code in the code section
    , Optional NewFileName1 As string

    if len(trim(NewFileName1))>0 then

    Code:
    Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String, Optional NewFileName1 As string)
    Dim eSubject As String, Sendto As String, CCto As String, EBody As String
    Dim app As Object, Itm As Variant
    Set app = CreateObject("Outlook.Application")
    Set Itm = app.CreateItem(0)
    
    With Itm
       .Subject = mSubject
       .To = Sendto
       If Not IsMissing(CCto) Then .CC = CCto
       .Body = mMessage
    if len(trim(NewFileName1))>0 then   .Attachments.Add (NewFileName1)    ' Must be complete path
       .Display ' This property is used when you want
    '    .send    ' This property sends the mail directly
    '    .Save   ' this property issued to save it as Draft for later processing,
    End With
    Set app = Nothing
    Set Itm = Nothing
    End Function
    
     
  13. 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/1210071

  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