Date Stamp & Mailto

[hmltnangel]

Hi folks,

Got two seemingly simple queries that I cant figure out myself.

First is to put a simple date stamp in a cell so I know when another cell was filled in. I have put the following formula in it but I know its wrong and Im not sure how to make it work correctly....

=IF(C5<>"",IF(J2="",NOW(),J2),"")

Basically I want J2 to record the date that C5 was completed.



Second up I have a 'mailto' setup so that when a button is clicked it mails the workbook to someone. Is it possible to have it copy a particular cell and use the contents of that cell as the subject for the email?

thanks

D

 

[hmltnangel]

bump*

noone got any ideas?

 

[jim may]

With your Sheet looking like this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1						Mail to: >>>>>	jmay@cox.net			Cell C5 last modified on:
2						Subject Line: >>>	Your Daily Report			11/10/2011
3
4
5			12
6
7
Sheet1

Excel 2007

In the Same Sheets Code window paste in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address<> "$C$5" Then Exit Sub
Application.EnableEvents = False
Range("J2").Value = Date
Application.EnableEvents = True
End Sub

Create a Forms Command Button on your sheet (Button1) -- the Event code will be created in a Standard Module. Inside the Wrapper Sub Button1_Click post in the below code to where it llooks like so:

Sub Button1_Click()
ActiveWorkbook.SaveCopyAs Filename:="C:\Users\James\Documents\ExcelChest\MyDaily\MyDaily-" & Format(Date, "mm-dd-yyyy") & ".xlsm"
ActiveWorkbook.SendMail Recipients:=Range("G1"), Subject:=Range("G2")
End Sub

CAUTION -- CHANGE ABOVE RED LETTERING TO YOUR SETUPS BEFORE RUNNING...

 

[hmltnangel]

brilliant - that works perfectly

 

[hmltnangel]

Well .... it did work, and now its stopped working..... any ideas why?

Does it make any difference if I protect the worksheet?

D

 

[jim may]

Are you getting a Run-time error - where code is halting and the Debug Option is showing up?