Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post

    Post Sending mail depend upon cell value.

    Hi Experts,
    We have tracker in place where we add data as per the projects we work on. Is it possible to send email to the person whose name is added in the Processing column when the date is added in the Data Received on Column.
    Also when I change the status column to Under SPOE then a mail should be send to the person whose named is added in the respective entry under SPOE column.
    The email template is simple.
    Processing
    Subject Project Name (From the column A) Assign for processing.
    Body The Project is assigned to you
    SPOE
    Subject Project Name (From Column A) assigned to you for SPOE check.
    Body The project is assigned to you for SPOE check.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Maudibe


    Jaggi,

    The following revised workbook will create an email to the Processor (col F) when the Date received is changed. If the SPOE is changed then an email is generated to the new SPOE. There is a veryhidden worksheet called "Settings" that has a list of the name in column A and their email address in column B.

    The number of project names is limited to 500 in the code and 50 employee accounts but this is easily adjustable. Let me know if adjutments are needed

    HTH,
    Maud

    Email generated when "Data Received on" is changed in column J
    jaggi5_5.png

    Email generated when "SPOE" is changed in column G
    Jaggi6.png
    Attached Files Attached Files
    Last edited by Maudibe; 2016-02-26 at 04:50.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud,
    Thanks for the code. I am not sure why I am not able to get the mails when I change the date or change the SPOE name.
    I think I bit unclear about my requirement the column Status(Column I) and Data Received date (Column J) will be empty initially. When we received the data we Update the Column J with the date and change the Status(Column I) to word Assign in it. Is it possible to trigger the mail when we add the Assign word in the Status(Column I) and when in the same Status(Column I) changes from Assign to Under SPOE word it should send the mail to the person whose name is available in SPOE(Column G). The names in Processing and SPOE will not change, we set the trigger on words Assign and Under SPOE in column Status(Column I).
    Please let me know if I am unclear anywhere.
    Regards,
    Jaggi

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Jaggi,

    If I understand you correctly, if "Assign" is entered into a cell in Column I then the Processor in column F gets the email. If "Under SPOE" is entered in column I, then the SPOE in column G gets the email instead.

    The attached spreadsheet reflects those changes. Let me know if you need anything additional.

    Maud
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud,

    I am still not able to generate the mail. What I am doing here is adding the word "Assign" in column I and clicking enter. Do I have to do anything else to generate the mail.

    Regards,
    Jaggi

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Jaggi,

    Are you using the sample I provided? The code will be inhibited if:
    1. ...the value changed is not in column I
    2. ...anything other than "Assign" or "Under SPOE" is entered in column I
    3. ...if "Assign" is entered but the Processor is blank.
    4. ...if "Under SPOE" is entered but the SPOE is blank
    5. ...if you do not have Outlook open, you will get a message warning

    Btw, the code is not case sensitive so "Assign" is the same as "assign"

    Please let me know if the sample workbook I provided is not working. If you have transferred the code to another work book, you need transfer code from 2 modules (worksheet and Standard) as well as exporting the class module then importing into your project. Also keep in mind that there is a very hidden sheet that must be transferred. The names of the sheets cannot change

  7. #7
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud,

    I am using the same sample file you have provided.
    - I am making the changes in column I.
    - I filled in all the pending cells in column I, but the mail is not generated.
    I even set the tool >> reference >> Microsoft Outlook 14.0 Object Library.

    Attached is the screenshot of your sample file.

    Let me know what I am doing wrong here.

    Regards,
    Jaggi
    Attached Images Attached Images

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Strange that this works fine for me (Done in Excel 2013). Good thought but the reference to the Outlook library is not needed. Can you open the VB editor (Alt-F11), double click sheet1 in the VBA Project window and in the gray vertical bar just to the left of the code, click a breakpoint on each of the lines EmailProcessors Target, "xxxxxx" (see image). Go back to the worksheet and place a valid entry in column I. If the event is being triggered, it will pause at one of the 2 breakpoints. If not, click the stop/reset button to the right of play and pause then try again.

    Jaggi8.png

    If the code initiated, click anywhere on the yellow highlighted line and tap F8 to plow through each line of code one line at a time. Let me know if you make it to the End Sub in the above code and if the email is generated. If not, let me know at which line you stopped executing

    Make sure you have macros enabled in both Excel AND Outlook under File > Options > Trust Center > Trust Center Settings > Macro Settings > Tick Enable all macros >Tick Trust access to the VBA project object model

    Maud
    Last edited by Maudibe; 2016-02-27 at 10:26.

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    Jaggi (2016-03-29)

  10. #9
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud,

    I think it is my bad luck. I changed the setting as well in book excel and outlook. Tested all the means you have mentioned above, but unfortunately my cursor on the VBA - sheet1 is not moving from first line itself.

    Regards,
    Jaggi

  11. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Jaggi,

    In the sheet module, did you set the breakpoints then initiate the code by an entry in column I? Did the code break at one of the breakpoints with a yellow highlighted line?

  12. #11
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud,

    I did the same, first break the code in sheet1 on both the lines EmailProcessors Target, "ASSIGN" and EmailProcessors Target , "SPOE". and entre word "Assign" in column IThe cursor is not moving at all. It is still at first line. I didn't get any error or yellow line.



    Regards,
    JD

  13. #12
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud,

    I find a below code from online and it is working in the sample excel workbook. I was trying to see if I am doing anything wrong here.
    It worked.
    Code:
    Private Sub Workbook_Open()
     
    Dim OutApp As Object
    Dim OutMail As Object
    Dim EmailSubject As String
    Dim EmailSendTo As String
    Dim MailBody As String
    Dim c As Long
    Dim r As Long
     
        Sheets("Sheet1").Activate
     
    For i = 1 To 100 '?
        If Sheets("Sheet1").Range("I" & i).Value = "Assign" Then
     
    'Subject string
        EmailSubject = Sheets("Sheet1").Range("A" & i).Value
        EmailSendTo = Sheets("Settings").Range("B" & i).Value
        MailBody = "Dear " & Range("A" & i).Value _
        & vbNewLine & "Ref: Report Dated " & Range("D" & i).Value
     
     
    'Send Mail
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(o)
            With OutMail
                .Subject = EmailSubject
                .To = EmailSendTo
                '.bcc
                .Body = MailBody
                .Display
                '.send
            End With
     
            Set OutMail = Nothing
            Set OutApp = Nothing
            'MailBody = ""
     
        End If
    Next i
    End Sub
    Regards,
    Jaggi

  14. #13
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud,
    A sign of Relief for me on this macro today. I don’t know what I was doing wrong and today finally the tool worked. Thank you for all your support. I will be testing the tool and will get back to you with the latest update shortly.
    Regards,
    JD

  15. #14
    2 Star Lounger
    Join Date
    Feb 2015
    Posts
    128
    Thanks
    19
    Thanked 1 Time in 1 Post
    Hi Maud,

    Sorry for the late reply.

    The tool works perfectly fine and big thanks to you for your support and guidance on this thread.

    Regards,
    JD

Tags for this Thread

Posting Permissions

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