When cell changes, save

KlaasE

New Member
Joined
Nov 4, 2019
Messages
37
Hi there,

I have column O "Done by" with a checklist if a task is done. When the task is done someone changes the empty cell to their initials. In column D on the same row is the name of the document that the task was about. I'm trying to make a macro that saves the name of the document (column D) to a Dim, when Column O is being changed. With this Dim I will then send a mail that task x in document x is done.

Pretty simply I assume, but I just can't seem to find the solution.

Thanks!
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("O:O")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        'What goes here...?'

End If
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Does the name of the document in column D include the file extension (xlsx, xlsm)? What is the full path of the folder where you want to save the file?
 
Upvote 0
I was a little unclear I think. I just need the name as a string. So D4 for instance is "DIS19-0086". I then want to be able to send an email like this: "part number for DIS19-0886 has been added".
 
Upvote 0
Your title does not reflect the actual question you are asking. Saving is not the same as sending an email. Sending an email from Outlook is straightforward but it's a bunch of code. First, can you confirm that you want to use Outlook? The solution I showed below uses late binding so you don't have to worry about setting references to Outlook.

Also,
send a mail that task x in document x is done
how can the code tell what "task x" is? Is that in another column on the same row?

Your code works but could be a little simpler.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Column = [O1].Column Then

       EmailTask Doc:=Cells(Target.Row, "D"), Task:=Cells(Target.Row, "[COLOR=rgb(255, 0, 0)]?[/COLOR]") ' What column is task name?

   End If
End Sub

Public Sub EmailTask(Doc As String, Task As String)

   Const olMailItem = 0
   Const olTo = 1

   Dim myitem As Object ' MailItem
   Dim OutlookApp As Object
   Dim NewRecipient As Object ' Recipient
   Dim BreakPos As Long ' character position of line break character
   Dim i As Long
      
    Set OutlookApp = CreateObject("Outlook.Application")
    Set myitem = OutlookApp.CreateItem(olMailItem)
    
    myitem.Display
    
    myitem.Subject = "[COLOR=rgb(255, 0, 0)]Your subject goes here[/COLOR]"
       
    Set NewRecipient = myitem.Recipients.Add("[COLOR=rgb(255, 0, 0)]Email address of recipient goes here[/COLOR]")
    NewRecipient.Type = olTo
    NewRecipient.Resolve
      
    myitem.HTMLBody = "Task " & Task & " in document " & Doc & " is done." & myitem.HTMLBody
   
End Sub
A minor point on terminology: "Dim" is a statement that declares a variable. You don't want to save the name of the document to a "Dim" you want to save it to a variable.
 
Upvote 0
Thanks for the reply. My title was indeed half way finished, I wasn't able to edit it afterwards. Thanks a lot for the code! Works great. the task name is the header of column O, so always the same cell. Also thanks for the terminology comment, any free knowledge is appreciated. :)
 
Upvote 0
I don't think I've posted a question here, just answers, so I don't know how to change a title. I don't see an edit button on my own posts. I suppose you could click "Report" and request moderators to change it for you.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top