Re: $$Excel-Macros$$ Dynamic Attachment with Email Send
Function workbook_path(txt1 As String) As String Dim pathname As String Dim wkb As Workbook pathname = Environ("Temp") & txt1 & " " & VBA.Format(VBA.Now, "ddmmmyy_hhmmss") & ".xlsx" If Sheets("Branch Name").AutoFilterMode = True Then Sheets("Branch Name").AutoFilterMode = False End If Set wkb = Workbooks.Add With ThisWorkbook.Sheets("Branch Name") .Range("A1:b10").AutoFilter Field:=1, Criteria1:=txt1 Set rngToCopy = .Range("A1:b10").SpecialCells(xlCellTypeVisible) rngToCopy.Copy wkb.Sheets(1).Range("a1") .AutoFilterMode = False End With wkb.SaveAs pathname wkb.Close workbook_path = pathname End Function Sub tes() Dim OutApp As Object Dim OutMail As Object Dim cl As Range Set OutApp = CreateObject("Outlook.Application") For Each cl In Sheets("Email").Range("a2:a4") Set OutMail = OutApp.CreateItem(0) With OutMail .to = cl.Offset(0, 1).Value .cc = cl.Offset(0, 2).Value .Subject = cl.Value .Body = "hello" & vbNewLine & "Please find the attachment" .Attachments.Add workbook_path(cl.Value) .DISPLAY End With Set OutMail = Nothing Next Set OutApp = Nothing End Sub On Fri, Mar 14, 2014 at 9:23 PM, Vijay wrote: > Dear Mr. Richard, > > Thanks for the reply. im really happy for received E Mail from you for > what i'm expected and i have few more requirement to fulfill the process > what i exactly need as mentioned below. > > 1. I want to send it through MS Outlook instead of Gmail. > 2. Not Require a Command Button in Excel, its enough in normal Excel macro. > 3. in this excel sheet it shows command button and drop down button. but i > do not require a Command & drop down to select and split the branch name > instead of that if i required for particular branch name, it should be > automatically filtered and be attached to the E Mail. > > and i also attached example Branch Split Macro for your ready reference. > > Regards, > Vijay > +919940408060 > > > On Fri, Mar 14, 2014 at 8:35 AM, Ricardo(R) wrote: > >> Hi Vijay, >> >> Follows this example, see if it meets your need . >> >> Rgds. >> >> Basole. >> >> >> >> 2014-03-13 15:23 GMT-03:00 Vijay : >> >>> Hi Team, >>> >>> I need a help in creating a macro for attached Excel File in E Mail. >>> >>> Process Brief:- >>> 1) I have one excel file. In that excel file, there is one column named >>> as "Branch Name". Which is important us to find out Branch Name and that >>> particular >>> branch details should be create as Separate file. >>> 2) Once separate file has been created, Excel file name should be saved >>> as Branch name after which >>> is created that the particular sheet automatically attached to E mail >>> "TO and "CC" should assigned as per particulars available in the E mail >>> column in that excel sheet. >>> and if other branch file name to be sent as attachement in E Mail, it is >>> only to send as per the E Mail column details. Example if TO and/or CC >>> column kept as blank. Macro should not send to the same. and also E Mail >>> should not repeat. >>> As mentioned above steps, Suppose i need another branch name, after >>> created as branch file name and attached if i want to send E Mail (as >>> applicable in that column, irrespective of "TO" and "CC") >>> it should e mail as per particulars available in that column. >>> 3) I should be able to give the path where the files are stored. The >>> file would in .xls format. >>> >>> Thanking you in advance. >>> >>> Regards >>> Vijay >>> >>> -- >>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? >>> It's =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>> https://www.facebook.com/discussexcel >>> >>> FORUM RULES >>> >>> 1) Use concise, accurate thread titles. Poor thread titles, like Please >>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >>> will not get quick attention or may not be answered. >>> 2) Don't post a question in the thread of another member. >>> 3) Don't post questions regarding breaking or bypassing any security >>> measure. >>> 4) Acknowledge the responses you receive, good or bad. >>> 5) Jobs posting is not allowed. >>> 6) Sharing copyrighted material and their links is not allowed. >>> >>> NOTE : Don't ever post confidential data in a workbook. Forum owners and >>> members are not responsible for any loss. >>> --- >>> You received this message because you are subscribed to the Google >>> Groups "MS EXCEL AND VBA MACROS" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to excel-macros+unsubscr...@googlegroups.com. >>> >>> To post to this group, send email to excel-macros@googlegroups.com. >>> Visit this group at http://groups.google.com/group/excel-macros. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you w
Re: $$Excel-Macros$$ Dynamic Attachment with Email Send
Dear Mr. Richard, Thanks for the reply. im really happy for received E Mail from you for what i'm expected and i have few more requirement to fulfill the process what i exactly need as mentioned below. 1. I want to send it through MS Outlook instead of Gmail. 2. Not Require a Command Button in Excel, its enough in normal Excel macro. 3. in this excel sheet it shows command button and drop down button. but i do not require a Command & drop down to select and split the branch name instead of that if i required for particular branch name, it should be automatically filtered and be attached to the E Mail. and i also attached example Branch Split Macro for your ready reference. Regards, Vijay +919940408060 On Fri, Mar 14, 2014 at 8:35 AM, Ricardo(R) wrote: > Hi Vijay, > > Follows this example, see if it meets your need . > > Rgds. > > Basole. > > > > 2014-03-13 15:23 GMT-03:00 Vijay : > >> Hi Team, >> >> I need a help in creating a macro for attached Excel File in E Mail. >> >> Process Brief:- >> 1) I have one excel file. In that excel file, there is one column named >> as "Branch Name". Which is important us to find out Branch Name and that >> particular >> branch details should be create as Separate file. >> 2) Once separate file has been created, Excel file name should be saved >> as Branch name after which >> is created that the particular sheet automatically attached to E mail "TO >> and "CC" should assigned as per particulars available in the E mail column >> in that excel sheet. >> and if other branch file name to be sent as attachement in E Mail, it is >> only to send as per the E Mail column details. Example if TO and/or CC >> column kept as blank. Macro should not send to the same. and also E Mail >> should not repeat. >> As mentioned above steps, Suppose i need another branch name, after >> created as branch file name and attached if i want to send E Mail (as >> applicable in that column, irrespective of "TO" and "CC") >> it should e mail as per particulars available in that column. >> 3) I should be able to give the path where the files are stored. The file >> would in .xls format. >> >> Thanking you in advance. >> >> Regards >> Vijay >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> 2) Don't post a question in the thread of another member. >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> 4) Acknowledge the responses you receive, good or bad. >> 5) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros+unsubscr...@googlegroups.com. >> >> To post to this group, send email to excel-macros@googlegroups.com. >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to a topic in the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/excel-macros/kY0OikQHSMo/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It's =TIME(2,DO:IT,N:OW) ! Join o
$$Excel-Macros$$ Dynamic Attachment with Email Send
Hi Team, I need a help in creating a macro for attached Excel File in E Mail. Process Brief:- 1) I have one excel file. In that excel file, there is one column named as "Branch Name". Which is important us to find out Branch Name and that particular branch details should be create as Separate file. 2) Once separate file has been created, Excel file name should be saved as Branch name after which is created that the particular sheet automatically attached to E mail "TO and "CC" should assigned as per particulars available in the E mail column in that excel sheet. and if other branch file name to be sent as attachement in E Mail, it is only to send as per the E Mail column details. Example if TO and/or CC column kept as blank. Macro should not send to the same. and also E Mail should not repeat. As mentioned above steps, Suppose i need another branch name, after created as branch file name and attached if i want to send E Mail (as applicable in that column, irrespective of "TO" and "CC") it should e mail as per particulars available in that column. 3) I should be able to give the path where the files are stored. The file would in .xls format. Thanking you in advance. Regards Vijay -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. MIS.xls Description: MS-Excel spreadsheet