Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email according to ListBox items
Hi all, I have ListBox1 on my sheet and with one click of button I
get all the files names from specific folder into ListBox1 item list. See below the example of files names. Derek Williom.xlsm John Robert.xls Steve Wasten.xlsx Jerry Ali.xlsm etc .. I want macro on a button which should Send separate Emails and in each email it should attache one file from ListBox1 items and put person's name in email address (where it say "TO") which will be the characters before the dot in file name. For example taking above files names lets say i have these files names in ListBox1 and which i got from folder "C:\Files". Because i have four files so i need to send four emails and in first email i need "Derek Williom.xlsm" file to be attached and in "TO" section i want the characters before the dot in file name so in this case it will be "Derek Williom" and same thing i need with other peoples email. Basically i want to send emails to people with their name file attached. The files in list box can be 1 or 100 or may be more. I hope i was able to explain my question. Please can any friend can help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email according to ListBox items
This sounds like it would be better as an Outlook VBA Macro rather than an
Excel VBA Macro. Even though the files are excel files it would be better as an email solution. You probably have Outlook opened on your PC and it would be simplier to just push a button in outlook than to open an excel file and run a macro. "K" wrote: Hi all, I have ListBox1 on my sheet and with one click of button I get all the files names from specific folder into ListBox1 item list. See below the example of files names. Derek Williom.xlsm John Robert.xls Steve Wasten.xlsx Jerry Ali.xlsm etc¦¦¦¦.. I want macro on a button which should Send separate Emails and in each email it should attache one file from ListBox1 items and put person's name in email address (where it say "TO") which will be the characters before the dot in file name. For example taking above files names lets say i have these files names in ListBox1 and which i got from folder "C:\Files". Because i have four files so i need to send four emails and in first email i need "Derek Williom.xlsm" file to be attached and in "TO" section i want the characters before the dot in file name so in this case it will be "Derek Williom" and same thing i need with other peoples email. Basically i want to send emails to people with their name file attached. The files in list box can be 1 or 100 or may be more. I hope i was able to explain my question. Please can any friend can help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email according to ListBox items
On Feb 18, 11:59*am, Joel wrote:
This sounds like it would be better as an Outlook VBA Macro rather than an Excel VBA Macro. *Even though the files are excel files it would be better as an email solution. *You probably have Outlook opened on your PC and it would be simplier to just push a button in outlook than to open an excel file and run a macro. "K" wrote: Hi all, *I have ListBox1 on my sheet and with one click of button I get all the files names from specific folder into ListBox1 item list. See below the example of files names. Derek Williom.xlsm John Robert.xls Steve Wasten.xlsx Jerry Ali.xlsm etc .. I want macro on a button which should Send separate Emails and in each email it should attache one file from ListBox1 items and put person's name in email address (where it say "TO") which will be the characters before the dot in file name. For example taking above files names lets say i have these files names in ListBox1 and which i got from folder "C:\Files". *Because i have four files so i need to send four emails and in first email i need "Derek Williom.xlsm" file to be attached and in "TO" section i want the characters before the dot in file name so in this case it will be "Derek Williom" and same thing i need with other peoples email. *Basically i want to send emails to people with their name file attached. *The files in list box can be 1 or 100 or may be more. *I hope i was able to explain my question. *Please can any friend can help.- Hide quoted text - - Show quoted text - Hi Joel thanks for replying. I dont need any macro on Outlook. This has to be done in excel. it is important for my project. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email according to ListBox items
try this
Sub SendMail() Folder = "c:\temp\" Set myOlApp = CreateObject("Outlook.Application") FName = Dir(Folder & "*.xls*") Do While FName < "" ToName = Left(FName, InStrRev(FName, ".") - 1) Set MyItem = myOlApp.CreateItem(olMailItem) Set myAttachments = MyItem.Attachments myAttachments.Add Folder & FName MyItem.Recipients.Add (ToName) MyItem.Send FName = Dir() Loop End Sub "Joel" wrote: This sounds like it would be better as an Outlook VBA Macro rather than an Excel VBA Macro. Even though the files are excel files it would be better as an email solution. You probably have Outlook opened on your PC and it would be simplier to just push a button in outlook than to open an excel file and run a macro. "K" wrote: Hi all, I have ListBox1 on my sheet and with one click of button I get all the files names from specific folder into ListBox1 item list. See below the example of files names. Derek Williom.xlsm John Robert.xls Steve Wasten.xlsx Jerry Ali.xlsm etc¦¦¦¦.. I want macro on a button which should Send separate Emails and in each email it should attache one file from ListBox1 items and put person's name in email address (where it say "TO") which will be the characters before the dot in file name. For example taking above files names lets say i have these files names in ListBox1 and which i got from folder "C:\Files". Because i have four files so i need to send four emails and in first email i need "Derek Williom.xlsm" file to be attached and in "TO" section i want the characters before the dot in file name so in this case it will be "Derek Williom" and same thing i need with other peoples email. Basically i want to send emails to people with their name file attached. The files in list box can be 1 or 100 or may be more. I hope i was able to explain my question. Please can any friend can help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Send email according to ListBox items
Thanks for the code Joel but I dont think this is what I am looking
for. I think I haven't explained my question properly. I thought if I'll ask small questions instead of big question then it would be easy to get an answer. Please let me expalin again that what I am trying to do. I got two ListBox and Three Buttons on my Sheet. Button1 caption is "Get Files from Folder" , Button2 caption is "Transfer" and Button3 caption is "Send Mail". When I click on Button1 I get Browse Folder window in which I select desired Folder and when I press OK on it I get all the files names in that Folder appeared in ListBox1. After this I select the files names in ListBox1 the one I am after and then I click on Button2 which then transfer those selected files names items into ListBox2. Now I need macro to assign to Button3 which should saparately Send Mail according to the items in ListBox2 (in other words if i have two items in ListBox2 then send two mails if i have ten then send ten mails etc). In each Mail each file (which names are showing in ListBox2) should be attached and in "TO" section of Mail macro should put names exracting from attached file name. Please do help if you can. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VBA macro to send email attachment from default email client | Excel Programming | |||
Send data from Excel in email from specific email address | Excel Programming | |||
select file fro listbox and send via email | Excel Programming | |||
send wkbk as an email attachment with an email address copied from | Excel Discussion (Misc queries) | |||
Adding Items to a ListBox-Unique Items Only | Excel Programming |