Tech Support Guy banner
Status
Not open for further replies.

Excel Auto Email Code ?

2K views 16 replies 2 participants last post by  scotty718 
#1 ·
Hi I am trying to set up a Client data base and I would like to be able to email everyone in the company when a change has been made to columns B,D,E, and F. I plan on having different tabs for each company and when I update the companies folder and change one of those four columns, I’m looking for it to send out an email alert saying "CompanyX Contact Manager Client Folder Has Been Updated".

I have been trying my best to figure it out with info I found on here but I am not very good with Code and any help will be much appreciated
 

Attachments

#6 ·
Yes, I can see you are quite new! Code that is written for a specific purpose would need to be modified for another purpose. However, it's good to review code that is written for other purposes, because you can often alter or adjust it, if you understand how it works, or can grasp some it.

That said... For your purposes, replace the below code into the worksheet module of the workbook you sent and see if that doesn't do the trick.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row > 8 And Target.Column = 2 Or Target.Column = 4 Or Target.Column = 5 _
    Or Target.Column = 6 Then SendMail "cnicker6@gmail", "Client Log Has Been Updated"

End Sub

Public Sub SendMail(strWho As String, strSubject As String)

ThisWorkbook.SendMail strWho, strSubject

End Sub
 
#13 ·
I have Excel and Outlook 2010. I figured it out i ended up just making a single email address through outlook that contain everyone in the office that will be editing the list. the only thing i need to figure out now is how to make it send an email only after i hit save in excel.
 
#14 ·
Yes, I haven't used SendMail in a looooong time and now I realize you can only use 1 e-mail address. Points for finding the solution on your own! Just so you know, you can interact with Outlook in a more robust way - in a complete way, actually, by interacting with Outlook directly from VBA. More telling you so that you know its possible and more robust in the future. Pretty much anything you can do in Outlook manually, and more, can be implemented through VBA, even inside of Excel (or any other Office App).

Now, about send on Save. You could put the code in the Before Save event of the Workbook or in the Before Close event, but I think you have an easier time managing it by placing a button on the sheet that will send the mail anytime you click it. To do that, take the code out of the worksheet change event and place it in a standard module, then connect that module to the button. I'll leave it to you to figure that out if you don't know how.
 
#15 ·
Thanks I had no idea I could just add a button to the folder that will be so much easier. Do I have to write a code to add a button I'm confused, I have never done this before?
Also my main goal here is to be able to have a contact list with all our clients and if someone updates one of the clients folders it will send an email out to the company letting them know that the clients folder has been updated. Do you think that the way i am approaching it with excel is the best way?
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top