UREGNT - Need Makro to remove formula from excel file before attaching into email

[aerricang]

I got this file from myt colleague which can:-
- vlookup details from >1 data source
- auto capture the excel details onto email content
- able to auto capture the SI email subject (K12) where we use & function to link a few field on the excel to derive the email subject
- auto capture the distribution email list (K14 & K15) where we vlookup from a data source

Question 1 : The problem i face now is the file size of the excel is too big when attached onto the email. I need to add in makro to remove all formula from the excel before attaching it onto the email.

Question 2 : the current makro i have will request me to save the file as NewSI.xls before attaching it to email. it is possible to have a makro that can auto save the file name based on a value from a specific cell in the excel?

Question 3 : If i must save onto the same file name NewSI.xls , how can i retain the vlookup formula for future use? must i create a few file with different name (eg. NewSI-ABC.xls & NewSI-XYZ.xls) with the vlookup formula & when need run the makro, save it onto NewSI.xls (removing formula). with this my NewSI-ABC.xls will not be over-write without formula. I can re-open NewSI-ABC.xls (with formula) for another use.

i dont have knowledge in Makro totally, so when you respond, i might not understand the technical terms. Might need your help to edit on the Makro & I will just copy paste onto my file.


the Makro i have is as per below


<CODE>Option ExplicitSub Outlook_Mail_Every_Worksheet_Body() Dim rng As Range Dim OutApp As Object Dim OutMail As Object ThisWorkbook.Save ActiveWorkbook.SaveAs Filename:="C:\Data\NewSI.xls", FileFormat:=xlNormal, WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False With Application .EnableEvents = False .ScreenUpdating = False End With Dim TotalRange As String TotalRange = Sheets("SI Template").Range("K11").Value 'MsgBox TotalRange Set rng = Nothing On Error Resume Next Set rng = Sheets("SI Template").Range(TotalRange).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = Sheets("SI Template").Range("K14").Value .CC = Sheets("SI Template").Range("K15").Value .BCC = "" .Subject = Sheets("SI Template").Range("K12").Value .HTMLBody = RangetoHTML(rng) .Attachments.Add "C:\Data\NewSI.xls" .Display 'or use .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub</CODE></PRE>

 

[pete234]

Sorry I just reread my post which suggested creating an .xlt file and it doesn't look like it will work with this macro, since it is already forcing a save in a specified name. It looks like you might be able to get around having to rename by setting it to add a date or time.

 

[aerricang]

Sorry I just reread my post which suggested creating an .xlt file and it doesn't look like it will work with this macro, since it is already forcing a save in a specified name. It looks like you might be able to get around having to rename by setting it to add a date or time.

HI Pete, thanks for your help here.
may i know how to add in the macro ya? whcih part should i include the macro in?

 

[pete234]

HI Pete, thanks for your help here.
may i know how to add in the macro ya? whcih part should i include the macro in?

:="C:\Data\NewSI.xls",

You would add some date or time formatting like in this post below to the save file.
This will append the date to the name, so you'll have new files.

http://www.ozgrid.com/forum/showthread.php?t=65552

If you only run once per week or day, you could probably use Today instead of now without the time stamp.
If more than once per day, use Now. This way your original file will always be preserved.
Once saved you can copy the sheet SI Template and Paste Special values to get rid of the formulas.
You would just go back to the first file to rerun again.
If you're not sure how to get to the developer tab or edit the macro, I'd ask the guy on your team. Show him this and he probably figure out how to do it.

Otherwise if 2010 this page can show you how to edit a macro:

http://msdn.microsoft.com/en-us/library/ee814737.aspx

 

[Michael M]

Try this.
I have assumed your file will be named after the value in cell A1

Sub SaveIt2()
Dim Fname As String, sname As String, wb As Workbook
Application.ScreenUpdating = False
Fname = Range("A1").Value & ".xls"
sname = ThisWorkbook.Path
Set wb = ActiveWorkbook
        With ActiveSheet.UsedRange
            .Cells.Value = .Cells.Value
        End With
        With wb
            .SaveAs sname & "\" & Fname
        End With
Application.ScreenUpdating = True
End Sub