Help VBA Saving and Emailing file

[adammark]

I am extremely new with VBA, and would be highly appreciative of help.
I have the script below working.

I would like to modify it a bit

ChDir "C:\file1\files\CURRENT YEAR\CURRENT MONTH\"
directory = "C:\file1\files\CURRENT YEAR\CURRENT MONTH\""

I want to have the CURRENT YEAR and MONTH populate automatically based on thir current days


I want to have the email sent to recipients based on a list on Sheet1 cells A1:A10

And I need to add body text to the email HTML prefeable, or txt maybe read from a txt file on my hard drive.


Any and all help is great.

Thanks!!!



Sub sendfile()
Sheets(ActiveSheet.Name).Copy

ChDir "C:\file1\files\CURRENT YEAR\CURRENT MONTH\"
directory = "C:\file1\files\CURRENT YEAR\CURRENT MONTH\""


filenamesave = "My File " & FormatDateTime(Date, vbLongDate)
ActiveWorkbook.SaveAs Filename:=directory & filenamesave & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False

If MsgBox("Are you sure this file is ready to send?", vbOKCancel, _
"Are you sure...") = vbCancel Then Exit Sub


ActiveWorkbook.SendMail Recipients:="abc@abcd.com", Subject:=filenamesave
MsgBox "File Sent.", vbOKOnly

End Sub

 

[westconn1]

not sure i quite understand but, maybe
chdir "C:\file1\files\" & year(now) & "\" & month(now) &"\"

 

[adammark]

Thanks, it kinda works, it looks for the right path, I should have specified that I would like to the directories to be created if they havent already been.

 

[westconn1]

use dir with vbdirectory to find if it exists, if not use mkdir to make new directory, but you have to make each folder in turn, mkdir will not make the entire tree in one go
so if the directory does not exist you need to check if the parent directory exists etc

directoryexists = true
mydir = "C:\file1\files\" & year(now) & "\" & month(now) 
if len(dir(mydir, vbdirectory)) > 0 then    'directory or file found to match
      If not GetAttr(mydir) = vbDirectory Then directoryexists = false  'is file,not directory
   else directoryexists = false
end if
if directoryexists = false then mkdir mydir  ' assumes parent directory already exists

possibly best to make into a function, then can call it whenever you want

 

[adammark]

Thank You so much. i will try tonight to get it to work. I'm not sure if I know how to make the command into a directory

everythings before my year and month will always exist. I will need to create month and year on the fly if need be

is there a way to have the month dir create its name in full aka March instead of 3?

 

[westconn1]

mydir = "C:\file1\files\" & year(now) & "\" & format(month(now), "mmmm")

if the year directory does not exist you will need to create that before creating the month directory
</pre>

 

[wynandbez]

Regarding the format in which the file is saved i.e.

ActiveWorkbook.SaveAs Filename:= _
Path & SaveName _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Are there alternatives to xlNormal?
I would like to save the file as values only so that readers cannot see the formulas, but I would also like to preserve the formats (i.e. layout, page breaks, etc.)

Any advice on this? Thanks a lot!

 

[wynandbez]

Although it's not the neatest code, I tried this and it seems to work:

ActiveWorkbook.SaveAs Filename:= _
Path & SaveName _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Sheets("Report").Range("A:M").Copy
ActiveWorkbook.Sheets("Report").Range("A:M").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save