Cell value change macro

wildignite

New Member
Joined
May 5, 2009
Messages
6
Is there a way to send an email to a particular recipient if the value in a cell equals their name. For example, Four Names: JOSH, TIM, ALAN, RON. Alright, when an owner, let's say JOSH(in column A row 3) is changed to TIM, I would like to have a macro that automatically sends and email to TIM to tell him a certain task is ready for him. If the names are in the "A" column then the subject would be in the "C" column. I hope I have explained myself properly. I am a novice in writing VB. Please help. Thank You.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Hello and welcome to MrExcel.

Based on http://www.rondebruin.nl/mail/change.htm (please read this for where the code goes):

Sheet code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Call Smail(Target.Value, Target.Offset(, 2).Value)
End Sub
Module code

Code:
Sub Smail(recip As Variant, subj As Variant)
    Dim iMsg As Object
    Dim iConf As Object
    '    Dim Flds As Variant
    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")
    '    iConf.Load -1    ' CDO Source Defaults
    '    Set Flds = iConf.Fields
    '    With Flds
    '        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Fill in your SMTP server here"
    '        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    '        .Update
    '    End With
    With iMsg
        Set .Configuration = iConf
        .To = recip
        .CC = ""
        .BCC = ""
        .From = """Ron"" <ron@something.nl>"
        .Subject = subj
        .TextBody = "xxxx" 'put message here in quotes
        .Send
    End With
    Set iMsg = Nothing
    Set iConf = Nothing
End Sub
 

wildignite

New Member
Joined
May 5, 2009
Messages
6
I am getting an error on .Send

how do i find my SMTP Server? I know your probably rolling your eyes right about now. I told you I was a novice. Could the address be something like mail.yahoo.com? Where yahoo is my company name.

Is the error linked to me not inputting the needed info for the server?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Which e-mail client do you use? Outlook, Outlook Express, Other...
 

wildignite

New Member
Joined
May 5, 2009
Messages
6
Outlook. '03

If it helps, I am also seeing the error saying the sendusing value is invalid.
 

wildignite

New Member
Joined
May 5, 2009
Messages
6
How would I write this?
Code:
Sub Mail_with_outlook()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String

 

    Set OutApp = CreateObject("Outlook.Application")

    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

 
If Cells = "JOSH" Then
    strto = "jmilton@worxofwood.com"
    strcc = ""
    strbcc = ""
    strsub = "Important message"
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "Cell A1 is changed"
    End If
    
 

    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Send
    End With
Where I have the if/then statement. Can I say if the cell changes to "JOSH" send to this email? And if the cell changes to "RON" send to this email address? And so on. Is this possible?

 

wildignite

New Member
Joined
May 5, 2009
Messages
6
This code worked for me, of course, before I threw up in it by adding that if then statement. I just wanted to show what I was thinking.
 

wildignite

New Member
Joined
May 5, 2009
Messages
6
Here is what I am using now. A friend of mine helped me out. But it is still not working. I am getting an error saying that niether the from or to sender is filled out. Do you see anything wrong with this code? I have the wrong email and pass word in there for obvious reasons.


Code:
Sub Smail(recip As Variant, subj As Variant)
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    Dim Flds As Variant


    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")


    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "jmilton@yahoo.com" 'put your email address of your service provider
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "jm0055785"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yahoo.com" 'ex smtp.gmail.com


        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Update
    End With


    'body of email
    strbody = "Hi there i am excel" & vbNewLine & vbNewLine & _
              "my code rocks" & vbNewLine


    With iMsg
        Set .Configuration = iConf
        .To = recip '"jmilton@yahoo.com" 'who ur sending to
        .CC = ""
        .BCC = ""
        .From = """"""
        .Subject = subj
        .TextBody = strbody
        .Send
    End With
End Sub
 

Forum statistics

Threads
1,082,106
Messages
5,363,180
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top