-
Nov 20th, 2020, 06:48 AM
#1
Thread Starter
Addicted Member
[RESOLVED] Save worksheet and send as attachment
Hi,
I wish to create a new macro that basically performs the below functions.
1. Save a worksheet (Master) to a particular drive with all the
same formats as .xlsx
File path. W:\.Team Documents\Freehold Team\Freeholder Managers\Reporting\Reports\
File to be saved as (Current month) MMM PM Template.
2. Attach file - MMM PM Template to email and send to the defined groups on control tab. Again Formats must be captured within the attachment.
I'm using outlook version 2002
-
Nov 20th, 2020, 03:41 PM
#2
Re: Save worksheet and send as attachment
untested
Code:
Dim w As Worksheet, b As Workbook, ol As Object, msg As Object
Dim mypath As String, myfile As String, scc As String, sto As String
mypath = "W:\.Team Documents\Freehold Team\Freeholder Managers\Reporting\Reports\"
With Sheets("Control")
sto = Join(WorksheetFunction.Transpose(Range("h6:h11")), ";")
scc = Join(WorksheetFunction.Transpose(Range("i6:i11")), ";")
End With
Set w = Sheets("master")
Set b = Workbooks.Add
w.Copy b.Sheets(1)
myfile = mypath & Format(Date, "MMM") & "PM Template.xlsx"
b.SaveAs myfile
Set ol = CreateObject("outlook.application")
Set msg = ol.CreateItem(0)
With msg
.To = sto
.CC = scc
.Subject = "Not sopecified"
.BodyFormat = olFormatHTML
.Body = "some text"
.Attachments.Add myfile
.Send
End With
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 23rd, 2020, 06:02 AM
#3
Thread Starter
Addicted Member
Re: Save worksheet and send as attachment
ok that's worked brilliantly. Just a few modifications i need to make. Simple ones as well.
1. The file that I have saved down. Remains open. How do i close (MMM PM Template) after its saved down? WB.close or b.close and where do I add this line?
2. On the file that I'm saving. I still see Sheet 1. Need this removed and only see the 'Master' tab
3. I've tried adding the below line for time stamp. but keep getting the below error message.
End with
Range("TimeStamp2"). Value = Format(Now, "dd/mm/yy HH:mm:ss")
Run-time error '1004':
Method 'Range' of object '_Global' failed
-
Nov 23rd, 2020, 03:32 PM
#4
Re: Save worksheet and send as attachment
b.close and where do I add this line? after b.saveas
I still see Sheet 1. Need this removed and only see the 'Master' tab
before saveas
Code:
Application.DisplayAlerts = False
For sh = b.Sheets.Count To 2 Step -1
sh.Delete
Next
Application.DisplayAlerts = True
but keep getting the below error message.
maybe trying to find the named range in the wrong workbook or sheet, fully qualify the range
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 23rd, 2020, 04:44 PM
#5
Thread Starter
Addicted Member
Re: Save worksheet and send as attachment
Breaks at....
sh.delete.
Object required.
Do I need to add sh As Object at the start???
I've tried to qualify the range using the below code but its not working.
b.worksheets("control").activate
Range("TimeStamp2"). Value = Format(Now, "dd/mm/yy HH:mm:ss")
Do I need to add DIM somewhere?
-
Nov 24th, 2020, 02:56 AM
#6
Re: Save worksheet and send as attachment
my error
Code:
b.sheets(sh).delete
range timestamp2 is probably a defined name in the original workbook, not in the new workbook, specify the cell address instead
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 24th, 2020, 05:04 AM
#7
Thread Starter
Addicted Member
Re: Save worksheet and send as attachment
B.sheets(sh).delete. Worked. Thank you.
Yes timestamp2 has been defined in the original workbook which is why the above doesn't work. What's the code so it picks up the defined name in the original workbook? or do you think referencing the cell address is better?
-
Nov 24th, 2020, 02:59 PM
#8
Re: Save worksheet and send as attachment
or do you think referencing the cell address is better?
as you are creating a new workbook and only writing it once i would think it is a lot simpler to just use the cell address
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Nov 25th, 2020, 04:33 AM
#9
Thread Starter
Addicted Member
Re: Save worksheet and send as attachment
Ok. What's the best way to reference the cell address?
-
Nov 25th, 2020, 04:53 AM
#10
Thread Starter
Addicted Member
Re: Save worksheet and send as attachment
Ignore my previous message. I've solved the problem. Cheers.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|