How to apply macro on new file

Like i have saved macro in module & i want to work it when i open a
new file.

Put it in Personal.xls.


Personal.xls is located in the XLStart directory, and is used to store
macros and
things that you want to be available to all workbooks, whenever you start
Excel.

You can create it by
- goto ToolsMacrosRecord New Macro...
- Choose Personal Macro Workbook form the dropdown
- OK
- click the Stop button on the toolbar that pops-up

You now have a Personal.xls workbook. It is not visible though, it is hidden
by default (WindowsUnhide)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Amol" wrote in message
oups.com...
How to apply macro on new file

Like i have saved macro in module & i want to work it when i open a
new file.

Hi Bob

Thnxs for ur suggestion

Pls find VBA code of macro where i am not able to connect it with new
file which contains diifferent sheet with client code as sheet
name....Though it is woking fine if i run macro in one file

Sub Mail_Every_Worksheet()
'Working in 2000-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim MailAdress As String


TempFilePath = Environ$("temp") & "\"


If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon


For Each sh In ActiveWorkbook.Worksheets
MailAdress = ""
On Error Resume Next
MailAdress =
Application.WorksheetFunction.VLookup(Int(sh.Name) ,
Sheets("LookupTable").Range("A1:B500"), 2, False)
On Error GoTo 0
strbody = "Dear All" & vbNewLine & vbNewLine & _
"Please find attached file of Credit/Debit given
to your account on dt" & " " & Format(Now, "dd-mmm-yy") & vbNewLine &
_
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
"Thanks & Regards" & vbNewLine & _
"" & vbNewLine & _
"Operations" & vbNewLine & _
""
If MailAdress Like "?*@?*.?*" Then


sh.Copy
Set wb = ActiveWorkbook


TempFileName = "Daily Credit MIS Dt." & " " & Format(Now,
"dd-mmm-yy") & " " & sh.Name


Set OutMail = OutApp.CreateItem(0)
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = MailAdress
.CC = ""
.BCC = ""
.Subject = "TRANSACTIONS" & " " & sh.Name
.Body = strbody

.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.display 'or use .Display
End With
On Error GoTo 0
.Close SaveChanges:=False
End With
Set OutMail = Nothing


Kill TempFilePath & TempFileName & FileExtStr
End If
Next sh


Set OutApp = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub






On Oct 29, 7:38 pm, "Bob Phillips" wrote:
Put it in Personal.xls.

Personal.xls is located in the XLStart directory, and is used to store
macros and
things that you want to be available to all workbooks, whenever you start
Excel.

You can create it by
- goto ToolsMacrosRecord New Macro...
- Choose Personal Macro Workbook form the dropdown
- OK
- click the Stop button on the toolbar that pops-up

You now have a Personal.xls workbook. It is not visible though, it is hidden
by default (WindowsUnhide)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Amol" wrote in message

oups.com...



How to apply macro on new file

Like i have saved macro in module & i want to work it when i open a
new file.- Hide quoted text -

- Show quoted text -

On Oct 29, 7:38 pm, "Bob Phillips" wrote:
Put it in Personal.xls.

Personal.xls is located in the XLStart directory, and is used to store
macros and
things that you want to be available to all workbooks, whenever you start
Excel.

You can create it by
- goto ToolsMacrosRecord New Macro...
- Choose Personal Macro Workbook form the dropdown
- OK
- click the Stop button on the toolbar that pops-up

You now have a Personal.xls workbook. It is not visible though, it is hidden
by default (WindowsUnhide)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Amol" wrote in message

oups.com...



How to apply macro on new file

Like i have saved macro in module & i want to work it when i open a
new file.- Hide quoted text -

- Show quoted text -

Hi Bob

Thnxs for ur suggestion

Pls find VBA code of macro where i am not able to connect it with new
file which contains diifferent sheet with client code as sheet
name....Though it is woking fine if i run macro in one file

Sub Mail_Every_Worksheet()
'Working in 2000-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim MailAdress As String


TempFilePath = Environ$("temp") & "\"


If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon


For Each sh In ActiveWorkbook.Worksheets
MailAdress = ""
On Error Resume Next
MailAdress =
Application.WorksheetFunction.VLookup(Int(sh.Name) ,
Sheets("LookupTable").Range("A1:B500"), 2, False)
On Error GoTo 0
strbody = "Dear All" & vbNewLine & vbNewLine & _
"Please find attached file of Credit/Debit given
to your account on dt" & " " & Format(Now, "dd-mmm-yy") & vbNewLine &
_
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
"Thanks & Regards" & vbNewLine & _
"" & vbNewLine & _
"Operations" & vbNewLine & _
""
If MailAdress Like "?*@?*.?*" Then


sh.Copy
Set wb = ActiveWorkbook


TempFileName = "Daily Credit MIS Dt." & " " & Format(Now,
"dd-mmm-yy") & " " & sh.Name


Set OutMail = OutApp.CreateItem(0)
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = MailAdress
.CC = ""
.BCC = ""
.Subject = "TRANSACTIONS" & " " & sh.Name
.Body = strbody

.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.display 'or use .Display
End With
On Error GoTo 0
.Close SaveChanges:=False
End With
Set OutMail = Nothing


Kill TempFilePath & TempFileName & FileExtStr
End If
Next sh


Set OutApp = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Hi Bob

Thnxs for ur suggestion

Pls find VBA code of macro where i am not able to connect it with new
file which contains diifferent sheet with client code as sheet
name....Though it is woking fine if i run macro in one file

Sub Mail_Every_Worksheet()
'Working in 2000-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim MailAdress As String


TempFilePath = Environ$("temp") & "\"


If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon


For Each sh In ActiveWorkbook.Worksheets
MailAdress = ""
On Error Resume Next
MailAdress =
Application.WorksheetFunction.VLookup(Int(sh.Name) ,
Sheets("LookupTable").Range("A1:B500"), 2, False)
On Error GoTo 0
strbody = "Dear All" & vbNewLine & vbNewLine & _
"Please find attached file of Credit/Debit given
to your account on dt" & " " & Format(Now, "dd-mmm-yy") & vbNewLine &
_
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
"Thanks & Regards" & vbNewLine & _
"" & vbNewLine & _
"Operations" & vbNewLine & _
""
If MailAdress Like "?*@?*.?*" Then


sh.Copy
Set wb = ActiveWorkbook


TempFileName = "Daily Credit MIS Dt." & " " & Format(Now,
"dd-mmm-yy") & " " & sh.Name


Set OutMail = OutApp.CreateItem(0)
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = MailAdress
.CC = ""
.BCC = ""
.Subject = "TRANSACTIONS" & " " & sh.Name
.Body = strbody

.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.display 'or use .Display
End With
On Error GoTo 0
.Close SaveChanges:=False
End With
Set OutMail = Nothing


Kill TempFilePath & TempFileName & FileExtStr
End If
Next sh


Set OutApp = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub





On Oct 29, 7:38 pm, "Bob Phillips" wrote:
Put it in Personal.xls.

Personal.xls is located in the XLStart directory, and is used to store
macros and
things that you want to be available to all workbooks, whenever you start
Excel.

You can create it by
- goto ToolsMacrosRecord New Macro...
- Choose Personal Macro Workbook form the dropdown
- OK
- click the Stop button on the toolbar that pops-up

You now have a Personal.xls workbook. It is not visible though, it is hidden
by default (WindowsUnhide)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Amol" wrote in message

oups.com...



How to apply macro on new file

Like i have saved macro in module & i want to work it when i open a
new file.- Hide quoted text -

- Show quoted text -