Excel 2010 Macros that sends Email when usernames change

Microsoft Excel 2010 - complete product...
October 15, 2013 at 13:37:16
Specs: Windows XP
Hi guys,

I really need your help.
I'm using a document that is being edited by multiple users. I have 20 users who are being reassigned on that document. I have created a macros that will send an email to an individual user based on whether or not conditional logic holds true for their name being in a specific cell.

The problem I'm having is how to track changes. In other words, let's say I have users A, B, and C. In cell E5, I currently have user C slotted. However, I change E5 to user B. I need an email to send to both users B and C saying there was a change. It doesn't need to be specific, but as it stands, my macros can only send an email to the individual users if their name is in cell E5.

I need that email to generate for both users who were affected by a change. Any ideas? I can't use Track Changes because that disables macros.

Please keep in mind that my VB knowledge is limited and self-taught, so I will appreciate any and all help. Thanks!


See More: Excel 2010 Macros that sends Email when usernames change

Report •

#1
October 15, 2013 at 17:32:10
Since you didn't post your macro, we don't know how you are doing what you are currently doing.

That said, the only way for a macro to know what the "old" value in E5 was before the change is if you store it someplace and then let the macro read it back. One possibly method is to use a SelectionChange macro to store the current value in E5 in another cell whenever E5 is selected. Your code could then read the value in that other cell and send an email to both parties if the value in E5 doesn't match the value in the other cell, meaning it must have changed.

If you plan to post any code, please click on the following line and read the instructions found via that link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 15, 2013 at 19:27:21
I've posted the macros below. Full credit to all of the help I found online - I don't pretend to be an expert.

The data validation right now is:

User 1 User 1's Email yes/no
User 2 User 2's Email yes/no
User 3 User 3's Email yes/no

Basically, if a particular user is selected from a drop-down in E10 (hypothetically), the third column will change to 'yes', which will trigger the macros to send an email to that user's email.

That's very helpful but is only useful if a new user is added. I need a macros that will also do the same thing if there is a change between users.

Say I replace E10 with User 2 instead of User 1. Now I need the macros to send both Users 1 and 2 an email saying that there was a change. How would I do this?

Sub Test1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
  
    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "C").Value) = "yes" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Text"
                .Body = "Hi " & Cells(cell.Row, "A").Value _
                      & vbNewLine & vbNewLine & _
                        "Test " & Cells(cell.Row, "E").Value _
                        & vbNewLine & vbNewLine & _
                        "Test." & _
                        vbNewLine & vbNewLine & _
                        vbNewLine & vbNewLine & _
                        "-Thanks-"

                .Send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub


Report •

#3
October 16, 2013 at 11:49:21
Did you look into the Worksheet_SelectionChange method I suggested?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 16, 2013 at 13:01:29
I think that might work, but it's a little beyond my technical know-how.
What macros would I use for that? I don't have enough knowledge yet to write one, but I can try modifying it.

Any guidance you could offer would be great.


Report •

#5
October 16, 2013 at 21:31:09
I'm not quite sure how to reconcile this statement...

"My macros can only send an email to the individual users if their name is in cell E5."

...with your code that says:

For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)

It looks like you are looping through all the cells in Column B looking for email addresses, yet you say you are only sending an email to the person whose name is in E5. That's confusing to me.

In any case, the following code will copy the value of the selected cell in column B and place it in A1. Well, it doesn't really "copy" the value, it just sets A1 equal to the value in the selected cell.

You could then write code to test if A1 equals the selected cell and if it doesn't, it must mean that the selected cell has changed. In other words, if Bx contains me@gmail.com, as soon as you selected Bx, A1 will display me@gmail.com. If you then change Bx to you@gmail.com A1 will no longer be equal to Bx.

In your code you could check If A1<>Bx and if it's TRUE then send an email to the "old" email address in A1 as well as the "new" address in Column B.

Since I'm not sure what you are doing with the loop nor how your macro gets triggered, I can't put together the total package. The best I can offer at this point is the code that will set A1 = Bx when Bx is Selected:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If a single cell in Column B is selected, 
'set A1 equal to the selected call
  If Target.Column = 2 Then
   If Target.Cells.Count = 1 Then
     Cells(1, "A") = Target
   End If
  End If
End Sub

This link might also help with writing your code. Since I don't know your level of expertise, these techniques might help out. I use them all the time.

http://www.computing.net/howtos/sho...

Perhaps if you provided a little more information about how E5 and your Column B loop are related, I could be more specific.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 17, 2013 at 06:58:38
Thank you for your continued help.

So, what I'm trying to do...

Column A has a bunch of names
Column B has the associated email addresses
Column C is a logic test

My current macros works such that if a name from Column A is selected from a drop-down in Column E (such as E5, E6, etc.), column C will change to 'yes'. That 'yes' will trigger an email to send to the associated email address listed in Column B for that user.

What I need is a bit more complicated. Let's say I open the workbook and I change from one user to another in a specific cell. I could then run my macros and send an email to both of the affected users. That email would have a generic statement. The problem I'm having to figure out is how would the macros know to send an email to the users who were affected by that change in selection.

My knowledge is very limited, so any code you could provide would be appreciated.


Report •

#7
October 17, 2013 at 09:47:12
First, just to make sure we are using the correct terms...

Macros - You have been using the word "macros" - plural - yet you only posted one macro. I assume that you are only discussing the single macro that you posted.

Trigger - You said "...column C will change to 'yes'. That 'yes' will trigger an email to send to the associated email address listed in Column B for that user."

As far as I can tell, the "yes" is not actually triggering anything. It appears to me that you are manually running the code, which looks for the "yes" and then sends the email for each "yes" that is found. When I think of a "trigger", I think of something like the Worksheet_SelectionChange macro that I posted. The selection of the cell actually "triggers" the code to run, i.e. there is no need to manually run the code.

Data Validation - You said "The Data Validation right now is:

User 1 User 1's Email yes/no
User 2 User 2's Email yes/no
User 3 User 3's Email yes/no"

When I think of Data Validation within Excel, I think of the Data Ribbon...Data Validation feature where a Data Validation drop down list can be created, or a cell can be set to a specific fill color based a formula, etc.

How are you using Data Validation to put a Yes in Column C next to the corresponding email address in Column B based on the Drop Down value chosen in Column E?

Finally, I'm confused by "...a name from Column A is selected from a drop-down in Column E (such as E5, E6, etc.), " How does E5, E6, etc. enter into this? i.e. it sounds like you have multiple Drop Downs. If so, why? How many names are you choosing before you run the macro? How many emails are being sent each time the macro is run?

I'm having trouble visualizing the process you are using to choose a name(s?) and have the email(s?) sent.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
October 17, 2013 at 11:36:27
You're correct - my terminology is off.

It's just one macro, and I manually run the macro. The 'data validation' is actually conditional formatting that I have used with IF statements.

I think you understand what I'm trying to accomplish - I'm just not sure how to do it.

Basically, when I manually run the macro, it sends an email to all the users who were affected by a change in names from a dropdown list. That's what I'm trying to create.


Report •

#9
October 17, 2013 at 12:41:30
re: it sends an email to all the users who were affected by a change in names from a dropdown list.

That was the part that was not clear. It appeared from your earlier posts that you were making one change, and you wanted to go from 1 email (new name) to 2 emails (new and old). You have now made it clear that you want to send emails to multiple new names and multiple old names. That's very different.

re: I think you understand what I'm trying to accomplish

Please keep in mind that I can not see your workbook from where I am sitting, nor can I read minds. I can only go by what you post in this forum, so if it is not clear to me what you are doing, all I can do is keep asking questions until I understand it all.

So, let me tell you what I do understand so far, and what is still not clear to me. Without a complete understanding of how the workbook is set up and how you are using it, I can't possibly offer a solution.

1 - You have a list of names in Column A
2 - You have a list of corresponding email addresses in Column B
3 - You have IF functions in Column C
4 - You have multiple Drop Downs in Column E, populated with the names from Column A
5 - When you select a name from a Column E Drop Down, the IF functions in Column C place "Yes" next to the email address for the person selected in the Drop Down.
6 - After you have selected all the names that you want to, you run the macro and the emails get sent to any email address with a Yes next to it.

Please let me know if those steps are correct.

Here are a couple of assumptions that I am making based on the steps above.

After you run the macro, I assume the Yes's are still in the cells next to the email addresses to which the emails were just sent. I further assume that each time you change a name, an old Yes goes away, because the name is no longer is selected in a Drop Down and the IF function "removed" the Yes. At the same time, the IF places a Yes next to the new name that was chosen.

Please let me know if those assumptions are correct.

What is not clear to me is how the new chosen name relates to an old name or whether that even matters. For example, let's keep it simple and say that you have 2 names chosen and 2 emails have been sent. Let's say you chose Bob and Sue, therefore Bob and Sue have Yes next to their email addresses.

You now want to change Bob to Fred and change Sue to Ann. Does is matter that Bob was changed to Fred and Sue was changed to Ann or do you simply want emails - the same email - sent to all four individuals? In other words, are the changes "paired up" new-to-old or are they nothing more than a bunch of unrelated names and all four of them should get an email?

I'm also not clear on whether or not the Drop Downs are lined up next to each name or if they are basically unrelated.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
October 17, 2013 at 18:48:08
1. Correct
2. Correct
3. Correct
4. So in totality, there are actually 14 columns E-R (one for each day of the week AM and PM) that have the drop-down names to select from.
5. Correct
6. Partially. Your example is exactly what I'm trying to do:

"You now want to change Bob to Fred and change Sue to Ann. Does is matter that Bob was changed to Fred and Sue was changed to Ann or do you simply want emails - the same email - sent to all four individuals? In other words, are the changes "paired up" new-to-old or are they nothing more than a bunch of unrelated names and all four of them should get an email?"

They are unrelated names. All four would get an email saying there was a change. The only specific that would change is the users would have a line in the email saying which column they were changed. So, column E would be Monday AM. Column F would be Monday PM, and etc. Let's say Bob was originally in Monday AM and Sue was in Monday PM. I then changed Bob to Fred, and Sue to Ann - like in your example.

The macro would send an email to both Bob and Fred saying there was a change on Monday AM, and an email to Sue and Ann saying there was a change Monday PM. The column names would not change, and the list of users from the drop-down would be a standard list of people.

The dropdowns are identical to the names in column A.

The IF function I have now checks to see if a user is in column E (I've kept it simple at this point as I wasn't sure how to get it to cover all of the potential other columns), and changes to 'yes' if the name is there. However, it doesn't reflect replacing one name with another, and that means it doesn't really do what I want it to.

message edited by cothm88


Report •

#11
October 18, 2013 at 07:48:57
First, a posting tip...

I know that we sometimes want to post as simple a question as possible, but that often leads us down the wrong path.

Your first post talks about a single change to E5 and a request to have emails sent to 2 users. That's a fairly simple process and I offered a suggestion on how to that.

We now know that you actually have 14 Drop Downs, you are making multiple changes to the Drop Downs, you want different emails sent to each "pair" of changed users, etc. This additional information changes the requirements Considerably, with a capital C. Instead of storing a single "old name" in a single cell, we need to find a way to save 14 "old names" in 14 cells and then have the macro determine which "old name" matches up with a "new name" so that it sends the correct email to the correct pairs. That is a considerably more difficult task.

In addition, based on the code you have posted, I don't see how you are accomplishing this task: "The only specific that would change is the users would have a line in the email saying which column they were changed." What were your plans for having each pair of emails contain the line specific to the column?

Lastly, so far you have only dealt with changes to Column E when in reality you need a "Yes" (or some type of flag) for changes to all 14 columns while also relating each pair of flags to the proper column.

My point here is that "keeping it simple" makes it difficult to offer a solution that will work for a rather complicated set of requirements.

At this point I am reluctant to spend time working on code that is not going meet all of your requirements. I think the first hurdle is for you to determine how you are going to deal with the multiple Drop Downs and setting the flag for each user. An IF function in Column C is fine for 1 Drop Down or maybe even a few, but I don't think you want to use a Nested IF with 14 possibilities.

Perhaps if I had a copy of your workbook I could try a few things. Can you delete any confidential material (such as actual names and email addresses) and post the workbook on DropBox or some other site where I can access it?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
October 30, 2013 at 09:57:02
Hi,

I'm so sorry for the delay - I've been rethinking the idea, and I've come up with a simpler way of doing it, but I still need your help.

Are you still willing to assist me?


Report •

#13
October 30, 2013 at 10:46:43
Feel free to post your latest thoughts on this matter.

If they are vastly different than what we have been discussing, I may ask you to start a new thread, but let's see what you have for now.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
October 30, 2013 at 11:03:35
So, here's a simplified idea.

At the end of the day, I would like each user to receive a single email saying where they are scheduled for the next day in the AM and PM.

For example, see below. Column B is a list of locations. Column C is the Monday AM and Column D is the Monday PM. Column E is Tuesday AM and Column F is Tuesday PM and etc.

Users are scheduled once in each Column.

So, let's say today is Sunday. I want to notify everyone where they are scheduled Monday AM and PM. So, from my example below, User 1 is at Location A on Monday AM and Location B on Monday PM. Users 2, 3, and 4 would get respective emails as well.

Then, on Monday, I would send a similar email to everyone for Tuesday AM and PM.

Column B Column C Column D

Location A User 1 User 2
Location B User 2 User 1
Location C User 3 User 3
Location D User 4 User 4


Does this make sense?


Report •

#15
October 30, 2013 at 11:49:11
Are you asking that each day a single email goes to each user with both locations included?

e.g.

User 1 gets 1 email that says AM Location A PM Location B

User 2 gets 1 email that says AM Location B PM Location A

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#16
October 30, 2013 at 11:52:23
That's correct.
One email for each user for the next calendar day.

It doesn't have to be automatic. I am fine with running the macro manually.


Report •

#17
October 30, 2013 at 12:26:30
It appears to me that all you need now is some code that will build the Body of the email. Your existing code appears to be already set up to create an email for each user, you just need to create the Body text showing (generically) AM Location, PM Location for each User.

Assuming you start with this data, the following code is an example of how to create that Body text.

       A           B         C
1  Location A    User 1    User 2
2  Location B    User 2    User 1
3  Location C    User 3    User 3
4  Location D    User 4    User 4

In this example, I use a MsgBox to display the Body text. In your actual macro, you would use similar code to create the Body text inside the loop that reads each email address and use the BodyText created as the .Body variable of the email.

Sub BuildBodyText()

'Determine Length Of Data
 lastRow = Range("A" & Rows.Count).End(xlUp).Row

'Loop through Column B
   For User_AM = 1 To lastRow

'Find matching User in Column C
     With Range("C1:C" & lastRow)
      Set User_PM = .Find(Range("B" & User_AM), lookat:=xlWhole)
     End With

'Build Body Text for Email
        BodyText = "AM - " & Range("A" & User_AM) & vbCrLf & _
                   "PM - " & Range("A" & User_PM.Row)
             MsgBox BodyText
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#18
October 30, 2013 at 12:51:31
Can you please show me how to integrate that into the macro I use currently?
Basically, I have it set right now so that Users are A1:A4, email addresses in B1:B4, and yes/no in C1:C4. I can certainly move those to another part of the sheet if that's easier.

Please see the existing macro below.


Sub Test1()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
  
    On Error GoTo cleanup
    For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "C").Value) = "yes" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Schedule Change"
                .Body = "Hi " & Cells(cell.Row, "A").Value & "," _
                      & vbNewLine & vbNewLine & _
                        "There has been a change in your support assignment " _
                        & vbNewLine & vbNewLine & _
                        "Please reference the calendar" & _
                        vbNewLine & vbNewLine & _
                        vbNewLine & vbNewLine & _
                        "Thank you!"

                .Send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub


Report •

#19
October 31, 2013 at 10:15:36
Here we go again...

In Response #14 you said that you had:

Column B      Column C       Column D

Location A      User 1            User 2
Location B      User 2            User 1
Location C      User 3            User 3
Location D      User 4            User 4

Now in Response # 18 you say you have:

"Users are A1:A4, email addresses in B1:B4, and yes/no in C1:C4"

Those are 2 very different layouts and require 2 very different pieces of code.

In addition, I don't see anything about User assignments based on your locations or User assignments based on AM/PM.

Once again, I can't help you if you keep changing the description of your data layout.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#20
October 31, 2013 at 11:54:18
To clarify,

I am using the first few to store the user data.

Cells A1:A4 have names.
B1:B4 have emails.
C1:C4 are the yes/no for emails

The rest of column A is blank. The rest of column B has locations (starting with row 6 for each column) The rest of column C has Monday AM (users listed in each cell). The rest of column D has Monday PM (users listed in each cell). The same is true for columns E:P.

If you like, I can send you the exact spreadsheet to take a look.


Report •

#21
October 31, 2013 at 13:47:45
I don't mean to sound harsh, but ever since the beginning of this thread, you have posted inaccurate or unclear descriptions of what your spreadsheet looks like. Each time, I have gone off and done some work based on your description, only to find that what you described isn't even close to what you actually have.

Let's look at what you posted this time:

"The rest of column C has Monday AM (users listed in each cell). The rest of column D has Monday PM (users listed in each cell). The same is true for columns E:P."

What is the same for Columns E:P? Monday AM and Monday PM or something different? Don't make us guess...when we do, we get it wrong and end up working with the wrong data. You know, as I look back over this thread I don't see any other mention of anything beyond Column E. Now you are talking about data all the way out to Column P. Maybe I missed it, feel free to point it out if I did.

Overall, my point is that unless you can provide a clear, concise and consistent description of the data you are working with, it is really, really frustrating to try and help you.

If you want to share your workbook, remove any confidential or personal data and upload it to DropBox or a similiar site and then post a link back here.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#22
November 9, 2013 at 11:25:09
Hi,

I'm happy to share the workbook with you - I agree - it will be a lot clearer.

Other than Dropbox, is there another way to share it? Does this forum have a messaging system?


Report •

#23
November 9, 2013 at 15:30:02
This forum has a messaging system but it's just for text based messages, not attachments.

What's wrong with using DropBox?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#24
November 10, 2013 at 06:42:36
HI,

I have uploaded a sample workbook here:

https://www.dropbox.com/s/0pfrjjxyp...

Just to clarify, I need a macro that will send an email for the next day AM & PM (if today is Monday, it'll send out Tuesday's locations; if today is Thursday, it'll send out Friday's locations, etc), to each user listed at the specific email.

Each email will have their location for AM & PM. If they are not listed for both, it will only have the one (either AM or PM), and if they are not listed for either AM or PM, it will not generate an email.

So, in the example workbook, on Sunday, Bob L would get an email saying he is at Ohio on Monday AM and Monday PM.

Sam G would get an email that she is at Virginia Monday AM and New Mexico Monday PM.

The 'Leader' and 'Code' columns are not applicable to the email. There will not be any emails on weekends.

The macro I have now doesn't do this, but it's what I've been able to create with my limited knowledge and what I've read online. Please feel free to edit it as needed.


I apologize for the inconsistencies in the past, and thank you for your help.


Report •

#25
November 20, 2013 at 13:29:08
Hi,

Have you had a chance to look at the workbook?

Thanks


Report •

#26
November 21, 2013 at 05:35:12
No, I haven't. I'll see what I can do over the next couple of days.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#27
November 21, 2013 at 06:32:49
Thank you. Please take your time.

Report •

Ask Question