Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
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" 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" wrote in ooglegroups.com... 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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
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" 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" wrote in ooglegroups.com... 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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
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 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" 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" wrote in ooglegroups.com... 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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
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 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" 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" wrote in ooglegroups.com... 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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
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 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 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" 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" wrote in ooglegroups.com... 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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
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 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 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" 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" wrote in ooglegroups.com... 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 - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
Please see
http://www.rondebruin.nl/personal.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Amol" wrote in message oups.com... 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 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 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" 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" wrote in ooglegroups.com... 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 - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
I have two files one is contain mail address which is named as
LookupTable & another workbook contains multiple sheets which contains different client data which has to send respective mail id's Now as per u suggested if i save macro in new file then that will be third file My course of action will be...... A) open a file which contains macro B) then Open LookupTable file which contains Mailaddress C)Open file which contains data that need to send to different client Now I have three files open and still macro dosent work......May be some changes need to be done in above code..... On Aug 7, 12:14 am, barnabel wrote: 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 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 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" 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" wrote in ooglegroups.com... 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 -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Create Main Macro File
The macro you posted does not reference any workbook except the active
workbook (which you open). You might need to change the mailaddress line like: MailAdress = Application.WorksheetFunction.VLookup(Int(sh.Name) , workbooks("Nameof Book with LookupSheet").Sheets("LookupTable").Range("A1:B500 "), 2, False) Then you run the macro from the workbook with the sheets that you want to mail. However, It is currently mailing the entire workbook to each person that gets an email. This is PROBABLY not desirable. You need to add a new workbook and copy the sheet into it. Take a look at: 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) , workbooks("NameofBookWithLookUp").Sheets("LookupTa ble").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 Workbooks.Add Set wb = ActiveWorkbook sh.Copy 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 "Amol" wrote: I have two files one is contain mail address which is named as LookupTable & another workbook contains multiple sheets which contains different client data which has to send respective mail id's Now as per u suggested if i save macro in new file then that will be third file My course of action will be...... A) open a file which contains macro B) then Open LookupTable file which contains Mailaddress C)Open file which contains data that need to send to different client Now I have three files open and still macro dosent work......May be some changes need to be done in above code..... On Aug 7, 12:14 am, barnabel wrote: 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 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 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" 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" wrote in ooglegroups.com... 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 -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to insert row in a linked file through main file. | Excel Programming | |||
how to insert row in a linked file through main file. | Excel Programming | |||
How can I show the name of the file on top of the Main Menu Bar | New Users to Excel | |||
how to create sub-groups within a main group | Excel Discussion (Misc queries) | |||
Create main switchboard and forms | Excel Programming |