+ Reply to Thread
Results 1 to 19 of 19

Macro to send notification from excel to my Outlook Email

  1. #1
    Registered User
    Join Date
    07-11-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Exclamation Macro to send notification from excel to my Outlook Email

    Hi,

    I have an Excel file which is shared among us 100 users, the file gets updated everynow and then, and its actually becoming a pain to go and see if the file has got updated and is there any status required from my side. What i need is as and when the file gets updated it sends a notification to my outlook email. It would save me time and would also help me avoid going to check the file for any update. Kindly help me with this a macro or any kind of notification would help and ease my problem, please note i have 2007 and 2003 excel.

    Chao!!

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Macro to send notification from excel to my Outlook Email

    Hello
    maybe this thread can help you..


    http://www.excelforum.com/excel-prog...ification.html
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    07-11-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Macro to send notification from excel to my Outlook Email

    Hi Vlady, No it din't help..guess the link you suggested had everything to do with daterange and 3 months validity. I dont require this but a simple way to get notified when a file has been updated.

  4. #4
    Registered User
    Join Date
    07-11-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Macro to send notification from excel to my Outlook Email

    Would really appreciate if somebody could help me out with the problem.

  5. #5
    Registered User
    Join Date
    07-11-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Macro to send notification from excel to my Outlook Email

    i actually have these two macros which run when a button is clicked. i need that after the button is clicked and the macro runs, it should also then send an email notification (outlook) pasting all three macros but dont know how all three macro can run with single button. please help.

    First Macro

    Sub btn_Submit_Click()

    Dim wsReq As Worksheet 'Request worksheet (Request Form)
    Dim wsSch As Worksheet 'Schedule worksheet (Admin)
    Dim rngFindName As Range
    Dim rIndex As Long
    Dim strName As String

    Set wsReq = ActiveSheet
    Set wsSch = Sheets("Admin")

    With wsReq
    If Trim(.Range("C7").Value) = vbNullString Then
    .Range("C7:D8").Select
    MsgBox "No employee code provided.", , "Shift Request Error"
    Exit Sub
    End If
    wsSch.Unprotect "international"

    Set rngFindName = wsSch.Columns("C").Find(.Range("C7").Value, , , xlWhole)
    If Not rngFindName Is Nothing Then
    rIndex = rngFindName.Row
    strName = Trim(.Range("I6").Value)
    If strName = vbNullString Then strName = "(-)"
    wsSch.Cells(rIndex, Columns.Count).End(xlToLeft).Offset(, 1).Resize(, 6).Value = _
    Array(.Range("C10").Text, .Range("F10").Text, .Range("C16").Value, .Range("C19").Value, strName, .Range("C23").Value)
    .Range("C4:E5,C7:D8,C10:D11,C13:D14,C16:D17,C19:D20,C23:L26,F10:G11,I6:K7,J10:K11,J14:K15").ClearContents
    .Range("C4:E5").Select
    Else
    .Range("C7:D8").Select
    MsgBox "Employee Code [" & .Range("C7").Value & "] not found.", , "Shift Request Error"
    End If
    wsSch.Protect "international"

    End With
    End Sub

    Second Macro

    Sub Macro1()

    Second Macro

    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
    Range( _
    "C4:E5,C7:D8,C10:D11,C13:D14,C16:D17,C19:D20,C23:L26,F10:G11,I6:K7,J10:K11,J14:K15" _
    ).Select
    Range("J14").Activate
    Selection.ClearContents
    Range("C4:E5").Select

    Third Macro


    Sub Mail_Workbook_1()
    ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
    ' This example sends the last saved version of the Activeworkbook object .
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    ' Change the mail address and subject in the macro before you run it.
    With OutMail
    .To = "[email protected]"
    .CC = "[email protected]"
    .BCC = ""
    .Subject = "Update Shift Ex-Change"
    .Body = "New Update on Shift Ex-Change file!"
    .Attachments.Add ActiveWorkbook.ShiftExChange.xls
    ' You can add other files by uncommenting the following line.
    '.Attachments.Add ("C:\test.txt")
    ' In place of the following statement, you can use ".Display" to
    ' display the mail.
    .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub


    please help...would really apprciate

  6. #6
    Registered User
    Join Date
    05-28-2012
    Location
    Derby, England
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Macro to send notification from excel to my Outlook Email

    I don't think you can. My sugestion would be to write a msg box macro that would tell the user to email you the updated file.

  7. #7
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Macro to send notification from excel to my Outlook Email

    hi savethisid,
    not sure if this what you look after but try this file according yr code!
    and yes, next time please use code tags around code!
    Attached Files Attached Files
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  8. #8
    Registered User
    Join Date
    07-11-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Macro to send notification from excel to my Outlook Email

    hey jhon it isnt working, i guess you've protected the same.

  9. #9
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Macro to send notification from excel to my Outlook Email

    hi,
    I am sorry... you have in yr code
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-11-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Macro to send notification from excel to my Outlook Email

    Hi John, thanks for the same, however if you please dont mind i would like a small favor .

    I need that the macro should not ask for confirmation of sending the email. The email should be sent automatically. In other words the user should not not even know that an email has been sent to the the concerned person who's name would feature in the VBA. Would that be possible? And finally the name that features in C4:E5 should feature in body just below the subject line, something like "New update of Shift change from:"John" is waiting for approval..is it possible. kindly advice and help.

  11. #11
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Macro to send notification from excel to my Outlook Email

    hi,
    for sending change
    Please Login or Register  to view this content.
    and check the attachement
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    07-11-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Macro to send notification from excel to my Outlook Email

    Hi John,

    There seems to be some problem, it does everything except sending the notification.

  13. #13
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Macro to send notification from excel to my Outlook Email

    Hi, could you be more specific?
    the code sends the email if you replace .display with .send
    the code from Module 1...it's yours!

  14. #14
    Registered User
    Join Date
    07-11-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Macro to send notification from excel to my Outlook Email

    Hi John,

    sorry wasn't around, well yes i would be more specific.

    Step 1. User would feed in there requests (shift change / ex-change).
    Step 2. user would thereafter click on submit button. Once the same is being clicked the excel saves the file and sends a notification without asking for any confirmation from the user i.e. (would not ask yes or no). in other words the excel would automatically send a notification to the email provided in the VB code.

    I hope i was able to explain. kindly suggest.

  15. #15
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Macro to send notification from excel to my Outlook Email

    the excel sends the file without any confirmation from the user when the he/she hits yr submit button located in Sheet Admin.

  16. #16
    Registered User
    Join Date
    07-11-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Macro to send notification from excel to my Outlook Email

    Hi John,

    sorry to trouble you yet again...something isnt working fine.. this time around i am attaching the whole file for you. Would really appreciate if you could locate the problem.

    step 1: user enters his/her shift change request on the request form.
    step 2: user then checks if the request is accepted on admin sheet (password protected) only for viewing purpose for users.
    step 3: user then saves the file (as soon as the file is saved the excel should send a notification to the concerned email recipent mentioned in the VBA.
    step 4: user then closes the file.
    step 5: at my end a notification would pop up in my outlook (only then i would go to this file) to check what was requested.

    kindly note this is a shared file used by 100 users.

    would really appreciate your inputs.
    Attached Files Attached Files

  17. #17
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Macro to send notification from excel to my Outlook Email

    hi, not sure if it's what you want but try
    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    03-27-2014
    Location
    NYC
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Macro to send notification from excel to my Outlook Email

    Hello john,

    I was reading to your posting and I need your help in vba script.
    I am a beginerer using vba script, i am a student at NYU and working on a project.

    I have developed a template which is link to various data sources and updated when you open and the links are update.
    I have put various conditional formating in the cells. Now I want an email notification when ever the condition is true and the cell is highlighted.

    1. Can i send email notification whenever the cell is highlighted when the condition is true.
    2. I have provide dropdown selection for different users, so can the email to be directed/send to the user email for which the dropdown selection is made.
    3. I try to put an manual send email button but didnt work, so provide me how to send to different user email as per selection.

    If you provide step by step with the vba script, it will be highly appreciated.

    Thank you for your time.

    Regards
    Ravi

  19. #19
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,028

    Re: Macro to send notification from excel to my Outlook Email

    Hello Ravi,
    Welcome to forum!

    According to the forum rules you need to open your own thread and perhaps is possible to be helped.

    Cheers
    j

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1