Send email according to ListBox items

[K]

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.

 

[Joel]

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]

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.







- 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.

 

[Joel]

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

 

[K]

Thanks for the code Joel but I don’t 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.