Attach files and send emails using vba

[mikezcg]

I keep a group of excel files in one folder, at the end of each
month, i have to email them as attachments with text and topic to
about 50 people.



I have programed all the excel manipulation access. What i wouldl ike
to do is something like a select case for each file in the dir like:





dim fi as fileobj

(dont know if fileobj exists but it does in .net so i will use here)



for each fi in DirObj

select case cstr(fi)

case "mary"

attach this excel file add subject and text and email to mary

case "hary"

attach this excel file add subject and text and email to hary



end select

next fi





The end result is i click a button on a form and send the same topic,
same text different excel files, to 50 users.





HELP!!! ha ha ha





M~

 

[Dick Kusleika]

M~

You can use the Dir function

Dim FName as String

Fname = Dir("C:\MyFolder\*.xls")

Do While Len(FName) > 0
Select Case FName
Case "Mary.xls"
'Code to email
Case "Harry.xls"
'Code to email
End Select
FName = Dir
Loop

For the code to email see here

http://www.rondebruin.nl/sendmail.htm
www.dicks-clicks.com

That's a lot of case statements, however. An you'll probably want the email
code in a separate sub so you don't have to repeat it. If you could create
a table or an array that held all the filenames and thier respective email
addresses, you wouldn't need to select case

Do While Len(FName) > 0
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = 'find email in table
.Subject = "Your file"
.Body = "Here's your file"
.Attachments.Add FName
.Send
End With
Fname = Dir
Loop