+ Reply to Thread
Results 1 to 6 of 6

Macro to send Email notification to specific people

  1. #1
    Registered User
    Join Date
    03-22-2013
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Question Macro to send Email notification to specific people

    I have written a Macro that transfers data from and input sheet into a data collection table. On the click of the button, the data is transferred to a new row, the input sheet it cleared, the workbook is saved and I receive an email notification to say that new information has been added to the sheet.

    I would like to know how to get the email notification to go to different people depending on the contents of a cell.

    For example,

    I have a merged cell (B9:C10) that details from which department action is required (Operations, Engineering or Quality).

    How do I adapt my code so if the cell = “Operations” it sends the email to PERSON A

    If the cell = “Engineering” it send the email to PERSON B etc etc.

    My current email code is as follows:

    Please Login or Register  to view this content.

    Please help.
    Thanks

  2. #2
    Registered User
    Join Date
    06-11-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    86

    Re: Macro to send Email notification to specific people

    Hi JDGreen,

    If merged cell (B9:C10) is the criteria to send the mail then I suggest you to create a range having the list of mail ids of all the peoples in the respective departments in separeted by semi colan in a single cell for each department.


    Add the loop or case statement to pick the cell having the mail Ids of specific department and give the address of that cell in place of "EMAIL ADDRESS"


    Note that each mail ids should be separated by Semi-colan

  3. #3
    Registered User
    Join Date
    03-22-2013
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Macro to send Email notification to specific people

    Hi Md Aejaz,

    Just so I can make sure I am understanding you correctly, I should end up with 3 seperate cells then with the email information eg: (AA1) = [email protected]; [email protected] (AA2) = [email protected]; [email protected] etc.......? and I understand that I then replace the contents of the "EMAIL ADDRESS" with the location of the corresponding cell.

    How do I link the criteria in (B9:C10) to choose the correct cell? For instance, if (B9:C10) = "Engineering" how do I get it to choose cell AA1? Do i need some kind of IF statement like

    Please Login or Register  to view this content.
    ???

    The criteria does not need to go into a merged cell if that makes it any easier. I can easily make that simply B9 rather then (B9:C10).

    Thanks for your help.

  4. #4
    Registered User
    Join Date
    06-11-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    86

    Re: Macro to send Email notification to specific people

    Hi,

    Replace the line (.To = "EMAIL ADDRESS"
    )with this below code and make the necessary changes

    If Range("B9") = "Engineering" Then
    .To = Range("AA1").Value
    ElseIf Range("B9") = "Operations" Then
    .To = Range("AA3").Value
    ElseIf Range("B9") = "Quality" Then
    .To = Range("AA2").Value
    End If

  5. #5
    Registered User
    Join Date
    03-22-2013
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Macro to send Email notification to specific people

    Works a treat! Thanks!

  6. #6
    Registered User
    Join Date
    03-22-2013
    Location
    Wolverhampton, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Macro to send Email notification to specific people

    Everything was working great until I changed the location of the file to a shared drive so different people at work can access it. It was on my desktop before.

    As a result, the hyperlink created by the "ActiveWorkbook.FullName" code is something like this U:\Example\Folder A\Quality\LAIR Turnbacks\LAIR_Turnbacks_Stafford_Road.xlsm

    The hyperlink is not working correctly because of the spaces in the folder names. Is it possible to get the hyperlink working with these spaces?

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA code to send email to the people whose email address is in the Access table
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-30-2014, 05:11 AM
  2. Macro to send notification from excel to my Outlook Email
    By savethisid in forum Excel General
    Replies: 18
    Last Post: 03-27-2014, 02:25 PM
  3. Excel Macro to send notification email when items pass a certain date
    By Krist87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-08-2014, 09:08 AM
  4. How to send email notification from excel
    By amitwaje in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-19-2012, 06:49 AM
  5. Send Email Notification Upon Edit
    By novamustangs in forum Excel General
    Replies: 1
    Last Post: 07-18-2007, 09:51 AM

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