I want to create a main macro file from which i can apply same macro
for new files which i open

Like i have a macro file which can send mail to different recepiant as
all mail address are defined in 1st sheet with specified sheet name on
it.

But the problem arises when i select new saved file. Macro wont work
at that time.

Please help

You can add your macro in your Personal.xls(b)
See
http://www.rondebruin.nl/personal.htm

Be aware that if you use Thisworkbook in the code it will point to the personal.xls(b)


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Amol" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>
> I want to create a main macro file from which i can apply same macro
> for new files which i open
>
> Like i have a macro file which can send mail to different recepiant as
> all mail address are defined in 1st sheet with specified sheet name on
> it.
>
> But the problem arises when i select new saved file. Macro wont work
> at that time.
>
> Please help
>

Is this for use just on your PC? If so, you could place the code in
your Personal.xls macro worbook which opens each time you open Excel.
I may be misunderstanding your question though.

Amol wrote:
> I want to create a main macro file from which i can apply same macro
> for new files which i open
>
> Like i have a macro file which can send mail to different recepiant as
> all mail address are defined in 1st sheet with specified sheet name on
> it.
>
> But the problem arises when i select new saved file. Macro wont work
> at that time.
>
> Please help

But does the same macro apply for all sheet.

is that create any problem for me.....cause most of the work is in
xl....and sending mail is just one of the activity




On Aug 6, 9:31 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> You can add your macro in your Personal.xls(b)
> Seehttp://www.rondebruin.nl/personal.htm
>
> Be aware that if you use Thisworkbook in the code it will point to the personal.xls(b)
>
> --
>
> Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
>
>
> "Amol" <amol.achare...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
> > I want to create a main macro file from which i can apply same macro
> > for new files which i open
>
> > Like i have a macro file which can send mail to different recepiant as
> > all mail address are defined in 1st sheet with specified sheet name on
> > it.
>
> > But the problem arises when i select new saved file. Macro wont work
> > at that time.
>
> > Please help- Hide quoted text -
>
> - Show quoted text -

Depends on how you have your code written. For instance, if you use
ThisWorkbook to refer to a workbook, it will only refer to the
workbook that the macro is stored in. However, if you use
ActiveWorkbook, you can run that snippet of code from any workbook and
it will apply to the active workbook. This is only a very simple
example of how your code will impact which workbook is affected. We
could help you more if you posted an example of your code.

Amol wrote:
> But does the same macro apply for all sheet.
>
> is that create any problem for me.....cause most of the work is in
> xl....and sending mail is just one of the activity
>
>
>
>
> On Aug 6, 9:31 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> > You can add your macro in your Personal.xls(b)
> > Seehttp://www.rondebruin.nl/personal.htm
> >
> > Be aware that if you use Thisworkbook in the code it will point to the personal.xls(b)
> >
> > --
> >
> > Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
> >
> >
> >
> > "Amol" <amol.achare...@gmail.com> wrote in messagenews:(E-Mail Removed)...
> >
> > > I want to create a main macro file from which i can apply same macro
> > > for new files which i open
> >
> > > Like i have a macro file which can send mail to different recepiant as
> > > all mail address are defined in 1st sheet with specified sheet name on
> > > it.
> >
> > > But the problem arises when i select new saved file. Macro wont work
> > > at that time.
> >
> > > Please help- Hide quoted text -
> >
> > - Show quoted text -

This is the code which RON gave me & its working fine if i add sheet
mannually in that particular workbook, However this is my daily
activity to send mail to the clients.
Now i have two different files One which contains macro & LookupTable
& second one contains data in different sheets.

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"

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 = "CMS 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 Aug 6, 9:43 pm, JW <JWRIGH...@triad.rr.com> wrote:
> Depends on how you have your code written. For instance, if you use
> ThisWorkbook to refer to a workbook, it will only refer to the
> workbook that the macro is stored in. However, if you use
> ActiveWorkbook, you can run that snippet of code from any workbook and
> it will apply to the active workbook. This is only a very simple
> example of how your code will impact which workbook is affected. We
> could help you more if you posted an example of your code.
>
>
>
> Amol wrote:
> > But does the same macro apply for all sheet.
>
> > is that create any problem for me.....cause most of the work is in
> > xl....and sending mail is just one of the activity
>
> > On Aug 6, 9:31 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> > > You can add your macro in your Personal.xls(b)
> > > Seehttp://www.rondebruin.nl/personal.htm
>
> > > Be aware that if you use Thisworkbook in the code it will point to the personal.xls(b)
>
> > > --
>
> > > Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
> > > "Amol" <amol.achare...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
> > > > I want to create a main macro file from which i can apply same macro
> > > > for new files which i open
>
> > > > Like i have a macro file which can send mail to different recepiant as
> > > > all mail address are defined in 1st sheet with specified sheet name on
> > > > it.
>
> > > > But the problem arises when i select new saved file. Macro wont work
> > > > at that time.
>
> > > > Please help- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

The personal.xls file is opened every time you start Excel so all macros in
it are available regardless of the other books that are open. The macro that
you posted references all sheets in the active workbook so it will work fine
stored in the personal.xls workbook and run using other books being active.

Peter Richardson

"Amol" wrote:

> This is the code which RON gave me & its working fine if i add sheet
> mannually in that particular workbook, However this is my daily
> activity to send mail to the clients.
> Now i have two different files One which contains macro & LookupTable
> & second one contains data in different sheets.
>
> 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"
>
> 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 = "CMS 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 Aug 6, 9:43 pm, JW <JWRIGH...@triad.rr.com> wrote:
> > Depends on how you have your code written. For instance, if you use
> > ThisWorkbook to refer to a workbook, it will only refer to the
> > workbook that the macro is stored in. However, if you use
> > ActiveWorkbook, you can run that snippet of code from any workbook and
> > it will apply to the active workbook. This is only a very simple
> > example of how your code will impact which workbook is affected. We
> > could help you more if you posted an example of your code.
> >
> >
> >
> > Amol wrote:
> > > But does the same macro apply for all sheet.
> >
> > > is that create any problem for me.....cause most of the work is in
> > > xl....and sending mail is just one of the activity
> >
> > > On Aug 6, 9:31 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> > > > You can add your macro in your Personal.xls(b)
> > > > Seehttp://www.rondebruin.nl/personal.htm
> >
> > > > Be aware that if you use Thisworkbook in the code it will point to the personal.xls(b)
> >
> > > > --
> >
> > > > Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
> >
> > > > "Amol" <amol.achare...@gmail.com> wrote in messagenews:(E-Mail Removed)...
> >
> > > > > I want to create a main macro file from which i can apply same macro
> > > > > for new files which i open
> >
> > > > > Like i have a macro file which can send mail to different recepiant as
> > > > > all mail address are defined in 1st sheet with specified sheet name on
> > > > > it.
> >
> > > > > But the problem arises when i select new saved file. Macro wont work
> > > > > at that time.
> >
> > > > > Please help- Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

Confused!@##$$$%%

i am very new to VBA progamming and macro

Please tell me what to do?

where do i copy this macro? and from which sheet i suppose to run it?

Is it from LookupTable or from Data File





On Aug 6, 11:02 pm, barnabel <barna...@discussions.microsoft.com>
wrote:
> The personal.xls file is opened every time you start Excel so all macros in
> it are available regardless of the other books that are open. The macro that
> you posted references all sheets in the active workbook so it will work fine
> stored in the personal.xls workbook and run using other books being active.
>
> Peter Richardson
>
>
>
> "Amol" wrote:
> > This is the code which RON gave me & its working fine if i add sheet
> > mannually in that particular workbook, However this is my daily
> > activity to send mail to the clients.
> > Now i have two different files One which contains macro & LookupTable
> > & second one contains data in different sheets.
>
> > 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"
>
> > 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 = "CMS 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 Aug 6, 9:43 pm, JW <JWRIGH...@triad.rr.com> wrote:
> > > Depends on how you have your code written. For instance, if you use
> > > ThisWorkbook to refer to a workbook, it will only refer to the
> > > workbook that the macro is stored in. However, if you use
> > > ActiveWorkbook, you can run that snippet of code from any workbook and
> > > it will apply to the active workbook. This is only a very simple
> > > example of how your code will impact which workbook is affected. We
> > > could help you more if you posted an example of your code.
>
> > > Amol wrote:
> > > > But does the same macro apply for all sheet.
>
> > > > is that create any problem for me.....cause most of the work is in
> > > > xl....and sending mail is just one of the activity
>
> > > > On Aug 6, 9:31 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> > > > > You can add your macro in your Personal.xls(b)
> > > > > Seehttp://www.rondebruin.nl/personal.htm
>
> > > > > Be aware that if you use Thisworkbook in the code it will point to the personal.xls(b)
>
> > > > > --
>
> > > > > Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>
> > > > > "Amol" <amol.achare...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>
> > > > > > I want to create a main macro file from which i can apply same macro
> > > > > > for new files which i open
>
> > > > > > Like i have a macro file which can send mail to different recepiant as
> > > > > > all mail address are defined in 1st sheet with specified sheet name on
> > > > > > it.
>
> > > > > > But the problem arises when i select new saved file. Macro wont work
> > > > > > at that time.
>
> > > > > > Please help- Hide quoted text -
>
> > > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

create an empty excel workbook
add the macro
save the workbook as: (note the directory is where Excel is installed)
"C:\Program Files\Microsoft Office\Office\XLStart\personal.xls"

Now any time you start excel the macros stored in personal.xls will be
available to run.

Open the workbook that has the "LookupTable" sheet. It does not matter
which sheet in that workbook you have active when you start the macro. The
macro is going to look at every sheet in the active workbook.

Peter Richardson



"Amol" wrote:

> Confused!@##$$$%%
>
> i am very new to VBA progamming and macro
>
> Please tell me what to do?
>
> where do i copy this macro? and from which sheet i suppose to run it?
>
> Is it from LookupTable or from Data File
>
>
>
>
>
> On Aug 6, 11:02 pm, barnabel <barna...@discussions.microsoft.com>
> wrote:
> > The personal.xls file is opened every time you start Excel so all macros in
> > it are available regardless of the other books that are open. The macro that
> > you posted references all sheets in the active workbook so it will work fine
> > stored in the personal.xls workbook and run using other books being active.
> >
> > Peter Richardson
> >
> >
> >
> > "Amol" wrote:
> > > This is the code which RON gave me & its working fine if i add sheet
> > > mannually in that particular workbook, However this is my daily
> > > activity to send mail to the clients.
> > > Now i have two different files One which contains macro & LookupTable
> > > & second one contains data in different sheets.
> >
> > > 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"
> >
> > > 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 = "CMS 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 Aug 6, 9:43 pm, JW <JWRIGH...@triad.rr.com> wrote:
> > > > Depends on how you have your code written. For instance, if you use
> > > > ThisWorkbook to refer to a workbook, it will only refer to the
> > > > workbook that the macro is stored in. However, if you use
> > > > ActiveWorkbook, you can run that snippet of code from any workbook and
> > > > it will apply to the active workbook. This is only a very simple
> > > > example of how your code will impact which workbook is affected. We
> > > > could help you more if you posted an example of your code.
> >
> > > > Amol wrote:
> > > > > But does the same macro apply for all sheet.
> >
> > > > > is that create any problem for me.....cause most of the work is in
> > > > > xl....and sending mail is just one of the activity
> >
> > > > > On Aug 6, 9:31 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> > > > > > You can add your macro in your Personal.xls(b)
> > > > > > Seehttp://www.rondebruin.nl/personal.htm
> >
> > > > > > Be aware that if you use Thisworkbook in the code it will point to the personal.xls(b)
> >
> > > > > > --
> >
> > > > > > Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
> >
> > > > > > "Amol" <amol.achare...@gmail.com> wrote in messagenews:(E-Mail Removed)...
> >
> > > > > > > I want to create a main macro file from which i can apply same macro
> > > > > > > for new files which i open
> >
> > > > > > > Like i have a macro file which can send mail to different recepiant as
> > > > > > > all mail address are defined in 1st sheet with specified sheet name on
> > > > > > > it.
> >
> > > > > > > But the problem arises when i select new saved file. Macro wont work
> > > > > > > at that time.
> >
> > > > > > > Please help- Hide quoted text -
> >
> > > > > > - Show quoted text -- Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

Please see
http://www.rondebruin.nl/personal.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Amol" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Confused!@##$$$%%
>
> i am very new to VBA progamming and macro
>
> Please tell me what to do?
>
> where do i copy this macro? and from which sheet i suppose to run it?
>
> Is it from LookupTable or from Data File
>
>
>
>
>
> On Aug 6, 11:02 pm, barnabel <barna...@discussions.microsoft.com>
> wrote:
>> The personal.xls file is opened every time you start Excel so all macros in
>> it are available regardless of the other books that are open. The macro that
>> you posted references all sheets in the active workbook so it will work fine
>> stored in the personal.xls workbook and run using other books being active.
>>
>> Peter Richardson
>>
>>
>>
>> "Amol" wrote:
>> > This is the code which RON gave me & its working fine if i add sheet
>> > mannually in that particular workbook, However this is my daily
>> > activity to send mail to the clients.
>> > Now i have two different files One which contains macro & LookupTable
>> > & second one contains data in different sheets.
>>
>> > 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"
>>
>> > 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 = "CMS 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 Aug 6, 9:43 pm, JW <JWRIGH...@triad.rr.com> wrote:
>> > > Depends on how you have your code written. For instance, if you use
>> > > ThisWorkbook to refer to a workbook, it will only refer to the
>> > > workbook that the macro is stored in. However, if you use
>> > > ActiveWorkbook, you can run that snippet of code from any workbook and
>> > > it will apply to the active workbook. This is only a very simple
>> > > example of how your code will impact which workbook is affected. We
>> > > could help you more if you posted an example of your code.
>>
>> > > Amol wrote:
>> > > > But does the same macro apply for all sheet.
>>
>> > > > is that create any problem for me.....cause most of the work is in
>> > > > xl....and sending mail is just one of the activity
>>
>> > > > On Aug 6, 9:31 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
>> > > > > You can add your macro in your Personal.xls(b)
>> > > > > Seehttp://www.rondebruin.nl/personal.htm
>>
>> > > > > Be aware that if you use Thisworkbook in the code it will point to the personal.xls(b)
>>
>> > > > > --
>>
>> > > > > Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
>>
>> > > > > "Amol" <amol.achare...@gmail.com> wrote in messagenews:(E-Mail Removed)...
>>
>> > > > > > I want to create a main macro file from which i can apply same macro
>> > > > > > for new files which i open
>>
>> > > > > > Like i have a macro file which can send mail to different recepiant as
>> > > > > > all mail address are defined in 1st sheet with specified sheet name on
>> > > > > > it.
>>
>> > > > > > But the problem arises when i select new saved file. Macro wont work
>> > > > > > at that time.
>>
>> > > > > > Please help- Hide quoted text -
>>
>> > > > > - Show quoted text -- Hide quoted text -
>>
>> > > - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text -
>
>